Advisory locks: locking a business key when there is no row to lock Intermediate

The problem

A cron job that recalculates reward points runs at 02:00 on all 3 pods (pod-a, pod-b, pod-c). If it runs twice, points are added twice.

Second case: two requests register an@example.com at the same time, both see “email does not exist yet” and INSERT. A row-level lock does not help: if the row does not exist, there is nothing to lock.

How do you lock a business key (a job, an email) instead of a row, and what does that lock actually guarantee?

What to choose (summary first)

Situation	Choose	Condition
Cron runs on only one pod	pg_try_advisory_lock + pg_advisory_unlock	Dedicated connection for the whole job; job is idempotent
Prevent duplicates per key when the row does not exist yet	pg_advisory_xact_lock	Every write path takes the same key
Report “busy” instead of waiting	pg_try_advisory_xact_lock	App handles false
Through PgBouncer transaction mode	Transaction-level only	Session-level is incompatible
“Only one writer” on a flaky network	Lease with a fencing token	An advisory lock is not enough

For unique emails within one table, UNIQUE on lower(email) is more reliable. Advisory locks are for rules a constraint cannot express, such as emails unique across accounts and pending_signups.

Core concepts

An advisory lock is a lock whose meaning the application defines: PostgreSQL only grants and queues on a number, not tied to a table or row. Code that forgets to take the lock can still write.

Function table (PostgreSQL 17). Session-level is held until unlock or session end; transaction-level is released automatically when the transaction ends and has no unlock function:

Function	Scope	Waits?	Returns
pg_advisory_lock(key)	session	yes	void
pg_try_advisory_lock(key)	session	no	boolean
pg_advisory_unlock(key)	session		true; false + WARNING if not held
pg_advisory_unlock_all()	session		void, releases every session lock
pg_advisory_xact_lock(key)	transaction	yes	void
pg_try_advisory_xact_lock(key)	transaction	no	boolean
..._shared of each function	same	same	Conflicts only with exclusive

Key: bigint or (int, int)

Each function takes one bigint key or two integer keys; the two key spaces do not overlap: pg_advisory_lock(1) differs from pg_advisory_lock(0, 1). The (int, int) form suits separating resource type and id; bigint suits keys hashed from strings.

Hashing a string into a key

The common approach: hashtext('signup:' || lower(email)) (32-bit) or hashtextextended(..., 0) (64-bit, the second argument is the seed).

• Collisions: two different strings can share a key and block each other (extra waiting, no loss of exclusion). With 32-bit, by the birthday paradox, the chance of some colliding pair exceeds 50% at about 77,000 keys. Use the 64-bit version.
• These are internal functions, not in the documented function list: do not persist hashed keys.
• Add a prefix (signup:, cron:) so key groups do not collide.

How it works

Three pods call pg_try_advisory_lock with the job’s key: who holds it, who skips, and what does the lock remember after release?

Three pods race for the cron: only pod-a gets true, but the lock does not stop a late run after unlock

◀ Prev▶ PlayNext ▶↺ RestartStep 1/5

pod-a calls pg_try_advisory_lock(key) on its own connection, gets true, and holds a session-level lock.

Step	Lock holder	Change
1	pod-a	true, lock tied to the session
2–3	pod-a	pod-b, pod-c get false, skip
4	nobody	unlock on the same connection
5	pod-c	a late run still gets it: needs a job_runs table

Behavior rules (per the docs, verified in the demo):

1. Session-level ignores transactions: a lock taken inside a transaction that then does ROLLBACK is still held.
2. Reentrant counting: take the same key 3 times and you must unlock 3 times. A session already holding it always succeeds when asking again.
3. Transaction-level is released on COMMIT/ROLLBACK; both kinds on the same key block each other normally.
4. When a session ends, including an abrupt client disconnect, all its session locks are released.

Observing in pg_locks

Rows have locktype = 'advisory'. A bigint key is split into classid (high half) and objid (low half), objsubid = 1; an (int, int) key has objsubid = 2. Locks only apply within one database.

SELECT a.application_name, l.granted,
       (l.classid::bigint << 32) | l.objid::bigint AS key
FROM pg_locks l JOIN pg_stat_activity a USING (pid)
WHERE l.locktype = 'advisory';

Set application_name per pod to know which pod holds it.

Operational details

• Advisory locks and regular locks share the lock table in shared memory, sized by max_locks_per_transaction (default 64, changing it requires a restart) and max_connections. Taking too many keys can leave the server unable to grant any lock.
• Calling a lock function alongside a LIMIT at the same query level can take extra locks; put the LIMIT in a subquery.
• pg_advisory_lock (the waiting version) honors lock_timeout; the try version does not need it.
• Taking two keys in opposite orders can still deadlock.

The biggest trap: a session-level lock is tied to the server connection, not to a request or pod.

Loading diagram…

In PgBouncer transaction mode, each transaction can run on a different server connection:

1. pod-a takes K on conn 1; the transaction ends, conn 1 returns to the pool still holding K.
2. pod-a unlocks on conn 2: false. K is stuck until conn 1 closes.
3. pod-b is given conn 1: try_lock(K) returns true because it is the same session.

PgBouncer explicitly states that session-level advisory locks do not work with transaction pooling. DISCARD ALL (which calls pg_advisory_unlock_all()) only runs on connection return in session pooling.

Real-world example

Recalculating reward points across 3 pods

Each pod opens a dedicated connection outside the pool and calls pg_try_advisory_lock(hashtextextended('cron:recalc-reward-points', 0)). On false it logs “skipped” and exits.

