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