Deadlock in PostgreSQL with subquery [duplicate]

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: stackoverflow.com/questions/44660368/… — Andrew
– Andrew, Commented Sep 12, 2019 at 18:17
Note: theORDER BY in the subquery is useless. (a subquery returns a SET, which is unordered by definition) — wildplasser
– wildplasser, Commented Sep 12, 2019 at 18:52

Michel Milezzi · Accepted Answer · 2019-09-12 20:29:18Z

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, 2019 at 20:29

answered Sep 12, 2019 at 20:13

Michel Milezzi

11.4k3 gold badges25 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Michel Milezzi Over a year ago

Well, advisory locks are different from explicit locking. Advisory locks aren't enforced by database engine.

Michel Milezzi Over a year ago

You're right, I fixed this section.

Andrew Over a year ago

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?

Michel Milezzi Over a year ago

AFAIK the 1st statement. The lock is evaluated at the predicate time, that is when it's evaluating the where clause.

Andrew Over a year ago

Appreciate your help!

Collectives™ on Stack Overflow

Deadlock in PostgreSQL with subquery [duplicate]

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related