Michael Malis via:
http://malisper.me/postgres-transactions-arent-fully-isolated/ has
determined that postgresql transactions are not fully isolated even
when using serializable isolationl level.
If I prep a table, ints via:
postgres=# create table ints (n int);
CREATE TABLE
postgres=# insert into ints values (1);
INSERT 0 1
postgres=# insert into ints values (2);
INSERT 0 1
and then run two concurrent in serializable isolation mode
transactions like this:
T1: BEGIN
T1: UPDATE ints SET n = n + 1;
T2: BEGIN
T2: DELETE FROM ints where n = 2; -- blocks
T1: COMMIT; -- T2 frees
T2: SELECT * FROM ints; -- both rows 2 and 3 visible
T2: COMMIT:
My understanding is that for serializable transactions, the result is
correct as long as you can play back transactions in either order, one
after another, when they overlap and get that result. This is clearly
not the case since when played in either order you'd end up with one
row.
I guess the failure occurs there is some kind of separation between
when the row is initially looked up and the deletion is qualified.
This is likely not a problem in practice, but is Micheal right in is
assessment that we are not precisely following the spec?
merlin