On Tue, Jun 14, 2011 at 3:30 AM, AI Rumman <rummandba@xxxxxxxxx> wrote: > Hi, > > I got duplicate key violate error in the db log for the following query: > INSERT INTO tab1 ( SELECT '1611576', '1187865' WHERE NOT EXISTS ( > SELECT 1 FROM tab1 WHERE id='1611576' AND id2='1187865' ) ) > > The error occured during production time. > But when I manually executed the query, it inserted one row with success and > next time it inserted 0 rows. Unfortunately the operation above is not atomic. This is a classic concurrency problem that everyone has to deal with -- there is no way at present to rely on a simple row level lock to prevent concurrent inserts to the same key. You have a few of ways to deal with this: *) retry the statement (personally not a big fan of this method) *) lock the table (lousy concurrency) *) advisory lock might work, if you must have concurrency and your key is an integer. be careful, and do not overuse the technique. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
