A PostgreSQL Savepoint Rolled Back. Did the Advisory Lock Release?

A successful ROLLBACK TO SAVEPOINT is not proof that an advisory lock acquired after that savepoint disappeared. That inference is specifically dangerous for session-level advisory locks: PostgreSQL documents that session-scoped advisory locks do not obey transaction rollback semantics. Once acquired, they remain until explicitly unlocked or until the owning session ends. Transaction-level advisory locks have a different lifetime: PostgreSQL describes them as behaving more like ordinary transaction locks and releasing automatically with transaction scope. PostgreSQL also documents that locks acquired after a savepoint are released when that savepoint is rolled back.

That creates a narrow release-acceptance problem. The operator-visible green signal (ROLLBACK TO SAVEPOINT completing) is insufficient. The state that matters must be proved independently: does pg_locks still show the target advisory lock, and can a separate backend acquire the same conflicting key? PostgreSQL explicitly exposes advisory locks through pg_locks, while the nonblocking pg_try_advisory_lock* functions provide a second-session contention probe.

This playbook treats those as separate evidence channels. It pins PostgreSQL 18.6, builds a disposable two-session fixture, defines an expected-case manifest before execution, preserves a negative control, and finishes with an ACCEPT / REPAIR / RERUN / HOLD decision. The cases in this article are EXPECTED / NOT EXECUTED; no test output below is presented as an observation.

Define the operational acceptance contract

The acceptance contract is not “the savepoint rollback succeeded.” It is: after ROLLBACK TO SAVEPOINT, the observed lifetime of each advisory lock must match its declared scope and acquisition boundary, and two independent state probes must agree.

PostgreSQL distinguishes two advisory-lock scopes. A session-level lock obtained with pg_advisory_lock() remains until a matching unlock or session termination; rollback does not undo its acquisition. A transaction-level lock obtained with pg_advisory_xact_lock() has no manual unlock operation and is automatically released according to transaction lifetime. Session- and transaction-level requests for the same identifier conflict with each other.

For release acceptance, require five assertions:

1. The exact server version is captured before testing.

2. Session A records whether the target lock was acquired before or after the savepoint.

3. An observer queries pg_locks after the rollback boundary.

4. Session B independently attempts the same conflicting key with pg_try_advisory_lock().

5. A free-key negative control proves the observer can acquire an uncontended lock.

pg_locks is appropriate state evidence because PostgreSQL documents one row per active lockable object/requesting process combination and identifies advisory locks with locktype = 'advisory'. For a single bigint advisory key, the key can be reconstructed from classid and objid using the expression documented by PostgreSQL.

A release candidate must therefore satisfy a reconciliation such as:

declared scope
+ acquisition position relative to SAVEPOINT
+ pg_locks state
+ independent conflicting acquisition probe
+ cleanup evidence
= accepted lifetime behavior

A mismatch is not waived because the SQL command itself returned normally.

The same evidence-first release discipline appears in Kubernetes preStop termination-budget validation, but that page addresses Kubernetes termination-budget accounting rather than database lock lifetime; its shared value is methodological, not topical.

Pin the documented baseline

PostgreSQL 18 is the current stable documentation branch as of this research, and PostgreSQL’s versioning page lists 18.6 as the current minor release for major version 18. PostgreSQL recommends running the current minor release of a supported major version.

Four documentation statements form the baseline.

First, SAVEPOINT marks transaction state so commands after that point can be rolled back, restoring the transaction state to the savepoint boundary.

Second, ROLLBACK TO SAVEPOINT rolls back commands after the named savepoint and starts a new subtransaction at the same level; the named savepoint remains valid.

Third, PostgreSQL’s explicit-locking documentation says a lock acquired after a savepoint is normally released when that savepoint is rolled back. It separately states that row locks follow the same savepoint-release rule.

Fourth, advisory-lock documentation creates the crucial exception and distinction: session-level advisory locks ignore transaction rollback semantics, while transaction-level advisory locks behave more like regular lock requests and release automatically with transaction lifetime.

The expected interpretation used by this playbook is therefore:

