Advisory locks - a mutex that is not a row lock

Day 38 claimed jobs with FOR UPDATE SKIP LOCKED on real rows. Today the resource is not a row at all — leader election, migrate-once, rebuild-once. PostgreSQL gives you application keys: pg_advisory_lock and friends.

An advisory lock is a cooperative lock identified by an integer key (or pair of ints) that you invent. PostgreSQL does not know what the key means. It only guarantees: at most one session (or transaction) holds that exclusive key at a time.

The problem

Eight app instances boot. Each wants to run a one-shot migration / become the cache rebuilder / be the shard leader. There is no job row to FOR UPDATE. If nobody coordinates, all eight “win.”

-- bad: every worker thinks it is the leader
UPDATE leader_flag SET winners = winners + 1;  -- runs 8 times

The API (what you actually type)

Function	Scope	Blocking?	Release
pg_advisory_lock(key)	Session	Yes — waits	pg_advisory_unlock or disconnect
pg_try_advisory_lock(key)	Session	No — returns false	Explicit unlock
pg_advisory_xact_lock(key)	Transaction	Yes	COMMIT / ROLLBACK
pg_try_advisory_xact_lock(key)	Transaction	No	COMMIT / ROLLBACK

Prefer xact locks for most app code: you cannot forget to unlock. Session locks are for long-lived “I am the leader until I die” patterns — and they leak if you mishandle unlock.

Worked example — try-lock leader

BEGIN;
SELECT pg_try_advisory_xact_lock(424242) AS got_it;
-- if got_it: do the one-shot work
COMMIT;  -- lock gone automatically

Losers see false, skip the work, and move on. No wait. No deadlock with row locks on unrelated tables (advisory locks live in a separate space — they do not conflict with FOR UPDATE on rows).

Namespacing keys

Keys are a global namespace per database. If service A uses 1 for “rebuild cache” and service B uses 1 for “nightly report,” they block each other by accident.

-- two-int form: (class, id) — e.g. class 1001 = migrations
SELECT pg_try_advisory_xact_lock(1001, 42);
-- or hash a string into bigint in the app, document the mapping

Shared vs exclusive (quick note)

Postgres also has pg_advisory_lock_shared — many readers, one writer style. Default exclusive is enough for leader election. Shared is for “many may observe, one may rebuild.”

Leak demo (session)

-- session A
SELECT pg_advisory_lock(7);
-- crash / forget unlock
-- session B
SELECT pg_try_advisory_lock(7);  -- false until A disconnects

That is why interview answers almost always start with: “I’d use the transaction-scoped variant.”

Lab — Podman PG 18, 8 concurrent backends, 30 runs

Real container til-postgres. Workers launched inside the container so backends overlap. Try-lock modes hold the key briefly (pg_sleep(0.08)) so concurrent peers observe false. The benchmark script and standalone write-up are not included in this repository.

Mode	Median wall ms	Outcome (30 runs)
No coordination	1,639.81	winners = 8 in 30/30
pg_try_advisory_lock	1,615.10	winners = 1 in 30/30
pg_try_advisory_xact_lock	1,622.86	winners = 1 in 30/30
pg_advisory_lock (block)	1,674.22	all 8 run serialized · winners = 8 in 30/30

Headline is correctness, not microseconds. Wall times are dominated by process spawn overhead (~1.6 s). The product claim: without a lock, 8/8 false leaders every run; with try-lock, exactly one winner 30/30.

Session vs xact — pick deliberately

Need	Use	Why
Work inside one transaction	*_xact_lock	Auto-release; no leak on error path
Leader for minutes/hours	Session lock + heartbeat	Survives many commits; unlock on resign/death
Don’t wait — skip if busy	pg_try_*	Returns boolean; peer continues
Must wait for the key	blocking pg_advisory_lock	Queue behind the holder

Day 38 vs Day 43 — which tool?

• Day 38 SKIP LOCKED: many rows (jobs) claimed in parallel. Lock is on the row.
• Day 43 advisory: one abstract resource (a key). No row required.
• Day 36 deadlocks: row locks in opposite order. Advisory keys can also deadlock if you take key A then B vs B then A — same rule: consistent order.

What breaks? — Anti-patterns

1. Session lock without unlock on every path. Exception after pg_advisory_lock, no unlock → key held until session ends. Fix: prefer xact locks; or try/finally unlock; or disconnect.

2. Using advisory locks instead of row locks for row data. You invent keys that drift from the primary key; bugs when someone updates the row without the key. Fix: if it is a row, lock the row (FOR UPDATE / SKIP LOCKED).

3. Assuming advisory locks survive a reconnect. Session locks die with the backend. A new pod is not the old session. Fix: re-acquire on boot; design for failover.

4. Blocking lock for “maybe I am leader.” You stall every loser behind the winner. Fix: pg_try_* for election; block only when the work must queue.

How it connects

• Day 38 (SKIP LOCKED): parallel claim of many jobs via row locks. Advisory = one abstract mutex.
• Day 36 (Deadlocks): wait-for cycles. Two sessions taking advisory keys in opposite order can still deadlock — order matters.
• Day 30 (Idempotency): leader work should still be safe if two leaders ever race (belt + suspenders).
• Day 10 / 24: transactions and MVCC; xact advisory is scoped to that transaction boundary.

Transfer questions

1. You need exactly one replica to run a nightly report. Session lock or xact lock? Why?
2. Why can advisory locks and FOR UPDATE on a table coexist without blocking each other?
3. Two services use key 1 for different meanings. What goes wrong, and how do you namespace keys?

Quiz

Your teach step

Close this lesson. Write the “Explain like I’m 10” and the “60-second LinkedIn version” from memory. Focus on: not a row lock; try vs block; session vs xact; lab 8 false leaders vs 1 winner 30/30. Post it, and paste the link.

Questions? Ask the agent — shared advisory locks, key namespacing, or leader lease patterns.