阅读中文版本

Advisory Locks

Advisory locks are application-defined locks managed by the database, letting code coordinate exclusive access to resources it names itself.

Definition

An advisory lock is a lock whose key is chosen by the application rather than tied to a table row, so the database stores and releases it while the meaning of the key stays in application code.

It is used for distributed singletons, migration guards, and serialising external work that has no natural row to lock. Compared to simpler alternatives, this concept fits best when the trade-off it represents is the one the system actually cares about.

When to use it

A common use case is applying it at the boundary where the cost of getting it wrong is highest, and the scenario usually involves a concrete guarantee the system must uphold. Unlike an ad hoc approach, it makes that guarantee explicit, reviewable, and testable.

How it works

Session-level advisory locks persist until explicitly released or the session ends, whereas transaction-level locks release automatically at commit or rollback.

With a connection pool the session may outlive the request, so a session lock can be left held and block every later caller that needs the same key.

Trade-offs and alternatives

Choose keyspace conventions carefully, because two features that pick the same numeric key will silently serialise each other with no visible relation. The main drawback is the extra complexity it introduces, so the benefit should clearly outweigh that cost before a team adopts it.

Common misconceptions

A persistent misconception is that advisory locks automatically expire when a process crashes. Another is that they grant the same guarantees as row-level locks. For example, an engineer who assumes otherwise may design around a guarantee that does not actually exist, which only becomes obvious under load or failure.

Anti-patterns to avoid

Two recurring anti-patterns are to holding a session-level advisory lock through a pooled connection and to using an advisory lock key that collides across unrelated features. Both tend to surface as hard-to-debug production issues rather than obvious test failures. Related concepts include Cost-Based Query Optimization, Database Connection Multiplexing, Autovacuum and Dead Tuple Reclamation.