The first three rows follow directly from the documented lifetime boundaries. The fourth is a documented-behavior inference: transaction-level advisory locks are defined as transaction-semantic locks, while PostgreSQL says locks acquired after a savepoint are released on rollback to that savepoint. The fixture exists specifically to validate that inference against the pinned runtime rather than silently promoting it from documentation synthesis to observed fact.

Pin exact runtime versions

Use an exact server image, not postgres:18 or latest. Docker’s official PostgreSQL image catalog currently exposes postgres:18.6-bookworm; the maintained Docker-library metadata also lists that exact tag.

# compose.yaml
services:
  pg:
    image: postgres:18.6-bookworm
    environment:
      POSTGRES_USER: locklab
      POSTGRES_PASSWORD: locklab
      POSTGRES_DB: locklab
    ports:
      - "55432:5432"
    volumes:
      - ./evidence:/evidence
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U locklab -d locklab"]
      interval: 1s
      timeout: 2s
      retries: 30

Capture the runtime before any case:

docker compose up -d

docker compose exec -T pg \
  psql -U locklab -d locklab -X -Atc \
  "SELECT version();" \
  | tee evidence/server-version.txt

Expected boundary: the returned server version identifies PostgreSQL 18.6.

Rejection criterion: a different major/minor runtime, an unpinned image, or missing version artifact means HOLD. Do not silently reuse expected results from another version.

Build an isolated reproducible fixture

Use synthetic lock identifiers that have no production meaning:

export K_SESSION_AFTER=771001
export K_XACT_AFTER=771002
export K_SESSION_BEFORE=771003
export K_XACT_BEFORE=771004
export K_STACKED_SESSION=771005
export K_NEGATIVE=771006
export K_XACT_COMMIT=771007
export K_SESSION_END=771008

rm -rf evidence
mkdir -p evidence

No tables, extensions, application traffic, or production credentials are required. Advisory identifiers are application-defined, and PostgreSQL supports either a single 64-bit key or two 32-bit keys; this fixture deliberately uses only the single-bigint form to keep lock identity unambiguous.

The observer query should reconstruct the key instead of assuming that objid alone is sufficient:

SELECT
    pid,
    mode,
    granted,
    database,
    objsubid,
    ((classid::bigint << 32) | objid::bigint) AS advisory_key
FROM pg_locks
WHERE locktype = 'advisory'
  AND objsubid = 1
ORDER BY advisory_key, pid;

PostgreSQL documents objsubid = 1 for the single-bigint advisory-key representation and gives the same reconstruction expression. Advisory locks are database-local, so evidence should preserve the database identity as well as the numeric key.

The fixture uses psql barrier files under /evidence so Session B runs only after Session A has crossed the rollback boundary. That removes arbitrary sleep durations from the correctness condition.

A representative Session A script is:

\set ON_ERROR_STOP on

BEGIN;
SAVEPOINT s;

SELECT pg_advisory_lock(771001);

\! touch /evidence/C1-acquired
ROLLBACK TO SAVEPOINT s;
\! touch /evidence/C1-rolled-back

\! while [ ! -f /evidence/C1-probed ]; do sleep 0.05; done

SELECT pg_advisory_unlock(771001);
COMMIT;

The methodology is adjacent to the artifact-first approach in SQLite WAL backup validation, but this PostgreSQL fixture is intentionally narrower: it reconciles lock-manager state rather than recovered rows.

Write the independent expected-case manifest

Write expectations before observing the runtime. The manifest must not be generated from test output.

runtime:
  postgres: "18.6"
  image: "postgres:18.6-bookworm"

execution_status: "NOT EXECUTED"

