From: Christopher Browne <cbbrowne@acm.org>
Subject: Re: Lock!
Date: Tue, 14 Oct 2003 15:00:11 -0400
Organization: cbbrowne Computing Inc
Lines: 67
Message-ID: <m3zng33aro.fsf@chvatal.cbbrowne.com>
References: <004c01c39175$0f0b3620$2702a8c0@WorkGroup>
	<1066148873.5581.67.camel@linda.lfix.co.uk>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-15
Content-Transfer-Encoding: 8bit
User-Agent: Gnus/5.1003 (Gnus v5.10.3) XEmacs/21.4 (Reasonable Discussion,
	linux)
Cancel-Lock: sha1:RBtWiIGjRiJft5bBL+7RgT772xg=
To: pgsql-admin@postgresql.org

In an attempt to throw the authorities off his trail, olly@lfix.co.uk (Oliver Elphick) transmitted:
> On Mon, 2003-10-13 at 11:30, Szabó Péter wrote:
>> Hi!
>>  
>> I have a lock problem. If i lock a record with SELECT FOR UPDATE, than
>> i try to lock again, the process just wait until the record free. But
>> i have to send a message to the user like 'The record is locked, try
>> later.'. But i can't. How can i do this?
>
> You can't.
>
> Quote from the manual:
>         So long as no deadlock situation is detected, a transaction
>         seeking either a table-level or row-level lock will wait
>         indefinitely for conflicting locks to be released. This means it
>         is a bad idea for applications to hold transactions open for
>         long periods of time (e.g., while waiting for user input).
>         
> You need to rethink your application.
>
> What I have done is to read the data with SELECT.  Just before changing
> it I do SELECT FOR UPDATE and tell the user if anything he is changing
> has changed since he read it in.  If there has been no change, I go
> ahead with the UPDATE.  This means that records are only locked for a
> very short time.  Now, instead of the possibility of being locked out
> for a long time, there is a possibility of having to throw away some
> editing, but I estimate that to be less of a cost overall.

Another strategy that some of our folks have been trying out is that
of "optimistic locking."

It's an in-the-application scheme, which is arguably not totally
ideal, but it has the not-inconsiderable merit that its cost is _very_
low for the common case where there is no conflict.

General idea: You start by SELECTing a lock field on the data you want
to update.  SELECT STUFF, LOCK_FIELD FROM SOME_RELATION;

When you actually do the update, you do it as:

 UPDATE SOME_RELATION SET THIS='This', THAT='That', LOCK_FIELD=txn_id
   WHERE [normal criteria] AND LOCK_FIELD='Value_I_Found_Earlier';

If the record has been updated, then LOCK_FIELD will have a different
value, and this transaction fails; you'll have to do something to
recover, probably a ROLLBACK, and perhaps a retry.

On the other hand, if the record _hasn't_ been touched by anyone else,
then this change will go through, and there wasn't any costly locking
done in the DBMS.

It's not new; it was presented in the IEEE Transactions on Software
Engineering back in '91, and that might not be its genesis...

<http://www.computer.org/tse/ts1991/e0712abs.htm>

There's discussion of it in a Squeak  Wiki...
<http://minnow.cc.gatech.edu/squeak/2634>

It seems to have become publicly popular in the Java world, presumably
due to them finding it expensive to do pessimistic locking (e.g. - as
in starting out with the SELECT FOR UPDATE).
-- 
output = ("aa454" "@" "freenet.carleton.ca")
http://cbbrowne.com/info/linux.html
debugging, v:
        Removing the needles from the haystack.