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