Thread: Lock!

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.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "I delight to do thy will, O my God; yea, thy law is
      within my heart."             Psalms 40:8

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.

Oliver Elphick wrote:
> 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).

Unless you set STATEMENT_TIMEOUT:

http://www.postgresql.org/docs/7.3/static/runtime-config.html#RUNTIME-CONFIG-GENERAL

In other words, try:

SET STATEMENT_TIMEOUT=50;
SELECT * FROM foo WHERE foo_id = 1 FOR UPDATE;

That means if another process is locking foo_id = 1 row for more than 50
millisecond after you try to SELECT it FOR UPDATE, you'll get an
error/rollback, and you can then assume it's being used.

Just remember to reset STATEMENT_TIMEOUT to zero or your default before
any slow queries in that transaction.


There was some discussion about emulating (IIRC) Oracle's SELECT .. FOR
UPDATE NOWAIT somewhere in the Postgresql archives. Look them up for
more details.

Is there a good all-inclusive resource out there that documents
LOCK in PostgreSQL, any side-effects when used, and how UPDATE,
DELETE, cursors, transactions, and so on use them?

Thanks,
Greg

--
Greg Spiegelberg
  Sr. Product Development Engineer
  Cranel, Incorporated.
  Phone: 614.318.4314
  Fax:   614.431.8388
  Email: gspiegelberg@Cranel.com
Cranel. Technology. Integrity. Focus.

Ang Chin Han <angch@bytecraft.com.my> writes:
> Just remember to reset STATEMENT_TIMEOUT to zero or your default before
> any slow queries in that transaction.

Also, you can use "SET LOCAL ..." to constrain the effects of the SET
command; this is probably safer than trying to remember to reset the
variable when you're done.

            regards, tom lane