Advisory Locks

Topic: Databases

Concrete problem

An application runs on five servers. Each server starts a cleanup job at midnight. Only one server may run the job. Two more cases have the same shape:

• A migration tool must run on one server at a time.
• A service calls an outside API that allows one call at a time for each customer.

None of these cases has a row to lock. The cleanup job is not a row. The outside API is not a table. Row Lock Modes and Table Lock Modes lock database objects. These cases need a lock on something that the database does not store.

The servers already share one thing: the database. Can the database hold a lock for them?

Background terms

Advisory lock

An advisory lock is a lock on a number. The number has no meaning to Postgres. The application decides what the number stands for. Postgres only checks that two sessions do not hold conflicting locks on the same number. The word “advisory” means that Postgres does not force the application to take the lock. The application must ask for it.

Lock key

The lock key is the number that names an advisory lock. The key is one 64-bit integer (bigint) or two 32-bit integers. The key space is separate for each database.

Session-level lock

A session-level advisory lock lasts until the session releases it or the session ends. A COMMIT or ROLLBACK does not release it. The session can take the same lock more than once. Each lock call needs its own unlock call.

Transaction-level lock

A transaction-level advisory lock lasts until the end of the transaction. The transaction cannot release it earlier. This is the same rule as for row locks and table locks.

Try form

The try form of a lock function asks for the lock and does not wait. It returns true if it got the lock. It returns false if the lock conflicts. This is the same idea as NOWAIT in Wait Policies.

Intuition

Row locks and table locks have two parts:

1. A name: a row or a table.
2. A conflict rule: which modes conflict.

Where Locks Live showed that Postgres stores table locks in the shared memory lock table. Each entry holds a name, a mode, and the holder. Postgres does not need to know that the name is a table. It only compares names.

An advisory lock uses the same machinery with one change. The application picks the name. The name is a number. So the application can say “the number $42$ means the nightly cleanup”. Postgres then gives the application these tools:

• Waiting: a session that wants a held number waits.
• Deadlock detection: the same detector as for row locks.
• Cleanup on session end: a crash releases session-level locks.

The lock protects no data. Row locks, UPDATE, and SELECT on any table ignore it. Two sessions are ordered only if both call the lock function with the same number. If one session forgets, no error occurs.

There are two modes only:

Mode	Function suffix	Conflicts with
Exclusive	(none)	Exclusive and shared on the same key
Shared	_shared	Exclusive on the same key

There are two lifetimes and two wait policies. This gives the function family:

	Wait	Try (no wait)
Session, exclusive	pg_advisory_lock(key)	pg_try_advisory_lock(key)
Transaction, exclusive	pg_advisory_xact_lock(key)	pg_try_advisory_xact_lock(key)

Shared forms add _shared after advisory, for example pg_advisory_xact_lock_shared. A session-level lock has a matching pg_advisory_unlock(key). A transaction-level lock has no unlock function.

The choice between the two lifetimes matters:

• Transaction-level is the safe default. A crash, an error, or a ROLLBACK always releases the lock. The lock cannot leak.
• Session-level fits work that spans many transactions. It needs care. A connection pool can give the same session to a different request. Then the lock outlives the code that took it.

Postgres stores the key as a number, not as text. To lock a name such as 'nightly-cleanup', hash it first: hashtext('nightly-cleanup'). Two different names can hash to the same number. Then they block each other. This is a harmless false conflict, but it is a surprise. Two features that both choose the key $1$ also block each other.

Numeric example

One cleanup job, three servers. Each server runs this at midnight:

SELECT pg_try_advisory_lock(42);

Time	Server	Result	Meaning
00:00:00.001	S1	true	S1 holds key 42. S1 starts the job.
00:00:00.002	S2	false	The lock conflicts. S2 skips the job.
00:00:00.003	S3	false	S3 skips the job.
00:07:10	S1	pg_advisory_unlock(42) returns true	S1 ends the job and releases key 42.

The try form gives the right behavior. S2 and S3 do not need to wait. The job is already running.

Stacking. One session runs pg_advisory_lock(5) three times. It holds the lock three times. One pg_advisory_unlock(5) leaves it held twice. Another session can get key 5 only after the third unlock.

Visual

graph TD
  subgraph App["Application: gives the number a meaning"]
    N["42 means the nightly cleanup"]
  end
  subgraph Shm["Shared memory lock table (pg_locks, locktype = advisory)"]
    L["key 42: ExclusiveLock, held by S1"]
    W["key 42: ExclusiveLock, requested by S2, waiting"]
  end
  S1["S1: pg_advisory_lock(42)"] --> L
  S2["S2: pg_advisory_lock(42)"] --> W
  W -.->|"conflicts with"| L
  N -.->|"only the application knows this"| L
  T["Any table, any row"] -.->|"not touched"| L

Notation

See the advisory locks that exist now:

SELECT pid, classid, objid, objsubid, mode, granted
FROM pg_locks
WHERE locktype = 'advisory';

With a bigint key, Postgres splits the key into classid (high 32 bits) and objid (low 32 bits), and sets objsubid = 1. With two int keys, classid and objid hold them, and objsubid = 2.

A key from a name:

SELECT pg_advisory_xact_lock(hashtext('nightly-cleanup'));

Plain-English translation

