Re: Promise index tuples for UPSERT

On 10/06/2014 04:44 PM, Simon Riggs wrote:
> On 6 October 2014 13:21, Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> wrote:
>
>>> My understanding of what you're saying is that if
>>>
>>> * we have a table with >1 unique index
>>> * and we update the values of the uniquely index columns (e.g. PK update)
>>> * on both of the uniquely indexed column sets
>>> then we get occaisonal deadlocks, just as we would do using current
>>> UPDATE/INSERT.
>>
>>
>> Right. To be precise: you don't need to update both of the columns in the
>> same transaction, it's enough that some of the concurrent transactions
>> update one column, while other transactions update the other column.
>
> CREATE TABLE foo
> (id1 integer not null primary key
> ,id2 integer not null unique
> ,val integer);
>
> Given the table above, which one do we mean?
>
> 1. When we mix UPDATE foo SET id2 = X WHERE id1 = Y; and UPDATE foo
> SET id1 = Y WHERE id2 = X; we can deadlock
> 2. When we mix UPDATE foo SET val = Z WHERE id1 = Y; and UPDATE foo
> SET val = W WHERE id2 = X; we can deadlock
>
> (2) is a common use case, (1) is a very rare use case and most likely
> a poor design

Well, at least one of the statements has to be an UPSERT, and at least
one of them has to update a column with a unique constraint on it. This
pair of transactions could deadlock, for example:

Transaction 1:
INSERT INTO foo VALUES (Y, X, Z) ON CONFLICT IGNORE;
Transaction 2:
UPDATE foo SET id2 = X WHERE id1 = Y;

That's made-up syntax, but the idea is that the first transaction
attempts to insert a row with values id1=Y, id2=X, val=Z. If that fails
because of a row with id1=Y or id2=X already exists, then it's supposed
to do nothing.

> If the user wishes to protect against such deadlocks they retain the
> option to use row locking. Yes?

Sorry, I didn't understand that. Row locking?

In general, this is of course a lot easier to implement if we restrict
it so that it only works in some limited cases. That may be fine, but
then we have to be able to document clearly what the limitations are,
and throw an error if you violate those limitations.

- Heikki