PostgreSQL Advisory Locks: Prove the Lock Dies When You Think It Does

The dangerous advisory-lock bug is often not failure to acquire a lock. It is failure to understand who still owns that lock after the application thinks the operation is over.

Consider a plausible sequence. Application code starts a transaction, calls pg_advisory_lock(), encounters an error, rolls the transaction back, and returns the database connection to a pool. The application may mentally classify everything inside that failed transaction as gone. PostgreSQL does not. A session-level advisory lock acquired inside the transaction survives the rollback and remains held until it is explicitly unlocked or the PostgreSQL session ends. Transaction-level advisory locks have different semantics: PostgreSQL releases them automatically when the transaction ends.

That difference turns connection pooling into part of the correctness model. Returning a connection to an application pool does not necessarily terminate its PostgreSQL session. With a transaction-pooling proxy such as PgBouncer, the distinction becomes even sharper: the server connection is released after the transaction, while PostgreSQL session state can belong to that physical backend. PgBouncer's own documentation says transaction-pooling clients must not depend on session-based features.

The acceptance question for this article is therefore deliberately narrow:

At every lifecycle boundary where the application believes an advisory lock has died, can we prove both that the old PostgreSQL backend no longer owns it and that a genuinely different backend can acquire the same key?

This is not another locking primer. It is a reproducible acceptance protocol for PostgreSQL advisory lock lifetime.

One limitation matters before any command is shown. No execution output is claimed here. The SQL below is a proposed disposable lab derived from primary PostgreSQL and PgBouncer documentation. Expected states are labeled as expectations; an acceptance run must replace those expectations with captured evidence rather than invented successful output.

Scope and acceptance target

PostgreSQL provides advisory locks whose meaning is defined by the application rather than enforced against particular table rows or objects by the database. PostgreSQL supports session-level and transaction-level acquisition. Session locks survive transaction boundaries; transaction locks are automatically released at transaction end. Advisory locks from either scope participate in the same lock space and can conflict when their keys match.

That is almost the entire conceptual background this lab needs. The point is not to survey every PostgreSQL lock mode. The point is to establish an evidence standard for lifetime and ownership.

The acceptance proof will use three independent signals:

1.       pg_locks identifies which PostgreSQL backend owns or waits for the advisory lock.

2.       pg_stat_activity, pg_backend_pid(), application_name, and backend_start identify the physical server session associated with that lock.

3.       A different backend calls a nonblocking pg_try_advisory_* function to prove whether the key is actually available. PostgreSQL documents pg_try_advisory_lock() and pg_try_advisory_xact_lock() as nonblocking alternatives that return immediately instead of waiting.

This protocol does not revisit queue design. Refonte Learning's existing Postgres-versus-Redis queue article covers PostgreSQL queue patterns involving FOR UPDATE SKIP LOCKED and LISTEN/NOTIFY. The focus here is advisory-lock lifetime, rollback behavior, pooled-backend ownership, and proof of release.

A test passes only when its evidence matches the lifecycle semantics being tested. For example, seeing a session lock survive ROLLBACK is a successful semantic test, even though it may prove that the proposed application design is unsafe.

Documented behavior versus engineering inference

A strong advisory-lock review should separate facts PostgreSQL promises from conclusions engineers draw from those facts.

Documented behavior: PostgreSQL states that session-level advisory locks last until explicitly released or until the session ends. They do not follow transaction rollback semantics. If one is acquired during a transaction that later rolls back, the session lock remains. Session-level acquisitions also stack: acquiring the same lock multiple times requires a corresponding number of releases before the lock is actually free.

Documented behavior: Transaction-level advisory locks behave differently. They are released automatically when the transaction ends, and PostgreSQL does not provide an explicit unlock function for them. Session-level and transaction-level requests for the same advisory key can block one another.

Engineering inference: If an application wants the lock lifetime to equal the database transaction lifetime, pg_advisory_xact_lock() or its try variant expresses that ownership model directly. Using pg_advisory_lock() and relying on exception handlers to imitate transaction lifetime creates an additional cleanup obligation.