• “Take an advisory lock on 42” means “tell the database: I hold the name 42. Make anyone else who asks for 42 wait.”
• “Try the lock” means “ask, and tell me yes or no at once”.
• “Session-level” means “keep it until I let go or I disconnect”.
• “Advisory” means “the database does not check that you ask. Every user of the resource must follow the rule.”

What changes when parameters change

• Many keys held. Each held key uses one slot in the shared memory lock table. This is the same $N_{slots}$ limit as in Where Locks Live. Millions of advisory locks can fill the table. Row locks cannot.
• Shared instead of exclusive. Many sessions hold the key together. An exclusive request waits for all of them. This makes a reader-writer lock for any resource.
• Session-level behind a connection pool. The pool can reuse one session for another request. The first request may never unlock. Use the transaction-level form, or hold one connection for the whole job.
• A crash. The server releases every session-level lock of the dead session. The lock cannot be stuck forever, as a file lock can.
• Two keys collide. The two users of the number block each other. Keep a list of keys, or use the two-integer form with one integer as a namespace for each feature.

Practice problem

Try these on a later review. Do not open the answers first.

1. A deployment starts four pods. Each pod runs SELECT pg_try_advisory_xact_lock(42) inside a transaction and runs a report only when the result is true. Pod 2 gets true. What do pods 1, 3, and 4 get? What happens when pod 2 commits?
2. The billing code uses key $7$ for “one invoice run at a time”. The email code, written by another team, uses key $7$ for “one digest at a time”. What does a user see? How can you fix this?
3. Session A holds pg_advisory_lock_shared(9). Session B calls pg_advisory_lock_shared(9). Session C calls pg_advisory_lock(9). Then session D calls pg_advisory_lock_shared(9). Which sessions wait?

Answers

1. Pods 1, 3, and 4 get false. They skip the report. When pod 2 commits, the lock is released. A later try call succeeds. The pods that skipped do not retry unless the code does it. A skipped pod does not wait.
2. The invoice run and the digest run block each other. Nothing is wrong with the data. The system is slower than expected, and one run can wait for the other. The fix: use the two-integer form with a different first integer for each feature, for example (1, 7) and (2, 7).
3. B does not wait (shared and shared do not conflict). C waits for A and B. D also waits. A waiting exclusive request blocks later shared requests, so that C does not starve. This is the same queue rule as for ACCESS EXCLUSIVE in Table Lock Modes.

Quiz

Asked on 2026-10-05 after the lesson. Two answers were correct and one was a miss. A re-check followed.

Q1. Session A runs BEGIN; SELECT pg_advisory_lock(9); ROLLBACK; and stays connected. Session B runs pg_try_advisory_lock(9). What does B get?

• true
• false
• An error
• B waits

Answer and reasoning

Your answer: true (miss). Correct answer: false.

pg_advisory_lock is the session-level form. A ROLLBACK does not release it. B gets false. The option true tests the idea that every lock ends with the transaction. That idea is right for row locks, table locks, and the xact form. It is wrong for the session form. The option “An error” tests the idea that a rolled-back transaction cancels the lock call. The option “B waits” confuses the try form with the wait form.

Q2. Session A holds pg_advisory_lock(5). Session B runs UPDATE on any row of any table. Which statement is true?

• B waits until A unlocks key 5
• B waits only if the table has a row lock
• B does not wait for A
• B fails with SQLSTATE 55P03

Answer and reasoning

Your answer: B does not wait for A (correct). Correct answer: B does not wait for A.

An advisory lock protects no data. Only sessions that ask for key 5 are ordered. The first option tests the idea that all statements check advisory locks. The second option tests the idea that advisory locks turn into table locks. The fourth option tests the idea that advisory locks raise lock errors for writers.

Q3. Many workers call an outside API. The API allows one call at a time for each customer. No table holds a row for the API. Which approach fits?

• SELECT ... FOR UPDATE on the customer row
• LOCK TABLE customers IN SHARE MODE
• pg_advisory_xact_lock with a key from the customer id
• SERIALIZABLE isolation for each worker

Answer and reasoning

Your answer: pg_advisory_xact_lock with a key from the customer id (correct). Correct answer: pg_advisory_xact_lock with a key from the customer id.

The API is not a database object, so a number that stands for the customer fits. The other options lock a row, lock a table, or use SERIALIZABLE. Each of these protects database data and not the outside call. SERIALIZABLE aborts on read-write patterns. It does not order calls.

Re-check. Session A runs BEGIN; SELECT pg_advisory_xact_lock(3); COMMIT; and stays connected. Session B runs pg_try_advisory_lock(3). What does B get?

• false
• An error
• B waits
• true

Answer and reasoning

Your answer: true (correct). Correct answer: true.

The xact form ends with the transaction. The COMMIT released the lock, so B gets true. The option false applies the session-level rule to the xact form.

My reasoning

The Q1 miss was a slip in reading the function name. The user said so in a follow-up. The user then answered the re-check about pg_advisory_xact_lock correctly. Rule to keep: xact in the name means transaction-level. No xact means session-level.

The probe on 2026-10-05 showed that the user thought of a lock as a lock on a database object. This node moves the idea one step. A lock is a name plus a conflict rule. The name can mean anything.

Session history

• 2026-10-05 — First taught. See 2026-10-05 Databases - Postgres Locking.
• 2026-10-05 — Quiz: 2 of 3, plus a correct re-check. Q1 miss was a slip (function name). Level 2. See 2026-10-05 Databases - Postgres Locking.