Lincoln Yeoh wrote: > At 01:51 AM 7/2/2003 -0400, Mike Mascari wrote: > >> > Maksim Likharev wrote: >> >>I do not know how that will work for PG, but in Microsoft SQL Server >> >>you can do following >> >>BEGIN TRANSACTION >> >>UPDATE [val] = [val] >> >> WHERE .... >> >>INSERT ... >> >>COMMIT TRANSACTION >> >> >> >>so basically by updating specific row ( let say you have such row ) >> >>in transaction, row/page lock will be held until end of transaction >> >>and concurrent UPDATE will wait until you are done. >> >>Kind of semaphore. >> > >> > Why the UPDATE? And in postgres every query runs in it's own >> transaction >> > so no need for the explicit BEGIN / END block. >> >> The solution works because: >> >> Backend #1: >> >> BEGIN; >> UPDATE foo SET bar = bar WHERE keyid = 1; >> >> Backend #2: >> >> BEGIN; >> UPDATE foo SET bar = bar WHERE keyid = 1; <-- Now blocks because of #1 > > > Sorry, I can't check at the moment but does this actually block in all > possible cases? Especially when there's no row with keyid=1 before BEGIN > is issued in any of the backends?
The idea would be to pre-create the row in question before the application starts. The application would then use the row, as Maksim Likharev wrote, as a semaphore or mutex. The row being updated is not the row associated with the INSERT. It is an arbitrary row. It is like a named mutex, with the name being the WHERE clause. Example: CREATE TABLE locktable (lockid integer); INSERT INTO locktable VALUES (1); Now, to prevent the race condition caused by the INSERT/EXISTS trick: BEGIN; UPDATE locktable SET lockid = lockid WHERE key = 1; INSERT INTO foo SELECT 'MyKey', 'MyField1', 'MyField2' WHERE NOT EXISTS ( SELECT 1 FROM foo f WHERE f.key = 'MyKey' ); END; The above example will not achieve row-level granularity for the lock, since any INSERT will block any other. In fact, if the row is used by the application for more than one table, it won't even achieve table-level granularity. But you could get closer to row-level granularity by doing something like: Before application begins: INSERT INTO locktable VALUES (0); INSERT INTO locktable VALUES (1); ... INSERT INTO locktable VALUES (999); And then, anywhere concurrency is an issue for a given key: UPDATE locktable SET lockid = lockid WHERE lockid = MyKey % 1000; Again, this trick doesn't work in SERIALIZABLE mode since the UPDATE will cause a serialization error in another backend. Mike Mascari [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
