Database evidence lab

PostgreSQL Advisory Locks

A pool checkout is a logical handle. PostgreSQL assigns advisory-lock ownership to the backend session or transaction behind it. Test that boundary before using a lock to coordinate SaaS jobs.

Executed server
PostgreSQL 17.11; two real backend sessions
Repeated ownership
Same session acquired again; independent backend remained excluded
Release count
Up to 4 session acquisitions needed 4 unlocks; one commit ended transaction ownership
Scope
15 cases; simulated logical reuse; no PgBouncer or benchmark
A workshop custodian keeps two keys on her belt while a departing worker returns a completed ticket. A new worker faces the still-locked tool cabinet.
Story illustration: returning the work ticket does not return the key held by its custodian.

A successful try can mean the same owner

Call pg_try_advisory_lock(73475001) twice on one PostgreSQL backend. Both calls can return true. An independent backend still cannot acquire that exclusive lock. The second success does not establish that a new application job became an independent owner: the database sees the same session acquiring its lock again.

This matters when a connection pool lends one physical connection to successive logical callers. Returning a connection to an application pool is not the same operation as terminating its PostgreSQL backend. If a session lock remains, the next checkout can inherit that session state. Our lab simulates those two logical uses on one backend and contrasts them with a second real backend. It runs no pool library or PgBouncer process.

The PostgreSQL advisory-lock contract offers application-defined, cooperative locks. Session functions such as pg_advisory_lock wait. pg_try_advisory_lock returns a Boolean without waiting for the requested lock. Their transaction counterparts are pg_advisory_xact_lock and pg_try_advisory_xact_lock. Choose the lifetime before choosing whether to wait.

Two logical callers reuse backend A, which retains the session lock and accepts a repeat acquisition. Independent backend B remains blocked. An explicit transaction owns the alternative lock until commit or rollback.
Ownership follows the physical backend. The logical checkout sequence is simulated. Both database sessions are real. View full-size figure.

All executed acquisitions below are exclusive. PostgreSQL also provides shared variants, with different conflict relationships. The function reference defines those APIs. This experiment does not test shared-lock upgrades.

Put transaction-scoped work inside one transaction

A transaction advisory lock is released when its transaction ends. A session advisory lock survives transaction rollback. In our two-session checks, the competing backend entered after rolling back a transaction lock, but remained excluded after rolling back a session-lock acquisition.

Illustrative SQL sequence: run both statements and the protected database work on the same checked-out connection.
BEGIN;
SELECT pg_try_advisory_xact_lock(73475001);
-- Application: proceed only if the returned value is true.
-- Run the protected database work on this connection.
COMMIT;

The Boolean branch belongs in application control flow. If acquisition returns false, skip or defer the protected work and end the transaction. A SQL client that prints false and then blindly runs the next command has not implemented mutual exclusion.

Autocommit changes the boundary. Executing pg_try_advisory_xact_lock as a standalone statement acquired and released the transaction lock before the following statement could use it. Our independent backend then acquired the same key immediately. Use an explicit transaction when several statements must share the protection.

Keep the transaction short. Holding a database transaction while waiting for a remote API extends connection occupancy and leaves external completion outside the database commit boundary. A transaction lock alone does not make the remote action transactional.

Count acquisitions before counting releases

For each acquisition count from 1 through 4, backend A acquired the same key repeatedly. Backend B tried between releases. Session locks required one matching unlock per successful acquisition. Repeated transaction-lock acquisition needed only one final COMMIT to release that transaction's ownership.

For 1, 2, 3 and 4 successful acquisitions, session unlock calls needed were 1, 2, 3 and 4. Transaction-end operations needed were 1, 1, 1 and 1.
Eight executed cases from results.json. The vertical axis counts release operations: explicit session unlock calls versus transaction commits. It measures no latency or throughput. View full-size figure.

With 4 session acquisitions, the contender results after successive unlocks were false, false, false, true. A finally block containing a single unlock is insufficient if nested helpers each acquire the same session lock. Give one layer responsibility for acquisition and balanced release, or make the intended nesting explicit.

The same run exposed only one matching row in pg_locks for each of these repeated session acquisitions. One visible row therefore did not mean one outstanding unlock. Track balanced calls in the owning code. Do not reconstruct a nesting count from that row count.

Check who performed the unlock

A cleanup query on another checkout can reach a different physical backend. In our test, backend B's pg_advisory_unlock returned false and the owner's lock remained. Treat that result as failed cleanup, not a harmless indication that the job is already complete.

