Re: Duplicate key insert question

On Tuesday 01 July 2003 09:25 pm, Jean-Christian Imbeault wrote:
> Reuben D. Budiardja wrote:
> > No, onlu *one* of them will fail, but yes, the other will then generate
> > error. So it really is a trade off. Another way would be to lock the
> > table, as other has suggested. But then there is disadvantages to that
> > also.
>
> Really? I just got a post form Alvaro Herrera saying;
>
> "The solution is not correct in that there _is_ a race condition."
>
> Maybe I misunderstood, but "not correct" doesn't sound good :)
>
If you want to avoid the race condition as well, then use the locking
mechanism for transaction. Combine it with the previous INSERT ... SELECT ...
WHERE NOT EXISTS, it should give you what you want. I suspect it's slower
though. Eg:

BEGIN WORK;

INSERT INTO mytable
SELECT 'value1', 'value2'
   WHERE NOT EXISTS
                (SELECT NULL FROM mytable
                    WHERE mycondition)

COMMIT WORK;

This should solve the Race Condition, since other transaction have to wait.
But if the PK already exists, this will quit without error.

RDB