Re: determine snapshot after obtaining locks for first statement

Tom Lane <tgl@sss.pgh.pa.us> wrote:
> With the code you give, the snapshot is acquired at the beginning
> of processing the UPDATE command, before it finds out that the
> target is t2a and acquires a lock on it.
Right.  The optimization would be to wait to determine the snapshot
for the UPDATE statement until we acquire the lock.  (This trick
only worked for the first statement in a database transaction.) 
Perhaps that's not feasible in PostgreSQL.  That's what I'm asking.
> (Besides which the lock acquired by UPDATE isn't exclusive and
> wouldn't block anyway...)
It blocks other UPDATEs.
>> 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.
> 
> If he's talking about automatically taking an exclusive lock, I
> doubt very many of our users would find that an improvement.
I don't believe he's talking about a lock which excludes SELECTs on
the data.  He's talking about reducing transaction aborts based on
the "First Committer Wins" rule.  Apparently it helped his
high-contention benchmarks considerably in all three isolation
levels he was testing -- snapshot, serializable snapshot, and
serializable S2PL.
If there's something fundamentally different about how PostgreSQL
does things, such that we *can't* get the lock before the snapshot,
that's fine -- I just thought maybe this was a trick we could use.
-Kevin