On 6 October 2014 13:21, Heikki Linnakangas <hlinnakan...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers