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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
