Re: a SELECT FOR UPDATE question

On Tue, Feb 08, 2005 at 01:45:44AM -0500, Tom Lane wrote:
> Michael Fuhr <mike(at)fuhr(dot)org> writes:
> > On Tue, Feb 08, 2005 at 12:58:34AM -0500, Tom Lane wrote:
> >> I'm too tired to work out an example, but I think this probably doesn't
> >> work in general: the xmax on the version of the row you can see might
> >> not correspond to a live transaction, but that doesn't mean someone
> >> else doesn't hold a lock on the latest committed version of the row.
>
> > If you could point me in the right direction I'll try to work out
> > an example where my suggestion fails.
>
> I'm thinking about a multiple-update situation: your snapshot includes
> row version A, which was superseded by version B, which was superseded
> by version C. By the time you are looking, the transaction that
> committed version B is gone so the xmax you see (B's xact) isn't locked
> anymore. But the "frontmost" version of the row is still locked (by C
> or some later heir) so if you tried to update you'd block.

I've been playing with this and I'm thinking the problem you describe
could happen due to a race condition between finding a particular
transaction ID in xmax and then checking if that ID is locked. Example:

xactA: updates row
xactB: attempts to update same row, blocks until xactA completes
xactC: query finds xactA in row's xmax
xactA: commits
xactB: unblocks and acquires a lock on the row
xactC: query to pg_locks doesn't find xactA, so assumes row not locked

Does that sound like what you're talking about? A new query by
xactC at this point would show xactB in xmax, but that doesn't do
us any good if we've already made a decision based on the previous
queries.

In any case, whatever a transaction learns from such a check could
be out of date by the time it acts on the information, so I'm not
sure how useful it would be.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/