SQL concurrent inserts issue

Question

I have a relation y(k, v) where k is a key. Having concurrent connections on the database, I run on each of them:

INSERT INTO y 
     SELECT 'k1', 'v1' WHERE NOT EXISTS (SELECT 1 FROM y WHERE k = 'k1');

sometimes I get the following error:

ERROR: duplicate key value violates unique constraint "y_pk"

Why does it happen? Can it be avoided?

is that complete query ? What is structure of Table? – H. Mahida Jun 18 '14 at 07:28 — H. Mahida, Jun 18 '14 at 07:28

score 1 · Accepted Answer · edited May 23 '17 at 12:14

The issue here is that if this query runs in two different connections at the same time then NOT EXISTS condition true for BOTH (it's tested before insert) and then BOTH connections INSERT the same value. You need a table lock or try to use this script:

BEGIN
    INSERT INTO Y(k,v) VALUES ('k1', 'v1');
    EXCEPTION WHEN unique_violation THEN
END;

Also read this similar topic: SO:How do I do an UPSERT...

score 0 · Answer 2 · answered May 02 '16 at 20:19

0

As of PostgreSQL 9.5, UPSERTS are natively suported, and would solve this use case.

answered May 02 '16 at 20:19

Jeff Widman

22,014
12
72
88

SQL concurrent inserts issue

2 Answers2