Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> we would instead get a fresh snapshot and retry -- which is what
>> we do in a READ COMMITTED transaction.
> I think you misunderstand how READ COMMITTED works; it does not
> change the snapshot for the entire statement, it only follows the
> update chain for a particular tuple that's been chosen for update
> or delete.
Thanks for the clarification. That does not work for SERIALIZABLE
at all, because other tables or rows referenced in that first
statement would be using the original snapshot. Indeed, the
behavior under READ COMMITTED could be astonishing in certain
circumstances as it breaks atomicity:
"atomicity: all of the results of a transaction should be visible in
the database, or none of them should be. It should never be possible
to see the results of some operations in a transaction without the
others."
connection1:
============
test=# create table t (c1 int not null primary key, c2 int);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"t_pkey" for table "t"
CREATE TABLE
test=# insert into t values (1, 101), (2, 7);
INSERT 0 2
test=# start TRANSACTION ISOLATION LEVEL READ COMMITTED ;
START TRANSACTION
test=# update t set c2 = c2 + (select c2 from t where c1 = 2) where
c1 = 1;
UPDATE 1
test=# update t set c2 = 11 where c1 = 2;
UPDATE 1
connection2:
============
test=# START TRANSACTION ISOLATION LEVEL READ COMMITTED ;
START TRANSACTION
test=# update t set c2 = c2 + (select c2 from t where c1 = 2) where
c1 = 1;
[blocks]
connection1:
============
test=# commit;
COMMIT
connection2:
============
UPDATE 1
test=# commit;
COMMIT
test=# select * from t;c1 | c2
----+----- 2 | 11 1 | 115
(2 rows)
The update on connection2 added the modified value of the first
update from connection1 to the unmodified value from the second
update on connection1. In other words, the atomicity of the update
on connection1 is broken in this case.
I'm not sure why this is considered OK. At a minimum it should be
mentioned in our documentation of our implementation of the READ
COMMITTED isolation level.
-Kevin
