a SELECT FOR UPDATE question

Hi,

If I select a row for update, is there anyway that someone can query to see
if that row has been 'selected for update' and by whom?

Then I'd like to know if the session that 'SELECT(ed) FOR UPDATE' dies, will
the server release the locked row?

Thank you,

tvadnais

On Mon, Feb 07, 2005 at 03:08:12PM -0800, Tim Vadnais wrote:
>
> If I select a row for update, is there anyway that someone can query to see
> if that row has been 'selected for update' and by whom?

I think you can infer that a process is working with a row in some
manner (UPDATE, SELECT FOR UPDATE) by looking at the row's xmax
column and checking pg_locks to see if any process is holding a
lock on the indicated transaction ID. But why do you need to know?
What are you trying to do?

> Then I'd like to know if the session that 'SELECT(ed) FOR UPDATE' dies, will
> the server release the locked row?

Locks should be released when the holding transaction terminates.
I'm not sure if it's possible for a connection to end and leave a
lock behind, but if it is, I suspect it would be considered a bug
in the server that needed to be fixed.

(This sounds familiar, like I've participated in a thread on this
topic before. I get the feeling Tom Lane is going to step in and
provide The Real Answer, and that my radio is about to start playing
"I Got You, Babe"....)

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

Michael Fuhr <mike(at)fuhr(dot)org> writes:
> On Mon, Feb 07, 2005 at 03:08:12PM -0800, Tim Vadnais wrote:
>> If I select a row for update, is there anyway that someone can query to see
>> if that row has been 'selected for update' and by whom?

> I think you can infer that a process is working with a row in some
> manner (UPDATE, SELECT FOR UPDATE) by looking at the row's xmax
> column and checking pg_locks to see if any process is holding a
> lock on the indicated transaction ID.

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.

> But why do you need to know?
> What are you trying to do?

Indeed. There's probably a better way to think about it ...

>> Then I'd like to know if the session that 'SELECT(ed) FOR UPDATE' dies, will
>> the server release the locked row?

> Locks should be released when the holding transaction terminates.
> I'm not sure if it's possible for a connection to end and leave a
> lock behind, but if it is, I suspect it would be considered a bug
> in the server that needed to be fixed.

Certainly.

regards, tom lane

On Tue, Feb 08, 2005 at 12:58:34AM -0500, Tom Lane wrote:
> Michael Fuhr <mike(at)fuhr(dot)org> writes:
> >
> > I think you can infer that a process is working with a row in some
> > manner (UPDATE, SELECT FOR UPDATE) by looking at the row's xmax
> > column and checking pg_locks to see if any process is holding a
> > lock on the indicated transaction ID.
>
> 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.

Okay...I had done some tests with UPDATE and SELECT FOR UPDATE and
observed what I wrote, but admittedly that covers only a few cases.
If you could point me in the right direction I'll try to work out
an example where my suggestion fails.

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

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.

Like I said, I'm pretty tired and I might be missing something...

regards, tom lane

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/

Michael Fuhr <mike(at)fuhr(dot)org> writes:
> 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?

Right. Furthermore, xactC's query result could have been stale when it
was obtained, nevermind the separate query to pg_locks:

xactA: updates row
xactC: starts, sets snapshot
xactB: attempts to update same row, blocks until xactA completes
xactA: commits
xactB: unblocks and acquires a lock on the row
xactC: query finds xactA in row's xmax because of MVCC rules

regards, tom lane

On Thu, Feb 10, 2005 at 09:53:49AM -0500, Tom Lane wrote:
>
> Right. Furthermore, xactC's query result could have been stale when it
> was obtained, nevermind the separate query to pg_locks:
>
> xactA: updates row
> xactC: starts, sets snapshot
> xactB: attempts to update same row, blocks until xactA completes
> xactA: commits
> xactB: unblocks and acquires a lock on the row
> xactC: query finds xactA in row's xmax because of MVCC rules

Hmmm...that's not what I'm seeing in 8.0.1, at least not when
xactC is READ COMMITTED:

CREATE TABLE foo (id integer PRIMARY KEY, val text NOT NULL);
INSERT INTO foo VALUES (1, 'initial');

xactA=> BEGIN;
xactA=> UPDATE foo SET val = 'A' WHERE id = 1;
xactA=> SELECT xmin, xmax, * FROM foo;
xmin | xmax | id | val
--------+------+----+-----
122508 | 0 | 1 | A

xactC=> BEGIN;

xactB=> BEGIN;
xactB=> UPDATE foo SET val = 'B' WHERE id = 1; -- blocks

xactA=> COMMIT; -- xactB now unblocked

xactB=> SELECT xmin, xmax, * FROM foo;
xmin | xmax | id | val
--------+------+----+-----
122512 | 0 | 1 | B

xactC=> SELECT xmin, xmax, * FROM foo;
xmin | xmax | id | val
--------+--------+----+-----
122508 | 122512 | 1 | A

In xactC's query, xmax is xactB. Is this test not the situation
you describe? I've seen stale info under certain conditions when
xactC is SERIALIZABLE, but when it's READ COMMITTED then the tests
I've done so far have always seen xmax change to whoever currently
holds the lock. There's still a race condition, but visibility
doesn't seem to be a problem. Is that not supposed to be happening,
or am I still missing something?

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

Michael Fuhr <mike(at)fuhr(dot)org> writes:
> On Thu, Feb 10, 2005 at 09:53:49AM -0500, Tom Lane wrote:
>> xactC: starts, sets snapshot

> Hmmm...that's not what I'm seeing in 8.0.1, at least not when
> xactC is READ COMMITTED:

Remember RC mode takes a new snapshot for each query. You'd need to
use serializable mode --- and do something to actually freeze the
transaction snapshot, which BEGIN does not --- to see the issue in a
manual test.

regards, tom lane

On Thu, Feb 10, 2005 at 10:40:44AM -0500, Tom Lane wrote:
>
> Remember RC mode takes a new snapshot for each query. You'd need to
> use serializable mode --- and do something to actually freeze the
> transaction snapshot, which BEGIN does not --- to see the issue in a
> manual test.

It sounds like the best a check could do would be the amazingly
astute "some transaction held a lock on this row at one time and
may or may not still hold that lock, and even if it did when you
checked it might have gone away by now and some other transaction
that you don't know about might hold a lock."

Does that about sum it up? ;-)

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

Michael Fuhr <mike(at)fuhr(dot)org> writes:
> It sounds like the best a check could do would be the amazingly
> astute "some transaction held a lock on this row at one time and
> may or may not still hold that lock, and even if it did when you
> checked it might have gone away by now and some other transaction
> that you don't know about might hold a lock."

> Does that about sum it up? ;-)

Yeah. Really, if you want to inspect the state of a lock,
the only meaningful operation is to try to acquire the lock.
It's reasonable to offer an "acquire only if immediately available"
operation --- but reporting on the instantaneous state seems
pretty useless by itself.

regards, tom lane