The essentials

Quick reference

One focused task per row. Jump to the related section for complete, working examples.

UseSyntaxExamples
Lock for the transactionSELECT pg_advisory_xact_lock(7001, 42);View examples
Try a transaction lockSELECT pg_try_advisory_xact_lock(7001, 42);View examples
Take a shared lockSELECT pg_advisory_xact_lock_shared(7001, 42);View examples
Try a shared lockSELECT pg_try_advisory_xact_lock_shared(7001, 42);View examples
Lock for the sessionSELECT pg_advisory_lock(8001, 17);View examples
Try a session lockSELECT pg_try_advisory_lock(8001, 17);View examples
Unlock one session levelSELECT pg_advisory_unlock(8001, 17);View examples
Unlock all session locksSELECT pg_advisory_unlock_all();View examples
Bound lock waitsSET LOCAL lock_timeout = '2s';View examples
Order multiple keysSELECT DISTINCT resource_id FROM app.requested_resources WHERE batch_id = 88 ORDER BY resource_id;View examples
Limit before lockingSELECT pg_advisory_xact_lock(q.id) FROM ( SELECT id FROM app.jobs ORDER BY id LIMIT 10 ) AS q;View examples
List advisory locksSELECT pid, mode, granted, classid, objid, objsubid FROM pg_locks WHERE locktype = 'advisory';View examples
Find advisory waitersSELECT pid, mode, waitstart FROM pg_locks WHERE locktype = 'advisory' AND NOT granted ORDER BY waitstart;View examples
Find blocking processesSELECT pid, pg_blocking_pids(pid) AS blockers FROM pg_locks WHERE locktype = 'advisory' AND NOT granted;View examples
Use one 64-bit keySELECT pg_try_advisory_xact_lock(9223372036854770000::bigint);View examples
Namespace with two keysSELECT pg_try_advisory_xact_lock(7001, 42);View examples
Detect recoverySELECT pg_is_in_recovery();View examples

Advisory locks coordinate application-defined resources without locking a particular row or table, but PostgreSQL does not enforce the convention they represent. Choose a stable collision-safe key scheme, prefer transaction-scoped locks for transactional work, bound waits, acquire multiple keys consistently, and design for reconnects and failover because advisory state is in memory and local to one database server.

Step by step

Detailed examples

01

Prefer transaction-scoped locks around transactional work

Transaction-level advisory locks release automatically on ordinary COMMIT or ROLLBACK and cannot be manually unlocked. PREPARE TRANSACTION is the important exception: two-phase state retains its locks until COMMIT PREPARED or ROLLBACK PREPARED, so monitor and resolve orphaned prepared transactions promptly. Acquire the lock and perform the protected read or write in the same explicit transaction and on the same connection. The lock only coordinates clients that use the identical key convention; it does not stop other SQL from changing the underlying rows.

Serialize one account close operation
BEGIN;
SELECT pg_advisory_xact_lock(7001, 42);

UPDATE app.accounts
SET closed_at = clock_timestamp()
WHERE id = 42
  AND closed_at IS NULL;
COMMIT;

Note: 7001 is an application-owned namespace and 42 is the resource ID; document both centrally.

Skip work already owned elsewhere
BEGIN;
SELECT pg_try_advisory_xact_lock(7001, 42) AS acquired;
-- Continue only when acquired is true.
COMMIT;
Back to quick reference ↑
02

Choose exclusive or shared semantics deliberately

Exclusive locks conflict with exclusive and shared locks on the same key. Shared locks coexist with other shared holders but conflict with an exclusive holder. Use shared mode only when concurrent readers genuinely need to exclude a coordinated writer; it is not a substitute for MVCC visibility, row locks, constraints, or SERIALIZABLE isolation.

Coordinate readers with a maintenance writer
BEGIN;
SELECT pg_advisory_xact_lock_shared(7100, 8);
SELECT generated_at, payload
FROM app.snapshots
WHERE id = 8;
COMMIT;

Note: The maintenance writer must use the exclusive function with the same (7100, 8) key.

Back to quick reference ↑
03

Reserve session locks for connection-owned lifetimes

A session-level lock survives transaction rollback and remains until explicitly released or the connection ends. Repeated acquisitions stack, so each requires a matching unlock. This is dangerous with connection pools: returning a connection does not end its database session, and a later borrower can inherit the lock. Use a dedicated connection, pair acquisition and release in guaranteed cleanup, and treat disconnect as implicit release.

Balance a session-scoped leader lease
SELECT pg_try_advisory_lock(8001, 17) AS acquired;
-- Keep this dedicated connection checked out while acting as leader.
SELECT pg_advisory_unlock(8001, 17) AS released;

Note: A network break releases the lock at session termination; the application must stop leader work when connection health is lost.

Back to quick reference ↑
04

Bound waits and acquire key sets in one order

Blocking advisory-lock calls participate in normal deadlock detection, but deadlock resolution aborts a transaction and is not flow control. Prefer try-locks for skip-or-retry workflows, add jittered backoff in the client, and set lock_timeout locally for bounded blocking. When multiple keys are required, sort and deduplicate them before acquisition so every worker follows the same order.

