Re: a SELECT FOR UPDATE question

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