Обсуждение: Transaction isolation levels

-----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+xUJrPkVOV4lDgAQJsFggApVahUUKqbNAyf7jm3e9WYIvj3bGf2nhL
0jbBtVDp+ewemMHYaCjSyx+Bj+IbnsHJEQywDVX5GQSuL9/7AEyB/RFs0lpGss26
CZDYdH08rBYSTonpEHy2x5cM77A5O/7MZSfcPliR/ON7iQRmuVwWltWjCt0or7VD
+3y45bWldzzSJ42WCBSS5eJQp5xjCA91CNR3dH09H4i+8Y5PgpHyvvZgB/cmdzCV
HmRWDDsohgaKDZRilh/A+q8BhUuxks3xzqY3JUrTZ7Js3x/9kcJyoB41EXuuT/AW
gwYcZJJ56XN2UrmPI8lXZWpkdKnBOAU5g2YIa+alAzw9U06CuFEatw==
=1Gpf
-----END PGP SIGNATURE-----

On Sat, Jul 09, 2005 at 01:13:13PM +0200, Geert Jansen wrote:

> "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.

Wrong.  You _should_ see committed data.  That's why it's called "read
committed."  What you should not see is data that has not been committed
yet, or data that was committed after the current _query_ began.  Note
that it says "query," not "transaction."

You can try it with a cursor, because for all purposes, all fetches from
one cursor effectively behave like they were a single query (they all
use one "database snapshot".)  So you can insert pauses in the query
while you commit other transactions in the middle.

sess 1:
alvherre=# create table foo (a int);
CREATE TABLE
alvherre=# insert into foo values (1);
INSERT 0 1

sess 2:
alvherre=# begin;
BEGIN
alvherre=# declare foo cursor for select * from foo;
DECLARE CURSOR
alvherre=# fetch 1 from foo;
 a
---
 1
(1 fila)

sess 1:
alvherre=# insert into foo values (2);
INSERT 0 1

sess 2:
alvherre=# fetch 1 from foo;
 a
---
(0 filas)


--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"And as an added bonus, now my computer goes to the toilet for me, leaving me
free to spend time on more useful activities! yay slug codefests!" (C. Parker)