Re: determine snapshot after obtaining locks for first statement

Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> The Cahill thesis mentions an interesting optimization -- they
>> defer determination of the snapshot until after any locks
>> required for the first statement have been acquired.  Where the
>> first statement was, for example, an UPDATE, this reduced
>> re-reads or rollbacks in the face of concurrent modifications.
>  
>> Does PostgreSQL currently do this?
> 
> Yes --- it's not an "optimization", it's necessary for basic
> functionality to work correctly.
Hmmm...  Testing seems to indicate that this doesn't work per the
described optimization:
T1: start transaction isolation level serializable;
START TRANSACTION
T2: start transaction isolation level serializable;
START TRANSACTION
T1: update t2a set c2 = c2 - 1 where c1 = 1;
UPDATE 1
T2: update t2a set c2 = c2 - 1 where c1 = 1;
[blocks]
T1: commit;
COMMIT
T2: [unblocks]
ERROR:  could not serialize access due to concurrent update
The optimization Cahill describes is that for the first statement in
a transaction, the lock for the UPDATE is acquired before obtaining
the snapshot, so T2 succeeds after T1 commits.
-Kevin