Обсуждение: How to prevent duplicate key error when two processes do DELETE/INSERT simultaneously?

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

Brodie Thiesfield <brofield+pgsql@gmail.com> writes:
> 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

You mean they both want to insert the same key?

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

If they both insert the same key, this is what *must* happen.  Surely
you don't expect both to succeed, or one to fail and not tell you.

> 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 think you've got the effects of SERIALIZABLE backward, but in any
case SERIALIZABLE does not affect uniqueness checks.  Unique is unique.

            regards, tom lane

On Thu, Jul 30, 2009 at 12:23 AM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
> Brodie Thiesfield <brofield+pgsql@gmail.com> writes:
>> 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
>
> You mean they both want to insert the same key?

Yes. There are two processes working independently on the same data.
They are both trying to work around the lack of INSERT OR REPLACE by
doing a DELETE/INSERT. I was hoping that this could be somehow done as
an atomic action.

>> One of these processes is getting to the INSERT and failing with
>> duplicate key error.
>> ERROR:  duplicate key value violates unique constraint
>
> If they both insert the same key, this is what *must* happen.  Surely
> you don't expect both to succeed, or one to fail and not tell you.

Yes, it appears my grasp of transaction isolation apparently isn't so
firm. So, the delete/insert combination cannot be made atomic and
transaction isolation is only for read and not update. I was hoping
that the updates would be serialized and so both would succeed with
only one being the eventual winner.

On further investigation, since the logic requires the delete to be
made first to get rid of other possible rows, so I'll go with:

DELETE
(if supported)    INSERT OR REPLACE
(otherwise)        INSERT, if duplicate key, UPDATE

Regards,
Brodie

On Wed, Jul 29, 2009 at 19:53, Brodie
Thiesfield<brofield+pgsql@gmail.com> wrote:
> On further investigation, since the logic requires the delete to be
> made first to get rid of other possible rows, so I'll go with:
>
> DELETE
> (if supported)  INSERT OR REPLACE
> (otherwise)             INSERT, if duplicate key, UPDATE
>

You can use plpgsql to emulate insert or replace (I think there are
some comments on emulating mysql specifically in the commented
versions of the docs somewhere... but thats up to you to find em) see
http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
example 38-1