duplicate key violate error

AI Rumman wrote:

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.

\d tab1
id int
id2 int
primary key (id,id2)

So, any idea why the error occurred at production time.

Concurrency?

Session 1:

CREATE TABLE tab1 (
id integer NOT NULL,
id2 integer NOT NULL,
PRIMARY KEY (id, id2)
);

START TRANSACTION;

INSERT INTO tab1
(SELECT '1611576', '1187865' WHERE NOT EXISTS
(SELECT 1 FROM tab1 WHERE id='1611576' AND id2='1187865')
);

Session 2:

START TRANSACTION;

INSERT INTO tab1
(SELECT '1611576', '1187865' WHERE NOT EXISTS
(SELECT 1 FROM tab1 WHERE id='1611576' AND id2='1187865')
);

Session 1:

COMMIT;

Session 2:

ERROR: duplicate key value violates unique constraint "tab1_pkey"

Yours,
Laurenz Albe

On Tue, Jun 14, 2011 at 3:30 AM, AI Rumman <rummandba@gmail.com> 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