Four instances of your service each run a scheduled job every hour. You need exactly one of them to actually do the work.
The instinctive solution is a lock table: insert a row, do the job, delete the row. It works until an instance crashes mid-job, leaving a row nobody will delete, and every subsequent run skips the job until someone notices. Then you add timestamps and staleness checks, and now you are maintaining a distributed lock implementation.
Your database already has one.
What changed in 2026
- Horizontal scaling made this ordinary. Multiple instances of everything is the default deployment shape, so single-execution coordination became a routine requirement.
- Serverless made it harder. Short-lived connections and aggressive pooling interact badly with session-scoped locks, which caught teams out.
- Pooler awareness improved. Documentation and tooling got clearer about which pooling modes break advisory locks and why.
- Dedicated lock services stayed common. Advisory locks remained the pragmatic choice for teams who would rather not run another piece of infrastructure.
How they work
An advisory lock is a lock the database holds on a number you supply. The database attaches no meaning to it — there is no table, no row, no data involved. It simply guarantees that only one holder can have a given number at a time.
You choose the number, typically by hashing a string like nightly-report-job into an integer. Every instance requests the same lock; one gets it, the others do not.
Two scopes, and the difference matters:
Session-scoped. Held until explicitly released or the connection ends. This is the powerful property — if the process crashes, the connection drops and the lock releases automatically. No stale locks, no cleanup, no staleness heuristics.
Transaction-scoped. Released when the transaction commits or rolls back. Simpler and safer where the work fits in one transaction, because there is no way to forget to release it.
|
Lock table |
Advisory lock |
| Cleanup on crash |
Manual, with staleness logic |
Automatic |
| Storage |
A table and rows |
None |
| Contention |
Row locks, possible bloat |
In-memory |
| Granularity |
Whatever you model |
Any integer |
| Survives restart |
Yes, which is the problem |
No, which is the point |
| Cross-database |
Possible |
No |
The two variants
Blocking. Wait until the lock is available. Appropriate when the work must happen and order does not matter much — but a worker blocked indefinitely on a lock held by a stuck process is its own outage.
Try. Attempt to acquire; return immediately with success or failure. For scheduled jobs this is almost always what you want: one instance gets the lock and works, the others get false and go back to sleep. No queue, no waiting, no pile-up.
The distinction matters more than it sounds. Using the blocking variant for an hourly job means that if one run hangs, every subsequent instance is stacked up waiting, and you discover this when connections run out.
Connection pooling is the trap
Session-scoped advisory locks belong to a connection, not to your application logic. Connection poolers hand connections around between requests.
The failure modes are unpleasant and quiet. Acquire a lock, return the connection to the pool, and another request may get that connection while still holding your lock — or your release may run on a different connection and silently do nothing. In transaction-pooling mode, where a connection is only yours for the duration of a transaction, session-scoped locks are effectively unusable.
Three ways out:
Use transaction-scoped locks. They live and die with the transaction, which matches transaction pooling exactly. This is the simplest answer when the work fits in a transaction.
Hold a dedicated connection. For long-running work, take a connection outside the pool for the lock's lifetime. Explicit, and it costs a connection.
Check your pooler's mode. Session pooling preserves the semantics; transaction pooling does not. Know which you are running before relying on session locks — see connection pooling explained.
Common mistakes
- Session locks behind a transaction pooler. Silently broken, intermittently.
- Blocking acquire for scheduled jobs. One stuck run backs up everything behind it.
- Colliding lock numbers. Two unrelated jobs hashing to the same integer serialise against each other for no reason. Namespace them.
- Assuming they work across databases. Scoped to one instance; replicas do not share them.
- Forgetting to release session locks. They persist for the connection's life, which in a pool may be a very long time.
- Using them for fine-grained data locking. Row locks and isolation levels are the right tools for protecting rows.
FAQ
Do I need a dedicated lock service instead?
Not usually. If you already run a database and need single-execution coordination, advisory locks avoid another dependency. A dedicated service earns its place when you need locks independent of the database, cross-datacentre coordination, or features like lease renewal.
What happens during a failover?
Advisory locks are in-memory and instance-scoped, so a failover loses them. Anything relying on them must tolerate a brief window where two instances could both acquire. For a scheduled job that usually means an occasional duplicate run — plan for idempotency, per idempotency explained.
How do I pick lock numbers?
Hash a descriptive string, and use the two-integer form where available to namespace by application and job. Colliding with an unrelated component's number produces confusing serialisation that is hard to diagnose.
Can I see who holds a lock?
Yes — most databases expose current locks in a system view, including advisory ones and the session holding them. That is the first place to look when a job stops running.
Where to go next
For the pooling behaviour that breaks session locks, read connection pooling explained. For the row-level locking these do not replace, database isolation levels, and for the notification mechanism that pairs well with job coordination, listen and notify.