A PostgreSQL advisory lock only coordinates the code that checks it — the database itself lets any connection ignore it and touch the same rows unchecked, which is exactly what its documentation warns.
I used a Postgres advisory lock to stop two of my deploy agents from running the same migration at once, and the first time I actually tested what happens when a third piece of code just ignores the lock, I learned something the name should already have told me. Nothing stopped it. The migration ran twice, back to back, because the lock was never a barrier the database was enforcing on my behalf — it was a flag that only mattered to code that bothered to check it first.
The documentation says this plainly, if you read the section on advisory locks rather than skimming past it once the feature does what you initially wanted. Advisory locks are called that, the PostgreSQL documentation explains, because the system does not enforce their use — it is up to the application to use them correctly. The database will happily let a connection touch the exact rows a lock is supposedly protecting, so long as that connection never asks whether the lock is held. It also distinguishes two lifetimes worth knowing before you rely on either: a session-level lock survives a rolled-back transaction, while a transaction-level one releases the moment the transaction ends, which matters enormously if you assumed the wrong one was covering the gap you actually cared about.
That single fact changes what an advisory lock is actually for. It is not a guarantee the database gives you, the way a foreign key constraint or a unique index is a guarantee. It is an agreement between pieces of code that all of them have chosen to honour, and the moment one of them has not — a new script, a manual connection, an agent that was never told the convention exists — the lock protects nothing, silently, with no error at the point of the actual violation. A unique constraint fails loudly the instant something violates it. An unchecked advisory lock fails by doing nothing at all, which is a far worse failure mode, because there is no signal telling you the coordination broke down.
Running several coding agents against the same database exposed exactly this gap for me. I had the migration pipeline itself taking the lock correctly, and I assumed that was the whole job done. What I had not accounted for was every quick one-off script I or an agent might run directly against the database outside that pipeline — a manual fix, a debugging query, a seed script run by hand because the proper path felt slower that day. None of those checked the lock, because none of them had been written with the lock in mind at all, and an advisory lock that only one of several writers respects is not providing the coordination I thought I had bought.
The fix was not a cleverer lock. It was accepting that an advisory lock only ever coordinates the code that was written to check it, and treating every new script or agent that touches that table as a place the convention has to be applied again, deliberately, rather than assumed. Where I actually need the database itself to refuse a bad state — not just discourage it among cooperating writers — I reach for a constraint the engine enforces regardless of who is asking, because that is the difference between a rule and a request. An advisory lock is a request, and a genuinely useful one; I still use it for exactly the coordination it is good at. I just no longer mistake it for something the system is enforcing on my behalf, because it told me, in its own documentation, that it is not.