On true it checks job_runs to see whether today’s run is already done, runs the batch, writes job_runs, then closes the connection.

Signup pattern when the rule spans two tables:

BEGIN;
SELECT pg_advisory_xact_lock(hashtextextended('signup:' || lower($1), 0));
-- Check AFTER acquiring the lock
SELECT 1 FROM accounts WHERE lower(email) = lower($1)
UNION ALL
SELECT 1 FROM pending_signups WHERE lower(email) = lower($1);
-- Empty: INSERT; otherwise ROLLBACK and return 409
INSERT INTO accounts (email) VALUES ($1);
COMMIT;

Under Read Committed, each statement takes a fresh snapshot, so the check sees the row just committed. Under Repeatable Read, the snapshot comes from the lock statement, so it does not see it (demo line 7); stay on Read Committed.

Demo: three connections on PostgreSQL 17

Schema demo_advisory_locks, dropped at the end. Real output, with the pg_locks dump lines removed:

npx --prefix examples ts-node examples/src/advisory-locks/demo.ts

1. key = hashtextextended('cron:recalc-reward-points', 0) = 6110350855857795459
   pod-a: pg_try_advisory_lock -> true
   pod-b: pg_try_advisory_lock -> false
   pod-c: pg_try_advisory_lock -> false
2. pod-a locks inside BEGIN then ROLLBACK; pod-b try_lock -> false
3. lock twice; unlock #1 -> true, pod-b try -> false; unlock #2 -> true; unlock #3 -> false
   after 2 unlocks, pod-b try -> true
4. xact: pod-a -> true; pod-b while A's tx is open -> false; pod-b after COMMIT -> true
5. A holds bigint 1; B try (int 0, int 1) -> true
6. signup: waiting locks = 1; after A commits, B sees 1 account -> no INSERT
7. Repeatable Read: after acquiring the lock, B sees 0 accounts -> would INSERT a duplicate

Line 3: pod-a still holds one acquisition from step 2. The extra unlock prints WARNING: you don't own a lock of type ExclusiveLock.

Trade-offs

Pros

• Locks things that are not rows: a job, an email that does not exist yet.

• Writes no table, so no bloat; the server cleans up when the session ends.

• Has try to skip a turn; observable via pg_locks.

< slot="cons">

• Only a convention: a write path that forgets the lock is not protected.

• Session-level conflicts with pools and PgBouncer transaction mode.

• Not a lease: no expiry, no fencing token.

Cons

Production checklist

1. Prefer pg_advisory_xact_lock; use session-level only for long work like cron, on a dedicated connection.
2. Session-level: lock and unlock on the same connection, or close the connection instead of returning it to the pool.
3. Through PgBouncer transaction mode, session-level is forbidden.
4. Keys: hashtextextended with a prefix, or (int, int); document the list of keys.
5. Jobs under the lock must be idempotent, with a job_runs table.
6. The waiting version sets lock_timeout; the try version handles false.
7. Monitor pg_locks with locktype = 'advisory' by application_name.

Common mistakes

The most common mistake is mixing session locks with the driver’s pool:

Lock and unlock via pool.query: they may run on two different connections

✕ Common

await pool.query('SELECT pg_advisory_lock($1)', [key]);
await recalcRewardPoints();
await pool.query('SELECT pg_advisory_unlock($1)', [key]); // may return false

✓ Prefer

const client = await pool.connect();
try {
  const { rows } = await client.query('SELECT pg_try_advisory_lock($1) AS ok', [key]);
  if (rows[0].ok) await recalcRewardPoints();
} finally {
  client.release(true); // destroy the connection: the session lock goes with it
}

Why

Each pool.query borrows any connection. The lock stays on the first connection; another request that borrows it “inherits” the lock.

The second mistake is treating an advisory lock as a distributed lease:

Connection drops: the lock is lost but the job keeps running

The network drops mid-batch. The server notices the dead session, releases the lock, and pod-b acquires it while pod-a is still running. There is no fencing token to reject pod-a’s writes. You need conditional writes (version, job_runs) or a lease with fencing.

Misconception	Consequence	What to do
ROLLBACK releases a session lock	Lock stuck until the connection closes	Unlock explicitly or use an xact lock
One unlock is enough	Taken N times, still held N−1	Count lock/unlock pairs
The lock protects the table	Other code writes directly	Every write path takes the same key
try_lock = true means nobody ran this round	A late pod runs a second time	job_runs table

Self-check

1. pod-a calls pg_advisory_lock(K) inside BEGIN then ROLLBACK. What does pod-b’s try_lock(K) get, and why?
2. Why, through PgBouncer transaction mode, can pod-b get true for a key pod-a is holding?
3. Why does the signup pattern still create duplicates under Repeatable Read?

Summary

Key takeaways

• An advisory lock locks an application-defined number, used when there is no row to lock.
• Transaction-level releases automatically; session-level ignores ROLLBACK, counts reentrantly, and must be unlocked on the same connection.
• Session-level does not work through PgBouncer transaction mode.
• Keys from strings: 64-bit hashtextextended with a prefix.
• A lock is not a lease: lost when the connection drops, no fencing; the job still needs to be idempotent.

References

• PostgreSQL 17 — Advisory Locks: session vs transaction, rollback, reentrancy, the LIMIT warning.
• PostgreSQL 17 — Advisory Lock Functions: function signatures, the two key spaces.
• PostgreSQL 17 — pg_locks: which columns show the key.
• PostgreSQL 17 — max_locks_per_transaction: lock table size.
• PgBouncer — Features: features unavailable under transaction pooling.