Engineering inference: “Request finished,” “transaction rolled back,” “ORM scope closed,” and “connection returned to pool” are not interchangeable with “PostgreSQL session ended.” Therefore none of those application events, on its own, proves that a session-level lock has been released. The inference follows from PostgreSQL's session ownership semantics and must be tested against the connection architecture actually deployed.

Proposed experiment: exercise each relevant boundary with separate holder, contender, and observer sessions.

Actual observation: none is asserted here. Output labeled “expected” is a prediction from documented behavior. Publication-quality acceptance evidence should contain the actual rows captured from the disposable environment.

This distinction matters because a plausible SQL transcript is not evidence merely because it looks realistic. For lifecycle testing, fabricated rows would erase the very failure mode the lab is intended to discover.

Lab fixture and version matrix

Use synthetic resources only. Create a disposable database such as advisory_lab, use one synthetic bigint advisory key such as 4242424242, and open at least three independent PostgreSQL sessions:

•        A, the holder, acquires the lock.

•        B, the contender, attempts to acquire the same key.

•        O, the observer, queries server-side state without owning the key.

The key itself does not identify a PostgreSQL object; advisory-lock keys are application-defined. PostgreSQL accepts either one 64-bit integer or a pair of 32-bit integers, with the two key spaces kept distinct. This article uses the single-bigint form to make the pg_locks evidence easier to reconstruct.

Keep the lab disposable for another reason: testing backend termination should never require killing a production application session. Refonte Learning's broader article on the Databricks–Neon acquisition and serverless Postgres discusses ephemeral database environments in a wider platform context; here, disposability is simply a safety and reproducibility requirement.

As of September 22, 2026, PostgreSQL's official release pages list PostgreSQL 18.6 among the August 13, 2026 supported releases, while PostgreSQL 19 is still represented in the development stream rather than as the current stable major version. PostgreSQL 18.6 is therefore the stable target for this protocol.

For the pooling extension, pin PgBouncer 1.25.2, released May 8, 2026, rather than referring vaguely to “current PgBouncer.”

Version and environment matrix

Record actual binary and environment identifiers during execution. A future run after a PostgreSQL major-version, pooler-version, or pool-mode change is a new acceptance event, not automatically covered by evidence captured earlier.

The same evidence discipline appears in Refonte Learning's broader PostgreSQL 19 workload-replay readiness guide, which emphasizes recording server, client, and pooler context. This article narrows that discipline to advisory-lock ownership rather than duplicating an upgrade-readiness exercise.

Do not silently substitute a PostgreSQL 19 prerelease for the 18.6 fixture. Testing a prerelease may be useful separately, but it changes the version claim and should produce a separate evidence bundle.

Baseline state and evidence model

Before acquiring anything, establish identities. In each direct session, set a distinct application_name and record the backend PID.

-- Session A
SET application_name = 'advlock-A';

SELECT
    pg_backend_pid() AS backend_pid,
    current_database() AS database_name,
    current_user AS role_name,
    current_setting('application_name') AS application_name,
    version() AS server_version;

Repeat with advlock-B and advlock-O. PostgreSQL documents pg_backend_pid() as the server process identifier attached to the current session. pg_stat_activity exposes that PID together with fields including application_name, backend_start, xact_start, state, and wait-event information.

Then make the observer query the advisory-lock state:

SELECT
    l.pid,
    a.application_name,
    a.backend_start,
    a.xact_start,
    a.state,
    d.datname,
    l.mode,
    l.granted,
    l.classid,
    l.objid,
    l.objsubid,
    CASE
        WHEN l.objsubid = 1
        THEN (l.classid::bigint << 32) | l.objid::bigint
    END AS bigint_key,
    a.wait_event_type,
    a.wait_event
FROM pg_locks AS l
JOIN pg_stat_activity AS a
  ON a.pid = l.pid
LEFT JOIN pg_database AS d
  ON d.oid = l.database
WHERE l.locktype = 'advisory'
ORDER BY l.pid, l.granted DESC;

pg_locks contains one row for relevant held or awaited locks, with granted indicating whether the lock is already held. For bigint advisory keys, PostgreSQL documents the mapping into classid, objid, and objsubid; the expression above reconstructs the original bigint key when objsubid = 1. The pid column can be joined to pg_stat_activity.

