From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-admin@postgresql.org
Subject: Re: Lock!
Date: Tue, 14 Oct 2003 15:00:11 -0400
Message-ID: <m3zng33aro.fsf@chvatal.cbbrowne.com> (raw)
References: <004c01c39175$0f0b3620$2702a8c0@WorkGroup>
<1066148873.5581.67.camel@linda.lfix.co.uk>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: cbbrowne@acm.org
Subject: Re: Lock!
In-Reply-To: <m3zng33aro.fsf@chvatal.cbbrowne.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox