Re: How to prevent duplicate key error when two processes do DELETE/INSERT simultaneously?

On Thu, Jul 30, 2009 at 12:23 AM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Brodie Thiesfield <brofield+pgsql(at)gmail(dot)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