The baseline for key 4242424242 should show no lock row. That expected absence is not enough by itself, however. The baseline should also prove that A, B, and O have different backend PIDs. Otherwise the later “contender” could accidentally be the same owner, invalidating the test.

Pairing PID with backend_start is stronger evidence than recording PID alone: it ties the observation to a particular server-session lifetime rather than merely to a numeric process identifier. That is an engineering evidence practice based on the session metadata PostgreSQL exposes.

Normal-path validation

Start with transaction-level acquisition because its desired lifetime is the simplest to prove.

In A:

BEGIN;

SELECT pg_try_advisory_xact_lock(4242424242) AS acquired;

Expected: acquired = true.

While that transaction remains open, O should see an advisory-lock row belonging to A. B, which must be a different backend, should fail immediately to acquire the conflicting key:

SELECT pg_try_advisory_lock(4242424242) AS acquired_by_b;

Expected while A holds the transaction lock: false.

Now make A end the transaction:

ROLLBACK;

O should no longer see A holding that key. B can then test availability:

SELECT pg_try_advisory_lock(4242424242) AS acquired_by_b;

Expected after A's rollback: true.

Because B has just acquired a session-level lock while proving availability, clean it up:

SELECT pg_advisory_unlock(4242424242) AS released_by_b;

PostgreSQL documents transaction-level advisory locks as automatically released at transaction end and session-level advisory locks as explicitly unlockable. pg_advisory_unlock() returns a boolean indicating whether a matching session lock was successfully released.

Repeat the same transaction-level sequence with COMMIT instead of ROLLBACK:

-- A
BEGIN;
SELECT pg_try_advisory_xact_lock(4242424242);
COMMIT;

After COMMIT, O should again find no owner for the key and B should be able to acquire it. That validates both normal transaction endings instead of assuming that commit and rollback cleanup were equivalent merely because both end a transaction.

Next validate the normal session-lock path:

-- A
SELECT pg_try_advisory_lock(4242424242) AS acquired;

O should identify A as the holder; B should get false. Then:

-- A
SELECT pg_advisory_unlock(4242424242) AS released;

The expected result is true. O should see the lock row disappear, and a fresh probe by B should return true. PostgreSQL documents both the explicit session unlock operation and the nonblocking try operation.

Do not use B's successful probe as the only artifact. Since probing with pg_try_advisory_lock() itself acquires a session lock when it succeeds, retain O's before/after rows and immediately unlock B after every successful availability check.

Boundary-condition experiment

The critical boundary is where developers most easily transfer transaction intuition to a session-scoped API.

Have A begin a transaction and intentionally use the session-level function:

BEGIN;

SELECT pg_try_advisory_lock(4242424242) AS acquired;

O should record that A owns the advisory lock. B should record that the same key is unavailable.

Now roll back A:

ROLLBACK;

Do not explicitly unlock first.

PostgreSQL states that a session-level advisory lock acquired during a transaction remains held even if that transaction is rolled back. Consequently, the documented expectation is that O still finds the lock owned by A and B still receives false.

This is the boundary that application-level exception handling frequently obscures. Database writes may have rolled back successfully while the lock remains perfectly healthy and deliberately held by the same session.

Deliberately failing boundary test

Turn that semantic fact into a test that should fail an incorrect application assumption.

Suppose the proposed invariant is:

“After our unit of work rolls back, no advisory lock acquired by that unit remains.”

Run:

A: BEGIN
A: pg_try_advisory_lock(4242424242)  -> expected true
O: capture pg_locks owner            -> expected A
B: pg_try_advisory_lock(4242424242)  -> expected false

A: ROLLBACK

O: inspect same key                  -> expected STILL A
B: pg_try_advisory_lock(4242424242)  -> expected STILL false

The test must fail the application invariant. That failure is valuable: the application selected a session-scoped API but expected a transaction-scoped lifetime.

Now explicitly clean up A:

SELECT pg_advisory_unlock(4242424242) AS released;

Then O should see no matching owner and B should be able to acquire the key.

A later green rerun does not prove that the failed attempt cleaned itself up. The original backend may have disconnected between runs, the pool may have recycled it, the rerun may have landed on another physical backend, or some unrelated reset may have cleared the stale state. PostgreSQL guarantees cleanup at session end, so eventual disappearance cannot retroactively prove that ROLLBACK released the original session lock.

The failed attempt therefore needs its own evidence: original PID, backend_start, rollback timestamp, post-rollback pg_locks row, and contender result. Recovery evidence must be attached to the failure, not inferred from the success of the next run.

Recovery and rollback behavior

Recovery depends on the lock scope.

For a transaction-level advisory lock, ordinary COMMIT or ROLLBACK is the release boundary. There is no separate transaction-level advisory-unlock function. For a session-level lock, the intended recovery mechanisms are explicit unlock, unlocking all session advisory locks, or ending the PostgreSQL session. PostgreSQL documents pg_advisory_unlock_all() as releasing all session-level advisory locks held by the current session, and says that this cleanup also occurs when the session ends, including an ungraceful disconnect.

DISCARD ALL can also matter in connection-reset designs. PostgreSQL documents that DISCARD ALL includes SELECT pg_advisory_unlock_all() among the session-state cleanup actions it performs, and DISCARD ALL cannot itself run inside a transaction block.

There is also an important distinction between cancelling work and terminating ownership. pg_cancel_backend(pid) cancels a backend's current query; pg_terminate_backend(pid, timeout) terminates the backend session. Since a session-level advisory lock lives until explicit release or session termination, cancelling a query should not be accepted as proof that the session lock has died. That conclusion is an engineering inference from the two documented behaviors.

A disposable termination experiment can prove the session-end path:

-- O records A's PID while A holds a session advisory lock.

SELECT pg_terminate_backend(<A_PID>, 5000);

Then verify two postconditions rather than trusting the termination call alone:

SELECT pid, backend_start
FROM pg_stat_activity
WHERE pid = <A_PID>;

The original session should be absent, and O should find no advisory-lock row belonging to it. Finally B should acquire the same key successfully. PostgreSQL exposes pg_terminate_backend() specifically for terminating a backend identified through server activity information. Appropriate permissions are required, which is another reason to keep this experiment isolated.

If the application requires cleanup to happen at rollback, relying on session termination later is not an equivalent design. It may remove the stale lock eventually, but it does not satisfy the original lifetime contract.

Concurrency, lifecycle, or version interaction

Pooling is where “session” needs an exact definition.

For PostgreSQL advisory locks, the relevant session is the PostgreSQL server session/backend. An application's logical request, ORM context, web request, worker task, or checked-out pool handle can have a shorter lifetime than that backend.

PgBouncer makes the distinction explicit. In session pooling, a server connection remains assigned until the client disconnects. In transaction pooling, the server connection is released after each transaction. PgBouncer also states that server_reset_query (whose default is DISCARD ALL) is not normally used when a server connection is released in transaction mode, because clients using transaction pooling are expected not to depend on session-based features.

That makes session advisory locks a poor fit for transaction pooling unless the design can establish some separate, rigorously tested ownership mechanism.

The broad database administration in 2026 article discusses connection pooling as an operational concern. Here the much narrower question is whether logical-client handoff preserves PostgreSQL server-session state that the next borrower did not knowingly create.

A particularly revealing disposable PgBouncer experiment is to set pool_mode = transaction and constrain the isolated pool so two logical clients can be shown to reuse the same captured PostgreSQL backend. Do not merely assume the handoff happened; pg_backend_pid() must prove it.

Logical client C1:

BEGIN;

SELECT pg_backend_pid() AS server_pid;
SELECT pg_try_advisory_lock(4242424242) AS acquired;

ROLLBACK;

Because this is a session advisory lock, PostgreSQL semantics predict that rollback does not release it. Transaction pooling can then make that server connection available to another logical client without ending the PostgreSQL backend.

Logical client C2 should begin a transaction and capture its server PID:

BEGIN;

SELECT pg_backend_pid() AS server_pid;
SELECT pg_try_advisory_lock(4242424242) AS acquired;

COMMIT;

The most deceptive outcome is possible if C2 receives the same backend C1 used. PostgreSQL permits a session that already owns an advisory lock to acquire that lock again; session-level acquisitions stack. Therefore C2's pg_try_advisory_lock() may return true even though the backend was already carrying C1's stale session state. It is not proof of clean acquisition. It can be a reentrant acquisition by the same physical PostgreSQL session.

One pg_advisory_unlock() would then only balance one acquisition. A residual acquisition can remain. This is precisely why a pool test needs a separate observer and a separate contender, not just application return values.

Advisory locks also should not be imagined as replicated ownership records. PostgreSQL's hot-standby documentation notes that advisory locks are not WAL-logged, so an advisory lock acquired on one server does not become an equivalent lock on a standby. A topology or failover test therefore needs to reason about server-session identity, not merely reconnect and assume the old lock migrated.

Observability and evidence retention

pg_locks is the central server-side artifact. PostgreSQL documents locktype = 'advisory', holder/waiter state through granted, the backend PID, and the fields needed to identify the advisory key. It also recommends pg_blocking_pids() when the objective is to identify which backend is blocking another, rather than reproducing all lock-manager conflict rules in a hand-written self-join.

A blocked backend can also expose wait_event_type = 'Lock' with an advisory-lock wait event, giving another signal when a blocking version of the advisory function is intentionally being tested. For ordinary acceptance probes, however, the pg_try_* variants are safer because they return rather than deliberately leaving the test process waiting.

The evidence bundle should answer four questions without reconstructing the experiment from memory:

Who owned the key? What exact PostgreSQL session was that? Which lifecycle boundary occurred? Could an independent backend acquire the same key immediately afterward?

Evidence that must survive a rerun

Keep the evidence from a failed run even after the environment is cleaned. A clean subsequent state can prove the environment is clean now; it cannot prove why the previous lock disappeared or whether it persisted past the boundary it was supposed to respect.

A useful retained record looks like this:

test_case_id
captured_at
postgres_version
pooler_version
pool_mode
database
advisory_key

holder_application_name
holder_backend_pid
holder_backend_start

contender_backend_pid
contender_backend_start

boundary_action
boundary_completed_at

pg_locks_before
pg_locks_after
contender_try_before
contender_try_after

cleanup_action
cleanup_verified_at

backend_start, xact_start, application name, state, and PID all come from PostgreSQL's server activity facilities; lock ownership comes from pg_locks. That allows the evidence to relate a lock row to a specific backend lifecycle rather than only to an application request identifier.

Do not collapse “expected output” and “captured output” in published evidence. The former belongs in the test specification. Only the latter belongs in an actual-observation column.

Negative tests and false confidence

Advisory-lock testing needs negative tests because several superficially green results are compatible with a broken ownership model.

The first is same-session probing. PostgreSQL permits an existing lock owner to acquire the same advisory lock again. A true result from pg_try_advisory_lock() therefore does not establish that the key was free unless the probe came from a different PostgreSQL backend. With session locks, that reentrant call also increases the acquisition count.

The second is single-unlock confidence. pg_advisory_unlock() returning true establishes that one held session-lock acquisition was released. If the same backend acquired the lock repeatedly, corresponding releases are required before another session can obtain it.

The third is application-boundary confidence. An exception handler reporting “rolled back” says something about the transaction, not about a session-level advisory lock.

The fourth is rerun confidence. A rerun that happens to use another physical backend proves nothing about the original backend's state at the moment of failure.

One more subtle test deserves inclusion: a lock probe should use the same database where the lock under examination was created. PostgreSQL's advisory-lock entries carry a database identifier and advisory locks are database-local, so an otherwise identical key tested in the wrong database is not evidence about the original lock.

Operational rollout and rollback criteria

A production rollout should begin only after the synthetic environment can demonstrate every lifecycle boundary used by the application.

Inventory every advisory-lock call site and classify it by function name. A code path using pg_advisory_lock() or pg_try_advisory_lock() has session semantics. A path using pg_advisory_xact_lock() or pg_try_advisory_xact_lock() has transaction semantics. The distinction should be explicit in review rather than inferred from where the SQL happens to appear inside application code.

