Re: Promise index tuples for UPSERT

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

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

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services