Seven executed boundary checks on PostgreSQL 17.11. Contender means independent backend B.
CaseActionObserved outcome
Session rollbackA acquires, then rolls backB cannot enter
Transaction rollbackA acquires transaction lock, then rolls backB can enter
Wrong backendB tries to unlock A's session lockUnlock false; B cannot enter
Unlock rollbackA unlocks inside a transaction, then rolls backB can enter
AutocommitA takes a transaction lock in one statementB can enter after the statement
Logical reuseSecond logical caller uses backend ASecond try true; B cannot enter
Session exitA physically closes its backend connectionB can enter

Session unlocking is also independent of transaction rollback. We acquired a session lock, began a transaction, unlocked and rolled back. The rollback did not restore the lock. A cleanup design that assumes SQL rollback undoes both acquisition and release is wrong in both directions for session locks.

pg_advisory_unlock_all() releases all session-level advisory locks held by the current session. That broad scope is unsuitable as an unreviewed local cleanup shortcut when other code legitimately owns locks on that backend. It does not reach another backend. Test the pool's actual reset contract before relying on it.

Physical session exit released the lock in this controlled local test. That result does not measure how quickly a server detects a dead network peer. Connection loss, cancellation, server failover and a worker continuing external work require additional failure tests.

Match the pool mode to the required lifetime

An application pool and a server-side transaction pooler are separate layers. A checked-out application connection may still pass through PgBouncer. Pinning the application handle alone does not prove that the PostgreSQL server session remains yours across transactions.

The PgBouncer feature matrix marks session-level advisory locks as supported under session pooling and unsupported under transaction pooling. Transaction pooling returns the server connection at the transaction boundary. Do not carry a session lock across that boundary and expect a later transaction to reach its owner.

For work confined to one explicit database transaction, a transaction advisory lock matches that lifetime. For a session lock that deliberately spans transactions, require a documented session-affinity path and release on the same backend. A dedicated direct connection can make that ownership explicit, but it still needs error handling and physical-close cleanup.

PgBouncer's reset configuration describes when its reset query runs and the separate transaction-pooling assumptions. Do not assume a reset query repairs use of unsupported session features. Configuration and version checks belong in your deployment review. The lab supplies PostgreSQL lifecycle evidence, not a verified PgBouncer configuration.

If a required release fails or the connection's state is uncertain, do not knowingly return it as a clean reusable connection. Use the driver or pool's supported discard path. Verify that path in the actual library. For capacity and queueing symptoms, the separate connection pool saturation guide owns sizing and wait-budget analysis.

Name the resource and its tenant boundary

An advisory-lock key identifies the business resource that cooperating callers must coordinate. Use a documented namespace plus the relevant tenant and resource identity. A backend PID identifies an owner for diagnostics. It is not the resource key. A random checkout identifier would let two jobs for the same resource acquire different locks.

PostgreSQL accepts one signed 64-bit key or a pair of signed 32-bit keys. The two key spaces do not overlap. Do not put a large tenant identifier into a 32-bit slot without a reviewed mapping, or switch forms and assume existing callers will conflict. In a shared database, define how every producer obtains the same key for the same tenant-resource pair.

Hashing a composite identity into a fixed-size key can collide. An unrelated-resource collision can create unwanted serialization and delay. Inconsistent encodings for the same resource are more dangerous: callers may stop contending at all. Specify canonical encoding, namespace/version rules and tests before changing the mapping. Advisory locks are scoped to the database, so equal keys in different databases are not a cross-database mutex.

The tenant boundary also depends on which data and compute layers are shared. Pharos Production's guide to multi-tenant SaaS architecture and database isolation compares silo, pool and bridge models and explains tenant-context enforcement in a pooled database. Use those architectural decisions when defining a tenant-qualified resource key. The guide addresses tenancy. The PostgreSQL references here establish lock behavior.

A tenant-qualified advisory key coordinates cooperating jobs. It does not authorize access to tenant rows and does not replace row-level security or a relational constraint. The tenant composite foreign-key cases cover a different invariant: preventing a stored relationship from crossing tenant boundaries.

Inspect backend ownership without inventing a count

Read-only inventory: inspect permissions and avoid exposing query text in broad logs.
SELECT l.pid, l.database, l.classid, l.objid, l.objsubid,
       l.mode, l.granted, a.application_name, a.state
