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?

  • 1
    Clearly you need EXCLUSIVE table level lock to avoid deadlocks in this scenario. Say good-bye to concurrency though.
    – mustaccio
    Nov 13 '18 at 13:46
  • What's wrong with handling the deadlock, assume that this scenario happened, and trying again?
    – danblack
    Nov 13 '18 at 21:04
  • @danblack Well that's effectively what's happening now, but only because it raises an exception and the (deliberate, handled) response is to exit the app for a watchdog restart. It's just the conflict wasn't foreseen, and I was curious what the best practice was if using locks as a way of handling this.
    – whoasked
    Nov 14 '18 at 12:11

The explicit locking doc says acquire in a consistent order, so perhaps always do the lower id first.

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.

  • 1
    If you want an explicit lock order, use an ORDER BY; the planner is under no obligation to respect the order within an IN (...) clause Nov 15 '18 at 15:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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