Deadlock in PostgreSQL with subquery

Question

We're getting deadlocks in a situation where I thought they wouldn't happen due to sorting.

2019-09-11T20:21:59.505804531Z 2019-09-11 20:21:59.505 UTC [67] ERROR:  deadlock detected
2019-09-11T20:21:59.505824424Z 2019-09-11 20:21:59.505 UTC [67] DETAIL:  Process 67 waits for ShareLock on transaction 1277067; blocked by process 35.
2019-09-11T20:21:59.505829400Z  Process 35 waits for ShareLock on transaction 1277065; blocked by process 67.
2019-09-11T20:21:59.505833648Z  Process 67: UPDATE "records" SET "last_data_at" = '2019-09-11 20:21:58.493184' WHERE "records"."id" IN (SELECT "records"."id" FROM "records" WHERE "records"."id" IN ($1, $2) ORDER BY id asc)
2019-09-11T20:21:59.505843428Z  Process 35: UPDATE "records" SET "last_data_at" = '2019-09-11 20:21:58.496318' WHERE "records"."id" IN (SELECT "records"."id" FROM "records" WHERE "records"."id" IN ($1, $2) ORDER BY id asc)

Here, since the ids from the (admittedly unnecessary) subquery will be sorted, I'd think a deadlock shouldn't be possible. Does IN not follow the ordering of the passed array? If not, how can I fix this?

(The subquery is coming from our ORM.)

I think this answer should work for me: https://stackoverflow.com/questions/44660368/postgres-update-with-order-by-how-to-do-it — Andrew, Sep 12 '19 at 18:17
Note: the`ORDER BY` in the subquery is useless. (a subquery returns a SET, which is unordered by definition) — wildplasser, Sep 12 '19 at 18:52

Michel Milezzi · Accepted Answer · 2019-09-12T20:29:18.180

1

What's the ORM you're using?

You could use advisory locking to mitigate the deadlocks:

UPDATE 
    "records" 
SET 
    "last_data_at" = '2019-09-11 20:21:58.496318' 
WHERE 
    "records"."id" IN ($1, $2)
    --This function will return TRUE if getting 
    --a lock is possible for current transaction
    AND pg_try_advisory_xact_lock("records"."id")

Honestly, IMHO relying on an order by clause to avoid deadlocks seems a bit fragile solution.

More info about advisory locking functions here.

edited Sep 12 '19 at 20:29

answered Sep 12 '19 at 20:13

Michel Milezzi

10,087
3
21
36

Well, advisory locks are different from explicit locking. Advisory locks aren't enforced by database engine. – Michel Milezzi Sep 12 '19 at 20:19
2

You're right, I fixed this section. – Michel Milezzi Sep 12 '19 at 20:21
Thank you! So does this try to get an advisory lock on each row that matches the IN clause and lock it just while updating it, or does it somehow advisor lock all of them first and then update? – Andrew Sep 12 '19 at 22:12
1

AFAIK the 1st statement. The lock is evaluated at the predicate time, that is when it's evaluating the where clause. – Michel Milezzi Sep 12 '19 at 23:42
1

Appreciate your help! – Andrew Sep 13 '19 at 00:15

Deadlock in PostgreSQL with subquery

1 Answers1