Thread: Transaction isolation with concurrent increments

Well, I'm a relative newbie to SQL, but I have been told that this behaviour
is a bug. I hope I didn't misunderstand something.


To reproduce, first create a table 'counters' that looks like that:

 counter1
----------
        0
(1 row)


Then execute the following commands parallelly on two psql terminals:

    set transaction isolation level serializable;
    begin;
    update counters set counter1=(select counter1+1 from counters);

(The second psql terminal will not return from executing the last statement)

Commit on both terminals, then do a select again. The result will be:

 counter1
----------
        1
(1 row)

Oops! ;-)



If instead I use an update statement like that:

    update counters set counter1=counter1+1;

psql behaves like expected, i.E. the result is 2.


--
Grüße,

Nico

Nico Erichsen <nico.e@gmx.de> writes:
>     set transaction isolation level serializable;
>     begin;
>     update counters set counter1=(select counter1+1 from counters);

Try it the other way round:

     begin;
     set transaction isolation level serializable;
     update counters set counter1=(select counter1+1 from counters);

Isolation level is a transaction-local setting so the first way doesn't
affect the level the BEGIN block actually uses.  (There is a SET
variable to change the default level for future transactions, but that
ain't what you set here.)

            regards, tom lane