cases:

  C1:
    name: session_after_savepoint_rollback
    key: 771001
    acquire: session
    position: after_savepoint
    expected_pg_locks_after_rollback: present
    expected_peer_try_after_rollback: false
    status: EXPECTED_NOT_EXECUTED

  C2:
    name: xact_after_savepoint_rollback
    key: 771002
    acquire: transaction
    position: after_savepoint
    expected_pg_locks_after_rollback: absent
    expected_peer_try_after_rollback: true
    status: EXPECTED_NOT_EXECUTED

  C3:
    name: session_before_savepoint_rollback
    key: 771003
    acquire: session
    position: before_savepoint
    expected_pg_locks_after_rollback: present
    expected_peer_try_after_rollback: false
    status: EXPECTED_NOT_EXECUTED

  C4:
    name: xact_before_savepoint_rollback
    key: 771004
    acquire: transaction
    position: before_savepoint
    expected_pg_locks_after_rollback: present
    expected_peer_try_after_rollback: false
    expected_release: outer_transaction_end
    status: EXPECTED_NOT_EXECUTED

  C5:
    name: stacked_session_lock
    key: 771005
    acquire_count: 2
    expected_after_one_unlock_peer_try: false
    expected_after_two_unlocks_peer_try: true
    status: EXPECTED_NOT_EXECUTED

  C6:
    name: free_key_negative_control
    key: 771006
    expected_peer_try: true
    status: EXPECTED_NOT_EXECUTED

  C7:
    name: xact_commit_boundary
    key: 771007
    expected_peer_try_before_commit: false
    expected_peer_try_after_commit: true
    status: EXPECTED_NOT_EXECUTED

  C8:
    name: session_disconnect_boundary
    key: 771008
    expected_peer_try_while_session_alive: false
    expected_peer_try_after_session_end: true
    status: EXPECTED_NOT_EXECUTED

Session-lock stacking is explicitly documented: repeated successful session-level acquisitions require corresponding unlocks before release. Transaction-level locks have no manual unlock function.

Preserve a negative control

C6 must run in the same environment and through the same probe path as the contested cases:

docker compose exec -T pg \
  psql -U locklab -d locklab -X -Atc \
  "SELECT pg_try_advisory_lock(771006);" \
  | tee evidence/C6-peer-try.txt

A successful pg_try_advisory_lock() is defined to return true; if the conflicting lock is unavailable, it returns false immediately rather than waiting.

Expected: t.

Rejection criterion: if the free-key control returns f, errors, connects to another database, or produces no evidence, do not interpret any contested f result as proof of lock retention. Mark the run HOLD until the probe path is repaired.

Reproduce the misleading success

C1 isolates the false-positive signal.

Session A:

\set ON_ERROR_STOP on
\echo C1_BEGIN

BEGIN;
SAVEPOINT s;
SELECT pg_advisory_lock(771001);
\! touch /evidence/C1-acquired

ROLLBACK TO SAVEPOINT s;
\echo ROLLBACK_TO_SUCCEEDED
\! touch /evidence/C1-rolled-back

SELECT
    pid, mode, granted,
    ((classid::bigint << 32) | objid::bigint) AS advisory_key
FROM pg_locks
WHERE locktype = 'advisory'
  AND objsubid = 1
  AND ((classid::bigint << 32) | objid::bigint) = 771001;

\! while [ ! -f /evidence/C1-probed ]; do sleep 0.05; done

SELECT pg_advisory_unlock(771001);
COMMIT;
\echo C1_END

Run it:

docker compose exec -T pg \
  psql -U locklab -d locklab -X \
  < c1-session-a.sql \
  > evidence/C1-session-a.log 2>&1 &

The misleading event is ROLLBACK_TO_SUCCEEDED. PostgreSQL absolutely permits the savepoint rollback itself to succeed while a session-level advisory lock survives it, because session advisory locks explicitly do not honor transaction rollback semantics.

The acceptance test must therefore continue.

After the host sees evidence/C1-rolled-back, Session B executes:

docker compose exec -T pg \
  psql -U locklab -d locklab -X -Atc \
  "SELECT pg_try_advisory_lock(771001);" \
  | tee evidence/C1-peer-try.txt

touch evidence/C1-probed
wait

Expected / NOT EXECUTED: C1’s peer result is f, while Session A’s pg_locks query still contains key 771001.

That pair provides the evidence relevant to lifetime; the successful rollback alone does not.

Run the controlled comparison

C2 changes only the scope function.

\set ON_ERROR_STOP on
\echo C2_BEGIN

BEGIN;
SAVEPOINT s;

SELECT pg_advisory_xact_lock(771002);
\! touch /evidence/C2-acquired

ROLLBACK TO SAVEPOINT s;
\! touch /evidence/C2-rolled-back

