Обсуждение: Buggy SELEC ... FROM UPDATE ?

Hello, (bis)

I try to use SELECT FOR UPDATE, but it seems to be buggy, hum ? Perhaps it's
my algorythm...

My syslock table:
---------------
id   integer
used boolean
---------------

Here is the algo:
---------------
boolean Got = false
while Got == false
    Start transaction
    SELECT * FROM syslock WHERE id=%d AND used='f' FOR UPDATE
    if SELECT returns a row
    then
        UPDATE syslock SET used='t' WHERE id=%d
        Commit transaction
        Got = true
    else
        Rollback transaction
        continue // yes, active wait ... I'm testing !
    end if
end while
Trace "I get the syslock number %d !!"
Do something with the syslock %d which is now reserved
UPDATE syslock SET used='f' WHERE id=%d
---------------

When a lot of threads (well, I tried 20, 5 and 2) used this algo, more than
one thread get the syslock %d at the same time.

What is the problem ?

Thanks
Patrice Espié
--
Le Monde, c'est comme un éléphant
qui va à la pêche

All locks are released when you commit your transaction.

> boolean Got = false
> while Got == false
>     Start transaction
>     SELECT * FROM syslock WHERE id=%d AND used='f' FOR UPDATE
the row is locked here ...
>     if SELECT returns a row
>     then
>         UPDATE syslock SET used='t' WHERE id=%d
>         Commit transaction
... and released again here; probably not what you want
>         Got = true
>     else
>         Rollback transaction
>         continue // yes, active wait ... I'm testing !
>     end if
> end while
> Trace "I get the syslock number %d !!"
> Do something with the syslock %d which is now reserved
> UPDATE syslock SET used='f' WHERE id=%d
should move your "commit transaction" here

have fun
helge

On Tue, 10 Apr 2001, Patrice Espié wrote:

> > > Sure, I want to release it : the other users MUST be free to go away
> instead
> > > of being locked if they try to lock anything already locked
> > A solution I have implemented once is to use an application-side timer
> > to cancel the request after a certain timeout; ugly, but doable. What
> > interface to postgres do you use?
> Doable...
> I use ODBC under Windows, but why not the C or C++ interface ? I will use
> the most efficient (if I can decide which one...)
uhh... I know nothing about Windows, but on Unix and libpq you would
simply use alarm(timeout) to initiate the timer and
sigaction(SIGALRM,...) to install a handler for the timer event which can
in turn call PQrequestCancel; I guess Windows and ODBC can't be too
different, but you better ask someone who knows Windows a bit better

note that the current transaction is in 'aborted' state when you cancel a
request, so you will have to "rollback" and "begin" a new one before
sending any other queries

hope that helps

----- Original Message -----
From: "Helge Bahmann" <bahmann@math.tu-freiberg.de>
To: "Patrice Espié" <patrice.espie@univ-lyon2.fr>
Cc: <pgsql-novice@postgresql.org>
Sent: Tuesday, April 10, 2001 4:39 PM
Subject: Re: [NOVICE] Buggy SELEC ... FROM UPDATE ?


> All locks are released when you commit your transaction.
>
> > boolean Got = false
> > while Got == false
> >     Start transaction
> >     SELECT * FROM syslock WHERE id=%d AND used='f' FOR UPDATE
> the row is locked here ...
> >     if SELECT returns a row
> >     then
> >         UPDATE syslock SET used='t' WHERE id=%d
> >         Commit transaction
> ... and released again here; probably not what you want
Sure, I want to release it : the other users MUST be free to go away instead
of being locked if they try to lock anything already locked
> >         Got = true
> >     else
> >         Rollback transaction
> >         continue // yes, active wait ... I'm testing !
> >     end if
> > end while
> > Trace "I get the syslock number %d !!"
> > Do something with the syslock %d which is now reserved
> > UPDATE syslock SET used='f' WHERE id=%d
> should move your "commit transaction" here
>
> have fun
> helge
Another idea, please !!
Thank's

=?iso-8859-1?Q?Patrice_Espi=E9?= <patrice.espie@univ-lyon2.fr> writes:
> When a lot of threads (well, I tried 20, 5 and 2) used this algo, more than
> one thread get the syslock %d at the same time.

"Threads"?  Are you sure each thread has an independent database
connection, and you're not reusing connections to issue commands
from different threads?  Your pseudocode looks like it should work
if issued to separate backends ...

            regards, tom lane