On 6 October 2014 13:21, Heikki Linnakangas <hlinnakangas@vmware.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
