Re: 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