1

If the two updates given in https://www.postgresql.org/docs/current/static/explicit-locking.html#LOCKING-DEADLOCKS happen in one UPDATE query will it still result in deadlock?

Transaction 1 executes the following statement:

-- transaction 1
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum IN (11111, 22222);

Transaction 2 executes the following statement:

-- transaction 2; note the acctnum order is different
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum IN (22222, 11111);

Please note that acctnum order provided in queries is different. Please ignore the fact that account balance will be incorrect. I am interested in the behavior as I have a similar situation.

1
  • 1
    The order of the values in the in clause is not significant, rows may be visited in a non-deterministic order.
    – Jasen
    May 27, 2018 at 3:56

2 Answers 2

2

The order of the values in the in clause is not significant.

the rows may be visited in any order, this order can change at any time.

however with such a small set of targets and in the presence of an ordered index (not a hash index) the records will probably be visited in index order.

2

Yes, these statements can deadlock each other.

The fact that the transaction they're part of has only this single instruction does not suppress the possibility of a deadlock.

The false hope that it would might come from an optimist interpretation of what atomicity provides at the instruction level. An UPDATE does not acquire several row locks in a single atomic grab: it has to grab them one by one, and any of them has a separate chance to be impossible to acquire.

The question's query comes from the documention at https://www.postgresql.org/docs/current/static/explicit-locking.html, except there it's shown as two independent queries processing a single acctnum. Surely, if updating two rows in the same query solved the deadlock problem, the doc would mention it at this point. It does not mention it because it's not true.

2
  • This would mean the deadlock can occur depending on the order in which the rows are visited. If they are not based on order provided in the query (based on what @jasen mentioned above) then it is not safe to use one query and the only way to avoid deadlock is use individual updates in deterministic order. Am I correct?
    – Manish
    May 29, 2018 at 16:31
  • an alternative approach would be to look for deadlocks and retry them,.
    – Jasen
    Aug 16, 2023 at 21:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.