Advisory locks: locking ideas, not rows

Advisory keys represent application-defined work. This example uses exclusive, database-local keys and no tables. All participating workers must agree on the key and lock protocol; advisory locking does not automatically lock application data.

A

B

lock(42)→ held

try-lock(42)→ false (no wait)

lock(42)→ ⏳ waits

unlock(42)

⏵ lock(42)→ completes

Two deploy runners must not migrate the same database at once. They agree lock 42 means 'migration in progress'.

A> SELECT pg_advisory_lock(42);
 pg_advisory_lock 
------------------
                  
(1 row)

B> SELECT pg_try_advisory_lock(42) AS got_it; -- false without waiting to acquire this advisory lock; latency is not measured
 got_it 
--------
 f      
(1 row)

try-lock says no without waiting. A runner can also queue up for the lock:

B> SELECT pg_advisory_lock(42);
⏳ B is waiting for a lock…

A> SELECT pg_advisory_unlock(42) AS released;
 released 
----------
 t        
(1 row)

⏵ B resumes:
 pg_advisory_lock 
------------------
                  
(1 row)

COMMIT does not release A's session-level advisory lock on key 9.

A> BEGIN;
BEGIN

A> SELECT pg_advisory_lock(9);
 pg_advisory_lock 
------------------
                  
(1 row)

A> COMMIT;
COMMIT

B> SELECT pg_try_advisory_lock(9) AS got_it;
 got_it 
--------
 f      
(1 row)

A> SELECT pg_advisory_unlock(9) AS released;
 released 
----------
 t        
(1 row)

After A commits, the next try-lock checks that its transaction-level key 7 is available.

A> BEGIN;
BEGIN

A> SELECT pg_advisory_xact_lock(7);
 pg_advisory_xact_lock 
-----------------------
                       
(1 row)

B> SELECT pg_try_advisory_lock(7) AS got_it;
 got_it 
--------
 f      
(1 row)

A> COMMIT;
COMMIT

B> SELECT pg_try_advisory_lock(7) AS got_it; -- freed by A's COMMIT alone
 got_it 
--------
 t      
(1 row)

B> SELECT pg_advisory_unlock(7);
 pg_advisory_unlock 
--------------------
 t                  
(1 row)

B> SELECT pg_advisory_unlock(42);
 pg_advisory_unlock 
--------------------
 t                  
(1 row)

Verified against PostgreSQL 18.6 · Run it yourself · Scenario source

B's try-lock returns false while A holds key 42. A blocking request waits until A unlocks. The try-lock avoids an advisory-lock wait; the scenario does not measure end-to-end latency. The PostgreSQL 18 contract also permits shared modes and repeated acquisitions by the same session. Repeated acquisitions need matching unlocks.

Session locks vs. transaction locks

COMMIT leaves A's session lock on key 9 held. A's transaction lock on key 7 prevents B's acquisition until A commits; then B's try-lock succeeds. These are Demonstrated behaviors. The manual documents that session locks survive rollback and end on explicit release or session termination. Transaction-level locks end with the transaction. Rollback and holder termination are Documented contracts, not executed cases here. A lost connection is not necessarily detected immediately.

Prefer a transaction-level lock for work contained in one transaction; use session locks when work must span transactions and arrange explicit cleanup. This is design advice, not a universal workload rule. The function contract defines separate key spaces for one 64-bit key and two 32-bit keys. Within a database and key space, features that choose the same key contend. Maintain an application key registry to avoid accidental collisions.

Further reading

• PostgreSQL 18: Advisory Locks
• PostgreSQL 18: Advisory Lock Functions
• The same lesson on MySQL

The reconstructed support assessment records the exact manual support and execution limits for this lesson.