Transaction isolation levels

From: Geert Jansen <geert(at)boskant(dot)nl>
To: pgsql-general(at)postgresql(dot)org
Subject: Transaction isolation levels
Date: 2005-07-09 11:18:09
Message-ID: 42CFB271.2090702@boskant.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

I'm having some trouble with transaction isolation levels, and would
appreciate some input on this.

According to the documentation of PostgreSQL 8.0.3, section 12.2:

"Read Committed is the default isolation level in PostgreSQL. When a
transaction runs on this isolation level, a SELECT query sees only data
committed before the query began; it never sees either uncommitted data
or changes committed during query execution by concurrent transactions."

Therefore, in this isolation level, I should not see data committed by
another concurrent transaction.

Now I open two terminals with a 'pgsql' command, and do the following:

terminal 1:
geertj=> \set AUTOCOMMIT off
geertj=> set transaction isolation level read committed;
SET
geertj=> create table products (id integer not null, code char(20)
not null);
CREATE TABLE
geertj=> commit; begin;
COMMIT
BEGIN

terminal 2:
geertj=> \set AUTOCOMMIT off
geertj=> set transaction isolation level read committed;
SET
geertj=> select * from products;
id | code
----+------
(0 rows)

terminal 1:
geertj=> insert into products values (10, 'pr10');
INSERT 34071 1
geertj=> commit;
COMMIT

terminal 2:
geertj=> select * from products;
id | code
----+----------------------
10 | pr10

As you see, the row inserted in the transaction from terminal 1 appears
into terminal 2, but terminal 2 had a transaction open that did not commit.

The top of section 12.2 of the manual tells that nonrepeatable reads may
happen in the 'read committed' isolation level. I can understand the
above behaviour in terms of this. However, the start of section 12.2.1
tells that in read committed mode, you get a snapshot of the database
when the transaction starts, and that snapshot will not inlude committed
changes from other transactions. Who is right here?

Regards,
Geert

-----BEGIN PGP SIGNATURE-----
Version: PGP Desktop 9.0.1 (Build 2185)

iQEVAwUBQs+ycZrPkVOV4lDgAQK0NggAonVk+Qmwui4a49UJr10P7mxcmrFblw+x
6HM6yXVCBk4qRczRT8BelQp3fBQR/8kostAbmnQ27Pes0wFPOsUEjiyWyskKdCtU
Mt5OXQsV7jAPAwPgFDwnYbd0geDVK76WwTJAKuXBunL5/Mz92nv6XHxWvFjLNNEs
laPpirH1xGFy9Po0kpYLx7Orgg6I/m/BM4V4BItZqVcFaiFLnCTt+Lolimk6j65Z
NzHIkTLq2C+ju1EqvbF6M1euAR0ni6D4DLIUd11XJnw0A8mx/+7ZV5ZGcHv+X6YT
dsfutW17jZkzlB9yUyKa91wII0leo/fFlRCnoSYqG3ONGJLoiNJdXw==
=/78D
-----END PGP SIGNATURE-----

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dennis Bjorklund 2005-07-09 11:18:11 Re: many updates to single row in single transaction
Previous Message Geert Jansen 2005-07-09 11:13:13 Transaction isolation levels