Thread: blocking INSERTs
I want to do the following: BEGIN; SELECT ... FROM table WHERE a = 1 FOR UPDATE; UPDATE table SET ... WHERE a = 1; if that resturns zero then INSERT INTO table (...) VALUES (...); END; The problem is that I need to avoid race conditions. Sometimes I get primary key exceptions on the INSERT. I think I need to lock the table in share mode to keep inserts from happening, but that blocks vacuums, and blocked vacuums block other things behind them. So how do I get around this?
Joseph Shraibman <jks@selectacast.net> writes: > I want to do the following: > > BEGIN; > SELECT ... FROM table WHERE a = 1 FOR UPDATE; > UPDATE table SET ... WHERE a = 1; > if that resturns zero then > INSERT INTO table (...) VALUES (...); > END; > > The problem is that I need to avoid race conditions. Sometimes I get > primary key exceptions on the INSERT. > > I think I need to lock the table in share mode to keep inserts from > happening, but that blocks vacuums, and blocked vacuums block other > things behind them. So how do I get around this? Ummm... Don't lock the table for very long? :) If you commit your transaction promptly then the table will be unlocked again. -Doug
On Tue, 7 Jun 2005, Joseph Shraibman wrote: > BEGIN; > SELECT ... FROM table WHERE a = 1 FOR UPDATE; > UPDATE table SET ... WHERE a = 1; > if that resturns zero then > INSERT INTO table (...) VALUES (...); > END; > > The problem is that I need to avoid race conditions. Sometimes I get > primary key exceptions on the INSERT. PG uses row locking and the problem above is that if there is no row with a=1 then there is no row to lock in the first select. The update will update zero rows and then the you come to the insert and nothing is locked so 2 transactions can do the insert at the same time. This means that one of the 2 transactions will fail. If you use pg 8.0 maybe this example might help you: http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE and if not you are correct that you need to lock the table (or just accept that it fail sometimes and handle that failure in the client). -- /Dennis
On Wed, 2005-06-08 at 05:31, Joseph Shraibman wrote: > I want to do the following: > > BEGIN; > SELECT ... FROM table WHERE a = 1 FOR UPDATE; > UPDATE table SET ... WHERE a = 1; > if that resturns zero then > INSERT INTO table (...) VALUES (...); > END; > > The problem is that I need to avoid race conditions. Sometimes I get > primary key exceptions on the INSERT. > > I think I need to lock the table in share mode to keep inserts from > happening, but that blocks vacuums, and blocked vacuums block other > things behind them. So how do I get around this? > I think there's no way to avoid the race condition, I got to this conclusion while following past discussions (which were many of them, look for "insert or update" for example). There is though a solution starting with 8.0, namely you should place a save point before the inserts which are susceptible to fail, and roll back to that save point on failure. Then you can update while still keeping the transaction running. The strategy would be then "insert first, update if failed". HTH, Csaba.