How to prevent duplicate key error when two processes do DELETE/INSERT simultaneously? - Mailing list pgsql-general

Hi,

I've got a problem with a PG client that I'm not sure how to fix.

Essentially, I have two processes connecting to a single PG database
and simultaneously issuing the following statements:

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
DELETE FROM licence_properties WHERE key = xxx;
INSERT INTO licence_properties ... values with key = xxx;
COMMIT

One of these processes is getting to the INSERT and failing with
duplicate key error.
ERROR:  duplicate key value violates unique constraint

The DELETE should prevent this duplicate key error from occurring. I
thought that the ISOLATION LEVEL SERIALIZABLE would fix this problem
(being that the second process can see the INSERT from the first
process after it has done the DELETE), but it doesn't.

I am obviously going about this the wrong way. The database layer is
implemented for a number of different servers and so I was trying to
keep it simple. However, perhaps this is something that I can't
simplify. Should I do SELECT FOR UPDATE and then either an INSERT or
UPDATE?

I would be very appreciative if someone more knowledgeable would point
me to the correct way of doing this? The full PG log of the two
processes follows (in case it is useful).

Regards,
Brodie


log_line_prefix = '%m %p %x %v '
(timestamp, process ID, transaction ID, virtual transaction ID)

logs are stable sorted by process ID then timestamp.

Process 1:
2009-07-29 23:01:01.218 JST 6108 0      3/65 LOG:  statement:
BEGIN;SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2009-07-29 23:01:01.218 JST 6108 0      3/65 LOG:  statement:
SAVEPOINT _EXEC_SVP_03741378
2009-07-29 23:01:01.218 JST 6108 0      3/65 LOG:  statement: SELECT
lid FROM licences WHERE groupid = E'' AND userid = E'test';
2009-07-29 23:01:01.218 JST 6108 0      3/65 LOG:  statement: RELEASE
_EXEC_SVP_03741378
2009-07-29 23:01:01.218 JST 6108 0      3/65 LOG:  statement:
SAVEPOINT _EXEC_SVP_03740350
2009-07-29 23:01:01.218 JST 6108 0      3/65 LOG:  statement: DELETE
FROM licence_properties WHERE lid = '114' AND pkey = E'mytestprop' AND
sessionid IN (0, '0') AND readonly IN (0, 0);
2009-07-29 23:01:01.218 JST 6108 0      3/65 LOG:  statement: RELEASE
_EXEC_SVP_03740350
2009-07-29 23:01:01.218 JST 6108 0      3/65 LOG:  statement:
SAVEPOINT _EXEC_SVP_03742768
2009-07-29 23:01:01.218 JST 6108 0      3/65 LOG:  statement: INSERT
INTO licence_properties (lid, sessionid, pkey, value, readonly) VALUES
('114', '0', E'mytestprop', E'update2', 0);
2009-07-29 23:01:01.218 JST 6108 453328 3/65 LOG:  statement: RELEASE
_EXEC_SVP_03742768
2009-07-29 23:01:01.218 JST 6108 453328 3/65 LOG:  statement:
SAVEPOINT _EXEC_SVP_03740868
2009-07-29 23:01:01.218 JST 6108 453328 3/65 LOG:  statement: UPDATE
licences SET revision = revision + 1 WHERE groupid = E'' AND userid =
E'test';
2009-07-29 23:01:01.218 JST 6108 453328 3/65 LOG:  statement: RELEASE
_EXEC_SVP_03740868
2009-07-29 23:01:01.218 JST 6108 453328 3/65 LOG:  statement: COMMIT

Process 2:
2009-07-29 23:01:01.218 JST 5460 0      2/47 LOG:  statement:
BEGIN;SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2009-07-29 23:01:01.218 JST 5460 0      2/47 LOG:  statement:
SAVEPOINT _EXEC_SVP_03741378
2009-07-29 23:01:01.218 JST 5460 0      2/47 LOG:  statement: SELECT
lid FROM licences WHERE groupid = E'' AND userid = E'test';
2009-07-29 23:01:01.218 JST 5460 0      2/47 LOG:  statement: RELEASE
_EXEC_SVP_03741378
2009-07-29 23:01:01.218 JST 5460 0      2/47 LOG:  statement:
SAVEPOINT _EXEC_SVP_03740350
2009-07-29 23:01:01.218 JST 5460 0      2/47 LOG:  statement: DELETE
FROM licence_properties WHERE lid = '114' AND pkey = E'mytestprop' AND
sessionid IN (0, '0') AND readonly IN (0, 0);
2009-07-29 23:01:01.218 JST 5460 0      2/47 LOG:  statement: RELEASE
_EXEC_SVP_03740350
2009-07-29 23:01:01.218 JST 5460 0      2/47 LOG:  statement:
SAVEPOINT _EXEC_SVP_03742768
2009-07-29 23:01:01.218 JST 5460 0      2/47 LOG:  statement: INSERT
INTO licence_properties (lid, sessionid, pkey, value, readonly) VALUES
('114', '0', E'mytestprop', E'update2', 0);
2009-07-29 23:01:01.218 JST 5460 453330 2/47 ERROR:  duplicate key
value violates unique constraint "pk_lic_prop"
2009-07-29 23:01:01.218 JST 5460 453330 2/47 STATEMENT:  INSERT INTO
licence_properties (lid, sessionid, pkey, value, readonly) VALUES
('114', '0', E'mytestprop', E'update2', 0);
2009-07-29 23:01:01.218 JST 5460 453330 2/47 LOG:  statement: ROLLBACK
to _EXEC_SVP_03742768
2009-07-29 23:01:01.218 JST 5460 453330 2/47 LOG:  statement: RELEASE
_EXEC_SVP_03742768
2009-07-29 23:01:01.218 JST 5460 453330 2/47 LOG:  statement: ROLLBACK