Search Postgresql Archives

Transaction isolation levels

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux