Re: Duplicate key insert question

Jean-Christian Imbeault wrote:
> Maksim Likharev wrote:
>
>>Finding if the duplicate value exists and inserting if not.
>
> Ok, thanks but I think it is still vulnerable to a race condition.
>
>>I do not know how that will work for PG, but in Microsoft SQL Server
>>you can do following
>>BEGIN TRANSACTION
>>UPDATE [val] = [val]
>>    WHERE ....
>>INSERT ...
>>COMMIT TRANSACTION
>>
>>so basically by updating specific row ( let say you have such row )
>>in transaction, row/page lock will be held until end of transaction
>>and concurrent UPDATE will wait until you are done.
>>Kind of semaphore.
>
> Why the UPDATE? And in postgres every query runs in it's own transaction
> so no need for the explicit BEGIN / END block.

The transaction semantics of your application should dictate the use
of BEGIN/END. If you aren't using BEGIN/END (as many non-InnoDB MySQL
applications fail to do), I doubt you can guarantee logical
consistency and performance will be less than what it should be. With
WAL, the difference is less noticeable, but it is there. The use of
BEGIN/END to guarantee logical consistency will help performance. You
kill two birds with one stone.

> So can't see how your solution is any better than the previous one :)

The solution works because:

Backend #1:

BEGIN;
UPDATE foo SET bar = bar WHERE keyid = 1;

Backend #2:

BEGIN;
UPDATE foo SET bar = bar WHERE keyid = 1; <-- Now blocks because of #1

Backend #1:

INSERT INTO weirdtable
SELECT weirdkey, field1, field2
WHERE NOT EXISTS (
 SELECT 1 FROM weirdtable w
 WHERE w.key = weirdkey
); <-- Performs the insert (or not)

COMMIT;

Backend #2:

INSERT INTO weirdtable
SELECT weirdkey, field1, field2
WHERE NOT EXISTS (
 SELECT 1 FROM weirdtable w
 WHERE w.key = weirdkey
); <-- Does nothing, as INSERT was (possibly) caused by Backend #1

COMMIT;

The UPDATE acts as a lock with row granularity, preventing the race
condition caused by multiple INSERT..WHERE NOT EXISTS. Tuple
visibility rules in READ COMMITTED will allow Backend #2 to see the
new INSERT performed by Backend #1, since Backend #2 won't continue
past the UPDATE until Backend #1 has issued a COMMIT or ABORT.
However, the solution prohibits you from using SERIALIZABLE, since the
UPDATE of the same lock row will cause a serialization error.

If READ COMMITTED is sufficient, the question arises as to how to get
row granularity from the lock. One could have a lock table composed of
the keys being inserted but how do the keys get into the lock table
without a race condition there? If row-lock granularity cannot be
achieved, and you absolutely cannot handle a race condition causing a
unique key violation, you might as well just use LOCK TABLE. You might
also want to investigate the userlocks in /contrib, although I haven't
used them so I cannot speak to their usefulness.

Mike Mascari
mascarm@mascari.com