Yesterday I stumbled across a deadlock caused by two apps concurrently updating the same table. The operation would have been harmless in this instance if it weren't for the deadlock. The pattern is as below, which I've shamelessly copied from here.
-- Transaction 1
UPDATE customer SET ... WHERE id = 1
UPDATE customer SET ... WHERE id = 2
-- Transaction 2
UPDATE customer SET ... WHERE id = 2
UPDATE customer SET ... WHERE id = 1
There are countless explainers as to why this deadlocks, and suggestions as to best practices to avoid the pattern in the first place. What I haven't really found is a consistent suggestion as to what locking strategy is best employed for the simple case above, assuming you want or are happy to use explicit table locking to work around it.
The Postgres documentation is generally very good, but I find the explicit locking doc a bit of a head-spin in the way things are described in a sort of circular reference manner.
My first thought, given the docs state "this lock mode will be acquired by any command that modifies data in a table", was that ROW EXCLUSIVE would force a queue. But then in the conflict matrix on the same page it shows ROW EXCLUSIVE does not conflict with itself, so that isn't going to help, is it? So I'm thinking maybe SHARE ROW EXCLUSIVE, but given that is obtained by CREATE and UPDATE table it sounds perhaps heavy handed.
So I guess I'm hoping some long-in-the-tooth Postgres guru can say which lock they'd use here, assuming no complicating factors?
Best Answer
The explicit locking doc says acquire in a consistent order, so perhaps always do the lower
idfirst.For explicit locking perhaps row level
SELECT 1 FROM customer WHERE id IN (1,2) FOR UPDATE.You should handle the deadlock at the query level, the schematics of a deadlock means its not obvious the the database how it should continue and leaves it for a application designer to implement. Exit and watchdog seems rather extreme.