What are Advisory Locks and When Should You Use Them?
Learn what advisory locks are, how they differ from row/table locks, and how to use them to coordinate scheduled jobs.
Expected Interview Answer
An advisory lock is an application-defined lock that the database manages on behalf of an arbitrary key you choose, rather than automatically on a row or table, and it only has effect if cooperating sessions explicitly check for it — it does not block ordinary reads or writes to any table.
Unlike row or table locks, which the engine acquires automatically to protect data integrity, an advisory lock is requested manually using functions like PostgreSQL's pg_advisory_lock(key), and it is purely a coordination signal: nothing stops an uncooperative session from reading or writing the underlying data while the advisory lock is held. This makes advisory locks ideal for application-level coordination that has no natural row to lock, such as ensuring only one instance of a scheduled job runs at a time across a cluster, serializing access to an external resource, or implementing a distributed mutex without standing up a separate lock service. They are lightweight, session- or transaction-scoped, and released automatically on disconnect, avoiding orphaned locks.
- Coordinates work with no natural row or table to lock
- Prevents duplicate execution of scheduled jobs across instances
- Lightweight compared to standing up a dedicated distributed lock service
- Automatically released on session/transaction end, avoiding orphaned locks
AI Mentor Explanation
An advisory lock is like a team captain informally telling teammates "I've got this ball," a verbal claim rather than a physical fence around the pitch. Any player who ignores the call and goes for the ball anyway can still do so, since nothing physically stops them. Advisory locks work the same way: they are a cooperative signal that well-behaved sessions check and respect, but they do not physically block anyone who ignores the convention from touching the same data.
Step-by-Step Explanation
Step 1
Choose a coordination key
Pick an arbitrary integer or string key representing the resource to coordinate, since it has no natural row to lock.
Step 2
Acquire the advisory lock
A session calls the database function (e.g. pg_advisory_lock(key)) to take the lock, blocking or failing if another session already holds it.
Step 3
Perform the coordinated work
Only the session holding the lock proceeds with the guarded operation, such as running a scheduled job.
Step 4
Release the lock
The session releases the lock explicitly or it is released automatically when the session or transaction ends.
What Interviewer Expects
- Understanding that advisory locks are application-managed, not automatic
- Awareness that they only work if all participants cooperate and check them
- A concrete use case, such as preventing duplicate cron/scheduled job execution
- Distinction between session-level and transaction-level advisory locks
Common Mistakes
- Assuming advisory locks physically prevent all reads/writes to the underlying rows
- Using an advisory lock where a normal row lock would be simpler and safer
- Forgetting to release session-level advisory locks explicitly if not using transaction scope
- Not accounting for uncooperative processes that never check the lock
Best Answer (HR Friendly)
“An advisory lock is a lock you ask the database to hold on your behalf for a key you choose, like a job name, rather than a lock on any actual row. It's useful for coordination problems that don't map to a table, like making sure only one instance of a scheduled job runs at a time, but it only works if every process actually checks for the lock — it doesn't physically stop anyone from touching the real data.”
Code Example
-- Try to acquire the lock; returns true if acquired, false if already held
SELECT pg_try_advisory_lock(42) AS got_lock;
-- If got_lock is true, this instance proceeds with the job:
-- (application code runs the exclusive job logic here)
-- Release the lock when finished
SELECT pg_advisory_unlock(42);
-- Alternative: blocking variant that waits until the lock is free
SELECT pg_advisory_lock(42);
-- ... do work ...
SELECT pg_advisory_unlock(42);Follow-up Questions
- What is the difference between a session-level and a transaction-level advisory lock?
- How would you use advisory locks to prevent a scheduled job from running twice?
- Why do advisory locks not protect against uncooperative processes?
- When would you choose an advisory lock over a dedicated distributed lock service like a coordination store?
MCQ Practice
1. What key difference sets advisory locks apart from row or table locks?
Advisory locks are manually requested on an arbitrary key and only coordinate sessions that explicitly check them; they do not enforce data access.
2. Which scenario is a good fit for an advisory lock?
Advisory locks excel at coordination problems with no natural row to lock, like serializing a distributed scheduled job.
3. What happens if a process ignores the advisory lock convention entirely?
Advisory locks are purely cooperative signals; the database does not use them to restrict actual data access for non-checking processes.
Flash Cards
What is an advisory lock? — An application-defined lock on an arbitrary key, managed by the database but only respected by cooperating sessions.
Do advisory locks block table reads/writes? — No, they do not physically restrict data access; they only coordinate processes that explicitly check them.
Classic advisory lock use case? — Ensuring only one instance of a scheduled job runs at a time across multiple servers.
When are advisory locks released? — Explicitly on unlock, or automatically when the owning session or transaction ends.