> 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?
e.g.
#1 BEGIN
#2 BEGIN
#1 UPDATE foo SET bar = bar WHERE keyid = 1;
(zero rows updated)
#2 UPDATE foo SET bar = bar WHERE keyid = 1;
(Will this really block???? If it does, why and how?)
If this really works, I'll probably have to start removing the locks and selects from my code.
I know "select for update where keyid=1" doesn't work - coz there's no row to lock on before the BEGINs. So this update .... where keyid=1 seems interesting - there's actually a locking difference between select for update and an actual update?
I'll check this tomorrow on 7.3.3.
Thanks, Link.
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
