Re: Re: Why is unique constraint needed for upsert? (treat atomicity as optional)

On 7/23/14 7:45 PM, John R Pierce wrote:
> On 7/23/2014 3:29 PM, Seamus Abshere wrote:
>> My argument lives and dies on the assumption that UPSERT would be
>> useful even if it was (when given with no options) just a macro for
>>
>>>   UPDATE db SET b = data WHERE a = key;
>>>   IF NOT found THEN
>>>     INSERT INTO db(a,b) VALUES (key, data);
>>>   END IF;
>
> but that won't work if two connections execute similar 'upserts'
> concurrently.    both updates will see the record isn't there, then one
> or the other insert will fail, depending on which transaction commits
> first.

John,

Right - if you had a situation where that might happen, you would use a
slightly more advanced version of the UPSERT command (and/or add a
unique index).

UPSERT, in this conception and in its most basic form, would be subject
to many of the same (and more) concurrency concerns as basic INSERTs and
UPDATEs.

Providing options may be preferable magically handling everything.

Best,
Seamus


--
Seamus Abshere, SCEA
+598 99 54 99 54
https://github.com/seamusabshere