Re: determine snapshot after obtaining locks for first statement

Hi,

Quoting "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Not at the table level. If you could lock only at the tuple level
> maybe you'd have something

AFAIUI this is about the tuple level lock, yes.

> but it seems like you can't find the
> target tuples without having acquired a snapshot.

Maybe not *the* target tuple, but we could certainly find candidate
target tuples. Of course it's impossible to determine visibility
without a snapshot (and thus find *the* one).

But it seems to me it might suffice to optimistically pick a plausible
tuple (i.e. determined by a candidate snapshot) and try to lock that.
Only after we hold the lock, we get a real snapshot and re-check
visibility of the tuple we've locked.

If it's the visible target tuple we want to update we are all fine, if
not another transaction updated the tuple and we have to look for the
new version of that tuple. As we've just taken a new snapshot *after*
that other transaction updating the tuple of interest, we now see the
*new* tuple and can continue with our transaction normally (instead of
having to abort the transaction with a serialization failure).

So yes, to me this looks like a theoretically possible performance
gain. It certainly only helps the very first tuple write. And it seems
to apply to SERIALIZABLE transactions exclusively.

Another minor gotcha exists, though. There's another possible cause
for the visibility check to fail: our initial pick with the candidate
snapshot might have been wrong. In that unfortunate case we can
continue as described, but it's worth mentioning that we were waiting
for the wrong lock (i.e. a tuple that's not visible according to the
real snapshot might have been one from an aborted transaction, for
example). The candidate snapshot should thus be rather "good", but
that's not much of an issue, I think.

If we want to completely get rid of serialization failures in the
first (writing) operation within a transaction, we'd have to repeat
these steps after a visibility check fails. Meaning having to degrade
the real snapshot acquired after the first lock to a candidate
snapshot for the second tuple lock we try.

Maybe "candidate snapshots" is a good short name for such a feature?

Another line of thought: isn't this like READ COMMITTED for just the
first operation in a SERIALIZABLE transaction?

Regards

Markus Wanner