Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)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