Re: Question on locking

On Fri, Nov 05, 2004 at 07:36:22AM +0000, Steve Tucknott wrote:

> If I have the situation where process 1 has selected record1 from table
> a for update and then process 2 tries to do the same, am I right in
> assuming that process 2 will wait until the first process completes the
> transaction (I've looked at Chapter 12 and this is intimated).

Yes. You can watch this happen if you run two instances of psql,
begin a transaction in each, and do a SELECT FOR UPDATE in each.
The first transaction should return immediately; the second should
block until the first transaction commits or rolls back.

> How can I detect the lock on process 2? I want to be able to tell the
> user that the row is tentatively locked and to allow them to abort the
> update attempt.

You could set statement_timeout to make your queries time out and
assume that somebody else has the record locked if that happens.
Pick a value (milliseconds) that's longer than the SELECT should
take but short enough not to be annoying to the user.

BEGIN;
SET statement_timeout TO 1000;
SELECT * FROM foo WHERE id = 1234 FOR UPDATE;

If you get a timeout then the current transaction will be aborted.

You might want to set the timeout back to its original value (probably
0 unless you've changed it) immediately after the SELECT completes to
avoid timeouts on other queries.

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