SELECT
    pid, mode, granted,
    ((classid::bigint << 32) | objid::bigint) AS advisory_key
FROM pg_locks
WHERE locktype = 'advisory'
  AND objsubid = 1
  AND ((classid::bigint << 32) | objid::bigint) = 771002;

\! while [ ! -f /evidence/C2-probed ]; do sleep 0.05; done
COMMIT;

Peer:

docker compose exec -T pg \
  psql -U locklab -d locklab -X -Atc \
  "SELECT pg_try_advisory_lock(771002);" \
  | tee evidence/C2-peer-try.txt

touch evidence/C2-probed

The documented inputs differ in one material dimension. pg_advisory_lock() creates session scope; pg_advisory_xact_lock() creates transaction scope. PostgreSQL says the latter behaves like regular transaction locks, and it says locks acquired after a savepoint are released when that savepoint is rolled back.

Expected / NOT EXECUTED: C2 shows no 771002 advisory row after rollback and Session B returns t.

That expectation is deliberately not reported as an observed PostgreSQL 18.6 result here. Executing the pinned fixture is what turns it into observation.

The procedural style has some kinship with Java periodic-task exception validation, which independently verifies a periodic task’s terminal state instead of trusting executor liveness. The Java page concerns scheduled futures, not lock lifetime.

Assert the failure boundary

The strongest comparison is not C1 versus C2 alone. Add C3 and C4 so acquisition position is independently varied.

C3:

BEGIN;
SELECT pg_advisory_lock(771003);
SAVEPOINT s;
ROLLBACK TO SAVEPOINT s;
-- Expected: still held.

C4:

BEGIN;
SELECT pg_advisory_xact_lock(771004);
SAVEPOINT s;
ROLLBACK TO SAVEPOINT s;
-- Expected: still held because acquisition predates the savepoint.

The boundary hypothesis is therefore precise:

session scope:
    rollback boundary does not release the lock

transaction scope:
    acquired before savepoint -> survives ROLLBACK TO
    acquired after savepoint  -> expected to unwind with savepoint

Rejection criterion: if C2 and C4 produce the same post-rollback availability despite the acquisition moving across the savepoint boundary, or if pg_locks and the peer probe disagree persistently, do not generalize from one result. HOLD and preserve raw evidence.

Test failure and boundary cases

The primary four cases are insufficient for a release gate because they can leave probe or cleanup errors hidden.

C5 tests session-lock recursion:

SELECT pg_advisory_lock(771005);
SELECT pg_advisory_lock(771005);

After one:

SELECT pg_advisory_unlock(771005);

the peer should still be blocked; after the second unlock it should succeed. PostgreSQL expressly documents stacking of repeated session-level advisory-lock requests.

C7 proves the normal transaction endpoint independently of savepoints:

BEGIN;
SELECT pg_advisory_xact_lock(771007);
-- peer probe here: EXPECTED false
COMMIT;
-- peer probe here: EXPECTED true

Transaction-scoped advisory locks are documented as automatically released at transaction end.

C8 proves session cleanup:

SELECT pg_advisory_lock(771008);
-- peer while this connection lives: EXPECTED false
\q

Then a new Session B probe should return true because session advisory locks are released when the owning session ends. PostgreSQL also documents pg_advisory_unlock_all() and notes that session termination implicitly releases session advisory locks.

Shared locks, two-integer keyspaces, deadlock testing, prepared transactions, and lock-capacity exhaustion are outside this playbook’s scope. They are legitimate PostgreSQL subjects, but they are separate from the release question tested here.

Capture raw evidence

An evidence bundle should be write-once for each run:

evidence/
  server-version.txt
  image-ref.txt
  manifest.yaml
  C1-session-a.log
  C1-peer-try.txt
  C1-pg-locks.tsv
  C2-session-a.log
  C2-peer-try.txt
  C2-pg-locks.tsv
  ...
  C6-peer-try.txt
  sha256.txt
  decision.md

Capture the image declaration:

grep 'image:' compose.yaml \
  | tee evidence/image-ref.txt

Capture lock state independently from Session A where practical:

docker compose exec -T pg \
  psql -U locklab -d locklab -X -F $'\t' -Atc "
    SELECT
      pid,
      mode,
      granted,
      database,
      objsubid,
      ((classid::bigint << 32) | objid::bigint) AS advisory_key
    FROM pg_locks
    WHERE locktype = 'advisory'
      AND objsubid = 1
      AND ((classid::bigint << 32) | objid::bigint)
          BETWEEN 771001 AND 771008
    ORDER BY advisory_key, pid;
  " > evidence/locks-after-boundary.tsv

Finally:

(
  cd evidence
  sha256sum * > sha256.txt
)

pg_locks gives a cluster-wide view and includes the backend pid, lock mode, whether the request is granted, database identity, and advisory-key representation. PostgreSQL warns that lock state can change while the view is gathered, so evidence should be captured at a controlled barrier rather than from an unconstrained production timeline.

Detect missing evidence

Define evidence completeness before looking at results.

For each C1–C4 case, require:

runtime version
case ID
lock key
scope function
savepoint ordering
ROLLBACK TO completion
post-boundary pg_locks capture
peer acquisition result
cleanup result

For C6, require the peer acquisition result and identical connection parameters.

Boundary: an absent pg_locks row by itself does not earn acceptance if the peer probe was never run; likewise, a peer t without the expected case identity cannot safely be attributed to a particular savepoint boundary.

Expected result: every required artifact exists, is nonempty where applicable, and maps to exactly one manifest case.

Rejection criterion: missing or ambiguously named evidence yields RERUN when the fixture is otherwise trustworthy, or HOLD when the missing material prevents reconstruction of what actually executed.

Reconcile the actual state

After execution, build the ledger from raw artifacts rather than from memory:

Reconciliation must use both identity and effect:

lock-manager evidence agrees with contention evidence
AND
case key agrees with manifest key
AND
runtime agrees with pinned runtime
AND
cleanup leaves no fixture keys held

If the ledger later contains a real run, change only evidence-supported cells to OBSERVED. Never bulk-relabel the expected manifest as observed.

This is the same anti-false-positive principle used in pandas GroupBy missing-key reconciliation: its domain is missing-key aggregation, but its useful pattern is reconciling state identity rather than trusting an attractive aggregate signal.

Separate documented from observed behavior

The evidence ledger should carry an explicit epistemic class.

The distinction matters because the PostgreSQL documents make the pieces strong enough to specify an expected outcome, but an operational acceptance record should still distinguish specification from runtime observation.

The same rule applies to pg_locks. PostgreSQL calls it the system view for outstanding locks and documents advisory-key representation, but a documentation statement does not prove that your particular backend, database, key, or savepoint ordering matched the fixture.

A useful evidence notation is:

D = documented
E = expected from documented contract
O = observed in captured execution
I = inference from multiple observed facts

Release acceptance should normally require O for the tested runtime, while engineering explanation may rely on D and clearly labeled E.

Measure the repair boundary

When a mismatch occurs, repair only the smallest layer implicated by the evidence.

If C6 fails, the probe path is broken: repair connection parameters, database identity, key collision, or fixture cleanup before changing application lock semantics.

If C1 unexpectedly becomes acquirable after rollback, first confirm that Session A did not end, call pg_advisory_unlock*, reconnect, or use a different key. That would contradict the documented session-level lifetime and therefore deserves a harness investigation before a code “fix.”

If C2 remains blocked while C1 and C6 behave correctly, capture the exact version, Session A PID, lock row, and raw transcript. Do not paper over the discrepancy by inserting an unconditional unlock: transaction-level advisory locks do not have an explicit unlock operation.

Repair boundary: modify one implicated layer, regenerate a clean fixture directory, and rerun the entire manifest. A partially repaired run cannot inherit pass results from an older evidence bundle.

Apply the smallest safe repair

The repair depends on the application’s intended ownership model.

If the lock is supposed to exist only for the transaction or subtransaction performing the protected operation, prefer the transaction-level function family:

BEGIN;
SAVEPOINT protected_step;

SELECT pg_advisory_xact_lock(900001);

-- protected work

ROLLBACK TO SAVEPOINT protected_step;
-- fixture must prove the intended post-boundary availability

COMMIT;

PostgreSQL defines pg_advisory_xact_lock() as transaction scoped and provides no explicit transaction-lock unlock function.

If the application actually requires session scope, keep pg_advisory_lock() but balance every acquisition deliberately:

SELECT pg_advisory_lock(900002);

-- work spanning transaction boundaries

SELECT pg_advisory_unlock(900002);

Do not assume an enclosing ROLLBACK, exception handler, or savepoint rollback performs that cleanup; PostgreSQL explicitly says session-level advisory locks ignore transaction rollback semantics. An unlock is also effective even if the surrounding transaction later fails.

Avoid casually substituting:

SELECT pg_advisory_unlock_all();

for targeted cleanup. It releases all session-level advisory locks held by the current session, so it is broader than a one-resource repair.

The repair acceptance criterion is not “the original test turned green.” Re-run C1–C8 so the negative control, transaction boundaries, session cleanup, and stacking behavior still provide coverage.

Test rollback and rerun safety

A rerun starts from a fresh server state, not from whatever the previous case left behind.

docker compose down -v
rm -rf evidence
mkdir evidence
docker compose up -d

Then recapture SELECT version() and the image reference. This prevents a leaked session-level lock, stale barrier file, or reused transcript from making the next execution appear deterministic when it is actually contaminated by prior state.

Before every contested case, optionally assert the target key is absent:

SELECT count(*)
FROM pg_locks
WHERE locktype = 'advisory'
  AND objsubid = 1
  AND ((classid::bigint << 32) | objid::bigint) = 771001;

Expected starting count: zero.

After each case, assert no test key remains:

SELECT
  ((classid::bigint << 32) | objid::bigint) AS advisory_key,
  pid,
  mode
FROM pg_locks
WHERE locktype = 'advisory'
  AND objsubid = 1
  AND ((classid::bigint << 32) | objid::bigint)
      BETWEEN 771001 AND 771008;

A zero-row result is cleanup evidence only when taken after all owning sessions have been intentionally closed or unlocked. PostgreSQL documents that session locks persist until unlock/session end and transaction locks until their transaction lifetime ends.

Ansible check-mode task coverage similarly treats a green dry run as weaker than explicit coverage evidence. Its subject is Ansible check-mode execution.

Assign operational ownership

Assign ownership by evidence layer:

Boundary: the person changing application code must not silently rewrite the expected manifest after seeing a failing result.

Expected result: any changed expectation is reviewed as a contract change, with a documented PostgreSQL basis.

Rejection criterion: if the same actor can retrospectively redefine the oracle so every observed outcome passes, the test has no independent acceptance value. Mark the release HOLD.

Make the release decision

Use four explicit outcomes.

The execution-state decision for this unexecuted fixture is HOLD / NOT EXECUTED. That is not a statement that PostgreSQL behavior is unknown; PostgreSQL’s session- and transaction-scope rules are well documented. It is a statement about the evidence standard used in this playbook: this text does not claim a laboratory observation that was not actually made.

When the suite is run, the minimum acceptance reconciliation should read:

C1 session/after:
  pg_locks = present
  peer_try = false

C2 xact/after:
  pg_locks = absent
  peer_try = true

C3 session/before:
  pg_locks = present
  peer_try = false

C4 xact/before:
  pg_locks = present until outer xact ends
  peer_try = false

C5 stacking:
  one unlock insufficient
  balanced unlock releases

C6 negative:
  peer_try = true

C7 xact end:
  blocked before commit
  free after commit

C8 session end:
  blocked while owner lives
  free after owner disconnects

Until real artifacts replace those expected lines, each remains EXPECTED / NOT EXECUTED.

Connect the practice to Refonte Learning

Refonte Learning offers the Database Administrator Essentials program. The current page describes training in database management and administration and lists database design, SQL query optimization, backup and recovery, performance tuning, security, cloud database management, migration, disaster recovery, role-based access control, and monitoring among its stated competencies. It currently lists a three-month period, 12–14 hours per week, basic programming knowledge as recommended, hands-on projects, and a potential internship.

For structured DBA practice beyond this isolated advisory-lock fixture, explore the Database Administrator Essentials program.