Acquire a sorted resource set with a timeout
BEGIN;
SET LOCAL lock_timeout = '2s';
DO $body$
DECLARE
  resource_key integer;
BEGIN
  FOR resource_key IN
    SELECT DISTINCT resource_id
    FROM app.requested_resources
    WHERE batch_id = 88
    ORDER BY resource_id
  LOOP
    PERFORM pg_advisory_xact_lock(9001, resource_key);
  END LOOP;
END
$body$;
-- Perform the protected batch operation here.
COMMIT;
Back to quick reference ↑
05

Constrain rows before calling a locking function

SQL expression evaluation order is not generally guaranteed. Calling an advisory-lock function in the same SELECT that has LIMIT can acquire more locks than the application expects if the function runs before the limit. Put selection and ordering in a subquery before calling the function, and prefer transaction locks so unexpected acquisitions cannot dangle beyond transaction end.

Select a bounded batch before locking it
SELECT pg_advisory_xact_lock(q.id)
FROM (
  SELECT id
  FROM app.jobs
  WHERE state = 'ready'
  ORDER BY id
  LIMIT 10
) AS q;

Note: Do not move pg_advisory_xact_lock into the subquery predicate or select list.

Back to quick reference ↑
06

Observe holders and waiters before intervening

pg_locks reports advisory records with locktype='advisory'; granted=false identifies waiters, and pg_blocking_pids maps them to direct blockers. Left-join pg_stat_activity because locks retained by a prepared transaction have a null pid; correlate those rows with pg_prepared_xacts by virtual transaction. For performance diagnosis, measure wait duration and contention rate instead of treating every held advisory lock as a problem. Activity and query-text visibility depend on monitoring privileges. Canceling a query, terminating a backend, or resolving two-phase state has wider transactional effects, so confirm ownership and impact first.

Diagnose advisory contention
SELECT l.pid, a.usename, a.application_name, px.gid, l.mode, l.granted,
       l.waitstart, pg_blocking_pids(l.pid) AS blocking_pids,
       l.classid, l.objid, l.objsubid
FROM pg_locks AS l
LEFT JOIN pg_stat_activity AS a USING (pid)
LEFT JOIN pg_prepared_xacts AS px
  ON l.virtualtransaction = '-1/' || px.transaction
WHERE l.locktype = 'advisory'
ORDER BY l.granted, l.waitstart NULLS FIRST, l.pid;
Back to quick reference ↑
07

Own a stable, collision-resistant key scheme

PostgreSQL accepts either one signed bigint or two signed integers; those key spaces do not overlap. Reserve numeric namespaces per resource type and verify IDs fit the chosen width. Hash-derived keys can collide, especially 32-bit hashes, and PostgreSQL hash functions are not an external durable identifier contract. Store or derive keys consistently across every cooperating service and version the convention when it changes.

Use a two-part application namespace
-- Registry: 7001 = account mutation, second key = signed 32-bit account ID.
BEGIN;
SELECT pg_advisory_xact_lock(7001, 42);
UPDATE app.accounts SET revision = revision + 1 WHERE id = 42;
COMMIT;
Back to quick reference ↑
08

Design for local, voluntary, non-durable coordination

Advisory keys carry no built-in ownership or authorization meaning, and functions normally receive PUBLIC execute privilege. A wrapper alone does not prevent direct calls: if lock keys form a trust boundary, manage EXECUTE on every applicable built-in overload and expose a narrowly granted wrapper only after hardening its search_path, inputs, and owner. Advisory locks are local to a database and live in the server's shared-memory lock table alongside regular locks. They work during recovery but are not written to WAL: primary and standby locks do not conflict, and restart or promotion does not preserve them. Route every contender for one write-coordination domain to the same writable primary, fence old leaders externally when required, and keep lock counts bounded because exhausting shared lock memory can prevent new locks server-wide.

Confirm the coordination endpoint
SELECT current_database() AS database,
       pg_is_in_recovery() AS is_standby,
       inet_server_addr() AS server_address,
       inet_server_port() AS server_port;

Note: After failover or reconnect, assume no advisory lock is held until the new session acquires it.

Back to quick reference ↑

Keep exploring

More SQL cheat sheets

Sources and further reading

References

Authoritative documentation used to verify and expand this cheat sheet.

  1. PostgreSQL Global Development GroupPostgreSQL: Explicit Locking — Advisory Lockspostgresql.org
  2. PostgreSQL Global Development GroupPostgreSQL: Advisory Lock Functionspostgresql.org
  3. PostgreSQL Global Development GroupPostgreSQL: pg_lockspostgresql.org
  4. PostgreSQL Global Development GroupPostgreSQL: Lock Management Configurationpostgresql.org
  5. PostgreSQL Global Development GroupPostgreSQL: Hot Standbypostgresql.org
  6. PostgreSQL Global Development GroupPostgreSQL: Monitoring Database Activitypostgresql.org
  7. PostgreSQL Global Development GroupPostgreSQL: Privilegespostgresql.org

Help us improve

Found a typo or missing example?

Tell us what would make this cheat sheet clearer, more complete, or more useful.

Share feedback