Thread: 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@fuhr.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@fuhr.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@fuhr.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@fuhr.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@fuhr.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@fuhr.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@fuhr.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