Re: determine snapshot after obtaining locks for first statement

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

You'd need an explicit LOCK TABLE t2a after starting the transaction.
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. (Besides which the lock acquired
by UPDATE isn't exclusive and wouldn't block anyway...)

> 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.

regards, tom lane