On 10/06/2014 04:44 PM, Simon Riggs wrote:
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

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.

(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:
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

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to