Next map each call to the real connection architecture: direct connection, application pool, PgBouncer session pool, PgBouncer transaction pool, or another proxy. For PgBouncer transaction pooling, its own documentation's restriction on session-based features should be treated as a design warning requiring resolution before rollout, not as an implementation footnote.

The operational philosophy aligns with the broader emphasis on tested procedures in Refonte Learning's cloud database administration in 2026. The lock-specific requirement here is stronger: retain server-side ownership evidence for the exact failure boundary being certified.

The operational rollback trigger should be semantic, not merely performance-based. If a session lock survives a boundary the design says should release it, or if a different logical client inherits a backend carrying stale ownership, stop the rollout and correct the lifetime model.

Acceptance matrix

The final acceptance matrix separates PostgreSQL semantic acceptance from application design acceptance. Those are not always the same verdict.

For example, a session lock remaining after ROLLBACK means PostgreSQL behaved correctly. An application that required rollback cleanup has nevertheless failed its design acceptance.

The authoritative distinction is straightforward: transaction-level locks end with the transaction; session-level locks remain until sufficiently unlocked or until the session ends. PostgreSQL exposes those locks through pg_locks, while independent try-lock functions provide a practical availability probe.

Pass criteria

A test passes only if the owner identity, boundary observation, and independent contender result agree.

A contender is “fresh” only when its PostgreSQL backend identity differs from the lock owner. Under a pooler, application-client identity is not a substitute for this condition.

Hold, refactor, or quarantine conditions

Application deployment should stop even when PostgreSQL itself is behaving exactly as documented if the code's ownership assumptions disagree with those semantics.

The strongest pass condition is therefore not “the command returned successfully.” It is a state transition: known owner before the boundary, documented lifecycle action, correct owner state after it, and a different backend confirming availability or continued exclusion.

Common implementation mistakes

The most common implementation error is simply choosing the function whose name looks convenient rather than the scope whose lifetime matches the invariant.

pg_advisory_lock() is not the transaction version of an advisory lock merely because it is invoked between BEGIN and COMMIT. Scope comes from the function selected. If automatic transaction-end cleanup is required, that requirement should be represented by the transaction-level API.

Another mistake is putting an explicit session unlock in a happy-path finally block and considering the problem solved. Explicit cleanup is useful, but acceptance must still test abnormal paths, stacked acquisition, physical connection reuse, and actual session termination. Otherwise the review validates source-code intent rather than server state.

Blocking acquisition can also make the lab harder to diagnose. PostgreSQL's blocking advisory-lock functions wait until the lock becomes available, whereas the pg_try_* variants immediately return success or failure. For contender probes, the nonblocking variants produce clearer bounded evidence.

Finally, avoid unsafe SQL shapes when advisory locking rows selected from queries. PostgreSQL's documentation specifically cautions that expressions acquiring advisory locks combined with LIMIT can be evaluated in an order that acquires locks the developer did not intend. That topic is not this article's primary lab, but it is another reason to keep lifetime testing on a single synthetic key rather than mixing it with application query-planner behavior.

The implementation rule is simple but demanding: the API's PostgreSQL ownership lifetime must match the application's ownership lifetime, and the match must be demonstrated from outside the owning session.

Final decision and Refonte Learning CTA

For a lock intended to protect exactly one database transaction, transaction-level advisory locking is the easier ownership model to prove: the lock ends with COMMIT or ROLLBACK, and an independent backend can verify that release. PostgreSQL documents that lifecycle directly.

Session-level advisory locks remain valid tools when the intended critical section genuinely spans transaction boundaries. But they create an explicit cleanup obligation and couple correctness to the lifetime of the PostgreSQL server session. Pooling makes that distinction operationally significant rather than theoretical.

The acceptance standard should therefore reject three shortcuts: rollback is not proof of session-lock release, returning a connection is not proof of server-session termination, and a later green run is not proof that the failed run cleaned itself up.

Decision record

Decision: do not ship a session-level advisory-lock design whose safety argument is “we roll back on error.” Ship only after a disposable run proves the exact physical-session and transaction boundaries on which release depends.

Explore Refonte Learning’s Database Administrator Program. Use this lab’s evidence gates independently when judging production readiness.