FROM pg_locks AS l
LEFT JOIN pg_stat_activity AS a ON a.pid = l.pid
WHERE l.locktype = 'advisory'
ORDER BY l.pid, l.classid, l.objid;

The pg_locks reference documents these fields and the representation of advisory keys. Record pg_backend_pid() on the actual connection at acquisition and release when diagnosing ownership. Compare the resource key and mode as well as the PID. A later connection checkout may use another backend.

granted = false identifies a waiting lock request visible in this view. A failed nonblocking try does not leave a queued request behind, so its absence is not proof that no job experienced contention. Record unsuccessful try results at the application boundary if that information is operationally useful.

The executed repeated-acquisition cases counted one granted lock row even when several matching session unlocks were required. Pair database inventory with acquisition/release events. Treat monitoring access carefully: activity visibility is role-dependent, and backend SQL text may contain information that should not be copied into public logs.

Keep the cooperative lock inside its promise

An advisory lock works only when every relevant writer follows the convention. A unique constraint enforces uniqueness even for a writer that never asks for an advisory lock. A row lock protects selected database rows under transaction rules. FOR UPDATE SKIP LOCKED supports a different queue-claim pattern. Choose those mechanisms by the invariant, not by whether they all contain the word lock.

A lock is not a durable completion record. If a job sends a payment request or writes to another system, releasing database ownership does not cancel an old external action. Use an idempotent operation contract where available. When a newer worker must reject a stale writer, the separate background-job fencing-token guide explains that boundary. This lab does not establish failover-safe distributed leadership.

For blocking advisory-lock calls, define a bounded wait and a stable lock order. PostgreSQL can detect deadlocks. Detection still aborts work rather than making the ordering correct. lock_timeout limits time spent waiting for a lock, while a try call returns immediately for contention. Retry or defer at a deliberate boundary. Do not spin indefinitely or keep an idle transaction open while retrying.

The published cases do not test savepoint rollback, prepared transactions, shared-lock upgrades, fairness, network partitions or a LIMIT query that evaluates lock functions. The explicit-locking documentation warns about expression evaluation order in queries containing advisory locks. Review those cases separately before applying this small two-backend result to them. No Redis comparison or framework-specific pool configuration was executed.

Run the same fifteen cases on a disposable server

Download the complete Python runner and saved JSON results. The runner uses the standard library, Docker CLI and psql inside the named container. It holds two real backend sessions and asserts both acquisition and release outcomes.

Reproduction example: an isolated local container with no host ports or persistent volume. Trust authentication is confined to this disposable test.
docker run -d --name advisory-lock-lab --network none \
  --tmpfs /var/lib/postgresql/data:rw \
  -e POSTGRES_HOST_AUTH_METHOD=trust postgres:17.11
docker exec advisory-lock-lab pg_isready -U postgres
python3 experiment.py --container advisory-lock-lab > actual.json
# Remove only this named disposable lab when finished.
docker rm -f advisory-lock-lab

Wait for pg_isready to report readiness before running the script. Our execution used PostgreSQL 17.11 on aarch64-unknown-linux-musl from the locally present official image. The tested image digest was sha256:b0f9560a2de083e2cc7382e75f808c7381a32852a7ec49117deedb300e552b24. A mutable image tag may later resolve differently.

There are 8 repeated-acquisition cases and 7 boundary cases, for 15 total. Two executions produced byte-identical JSON because changing backend PIDs are asserted internally but omitted from the result. The chart is generated from the saved release-operation counts. No workload benchmark, request latency, real production incident or PgBouncer runtime result is claimed.

Extend the regression with the actual pool before relying on it in a SaaS worker: verify physical ownership at checkout and release, exercise the error/discard path, and make an independent backend try the same resource key after cleanup. The acceptance criterion is that the intended owner holds the lock for the required work and that the next independent owner can enter after the documented release boundary.

Sources

Documentation checked .

  1. PostgreSQL17 explicit and advisory locking
  2. PostgreSQL17 advisory-lock functions
  3. PostgreSQL17 pg_locks
  4. PgBouncer pooling feature matrix
  5. PgBouncer server reset configuration

Continue the conversation

Comments (0)

    Leave a comment

    Your name and comment stay in this page and are cleared after the spam check.

    10–2,000 characters. Keep the discussion relevant to this article.

    Spam protection verification
    Spam protection loads when you begin the form.

    JavaScript is required to use this form and its spam protection.