Articles

PostgreSQL Advisory Lock Interview Questions: Session Scope, Transactions, and Pooling

Practice PostgreSQL advisory lock interview questions with session ownership, transaction scope, pooling failures, key design, and safe recovery examples.

Two workers both receive true from an advisory-lock call and start the same export. PostgreSQL may still be enforcing its ownership rules. The application may have routed both requests through the same database backend, which can reacquire a lock it already owns.

Strong PostgreSQL advisory lock interview questions test whether logical work, database-session ownership, and lock lifetime line up. Start by naming the owner and the protected interval. Then explain connection-pool behavior, failure handling, and the durable record that prevents an already completed operation from running again.

Evidence boundary: This article uses PostgreSQL 18 and PgBouncer official documentation checked September 21, 2026. Official facts are labeled. Worker timelines, key-design choices, and recovery policies are original interview exercises. No candidate reports are used. SQL is illustrative and was not executed against a PostgreSQL server for this article.

What does an advisory lock actually protect?

Official fact: Advisory locks have meanings assigned by the application. PostgreSQL does not require other code to acquire them before accessing the underlying business data. Session locks survive transaction rollback and remain until explicitly released or the session ends; transaction locks end with their transaction. PostgreSQL advisory locking

Original scenario: Only one worker should prepare tenant 42's daily export at a time. The application defines a lock key for that export scope. Every participating worker must use the same mapping and acquire the lock before reading the state used to decide what to do.

A maintenance script that ignores the convention can still change the export tables. Therefore, keep database constraints for properties that must hold regardless of which code path writes. Advisory coordination is useful when the protected concept spans several rows or does not yet have a row to lock.

Ask what “one export” means. Is exclusion per tenant, per tenant and date, or global? A global key makes unrelated tenants wait. A date-only key can accidentally serialize every tenant sharing that date. The business scope determines the mapping.

Which function should you choose?

Official fact: PostgreSQL provides blocking and nonblocking advisory-lock functions, with session and transaction variants. pg_try_advisory_xact_lock returns a Boolean immediately; false means the requested lock was unavailable. Transaction-level locks have no explicit unlock function. Advisory-lock functions

Use a transaction-scoped lock when the protected decision and writes fit in one short transaction. Consider a session-scoped lock only when the operation needs that lifetime and the application can retain the owning connection through cleanup.

The following original outline uses Read Committed and makes the branch explicit:

BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT pg_try_advisory_xact_lock(7301, 42) AS acquired;
-- Application reads acquired on this transaction connection.
-- false: ROLLBACK; return busy or schedule a bounded retry.
-- true: inspect export state and perform protected DB writes.
COMMIT;

The comments describe application control flow; copying the block alone does not implement the Boolean branch. Do not execute protected writes after a false result.

Here, 7301 is an application-assigned namespace and 42 is the tenant identifier. Neither number is a PostgreSQL-reserved export identifier. A real implementation must document ranges and use bound parameters for variable keys.

Official fact: Without an explicit transaction block, PostgreSQL normally treats each statement as its own transaction. BEGIN

Interview inference: A transaction lock acquired by an isolated autocommit statement has already ended before the next statement begins. Put acquisition, decision, and mutation inside the same transaction and connection. Merely adding the word xact to the function name does not create that boundary.

Official fact: Read Committed uses a new snapshot for each command, while Repeatable Read retains a transaction snapshot. Transaction isolation

Design consequence: Acquiring an advisory lock does not refresh an older snapshot. In this outline, inspect protected state in a subsequent statement after acquisition. If the operation uses a stronger isolation level, reason separately about its snapshot and retry requirements.

Why does rollback not clean up every advisory lock?

Official fact: Session-level acquisitions are counted: repeated successful acquisitions require matching releases. A session can reacquire its own advisory lock, including when other sessions are waiting. Advisory-lock lifetime and reacquisition

Original debugging exercise: A helper acquires a session lock, calls another helper that acquires the same key, and releases once in a finally block. The developer sees the cleanup execute and assumes the connection is clean. One unmatched acquisition remains.

The diagnostic question is not just whether an unlock line ran. Trace how many ownership acquisitions occurred and which backend issued each release. Give one layer responsibility for ownership instead of letting nested helpers silently acquire the same session lock.

A transaction rollback is not a substitute for that cleanup protocol. Nor should generic application code release every session lock indiscriminately: a shared connection may carry another component's ownership assumptions. Encapsulate the connection and its lock lifecycle together.

For short database work, transaction scope removes an explicit release path and makes the intended boundary easier to review. It does not excuse swallowed exceptions, an unclosed transaction, or work accidentally performed on another connection.

How does transaction pooling break session-lock assumptions?

Official fact: PgBouncer transaction pooling assigns a server connection for a transaction and returns it afterward. Its feature matrix marks session-level advisory locks incompatible with transaction pooling. Session pooling retains a server connection for the client connection's lifetime. PgBouncer pooling modes

Original failure timeline:

Step	Ownership consequence
Worker A acquires a session lock on backend 71	PostgreSQL associates ownership with backend 71.
A ends its transaction and continues an external export	The pool may make backend 71 available while the session lock remains.
Worker B receives backend 71 and requests the same key	Reacquisition can succeed because this backend already owns the lock.
A later requests an unlock through a different backend	That request does not release backend 71's ownership.

The database identity stayed consistent; the application's worker identity did not. The example illustrates a misuse of session features under transaction pooling, not supported PgBouncer behavior that an application should rely on.

An application-side connection pool creates a related responsibility. Returning a connection is not proof that its database session ended or that every session feature was reset. Inspect the pool's actual reset contract and keep ownership within the checked-out connection's lifetime.

For transaction-scoped work, use one explicit transaction so acquisition and protected writes remain on its backend. For work spanning transactions, choose a connection arrangement that preserves session ownership or redesign coordination as durable job state. Changing pool size does not repair a lifetime mismatch.

How should you design lock keys?

Official fact: Advisory APIs accept either one 64-bit integer or two 32-bit integers; these are separate key spaces. The same numeric-looking application identifier encoded through different overloads does not automatically identify the same lock. Advisory key signatures

Design recommendation: Define a small registry of namespaces and resource mappings. For a tenant-wide operation, a two-integer namespace and tenant ID may be readable. If tenant IDs exceed that representation, choose and document a different mapping rather than truncating them.

Hashing a string is a design tradeoff, not proof of uniqueness. Collisions can make unrelated resources contend. More dangerously, inconsistent hashing across languages or deployments can give the same business resource different keys, allowing concurrent work.

Version the mapping carefully. During a rolling deployment, old and new workers must still coordinate on the same business scope. Treat a key-format migration like a protocol change, with a compatibility plan.

Official fact: Advisory locks are local to a database. Equal keys acquired in different databases do not form one shared ownership domain. pg_locks and advisory identifiers

That matters when tenant routing, failover topology, or separate databases enter the design. Write down where all contenders connect; a key convention alone cannot join independent lock managers.

Should a worker wait, retry, or skip?

A try-lock failure is a business-policy branch. For an optional cache refresh, skipping can be acceptable. For an export that must eventually complete, persist pending work or use a queue so “busy” does not silently become “lost.”

Original policy matrix:

Event	Proposed application response
Try-lock returns false	Return busy, skip only if allowed, or schedule a bounded retry.
Lock wait times out	Roll back the failed transaction and classify the attempt before retrying.
Protected work raises an error	End the transaction or release owned session acquisitions on the owning connection.
Connection is lost	Stop assuming ownership; reconcile durable work state before resuming.

Official fact: lock_timeout limits time spent waiting for each lock acquisition; statement_timeout limits statement duration more broadly. Neither is a business-operation deadline by itself. Client timeout settings

Design inference: Give retries a deadline and delay with jitter. Immediate polling by hundreds of workers turns a busy resource into repeated database traffic. Record contention separately from application errors so operators can distinguish expected exclusion from a broken job.

When several keys are needed, define one ordering across all participating code paths. Draw the wait graph for opposite orders before assuming a short critical section makes deadlock impossible. Also consider row locks acquired inside that section; advisory keys are only part of the lock ordering.

What should you inspect when workers appear stuck?

Official fact: pg_locks exposes advisory-lock rows, backend PIDs, lock mode, and whether the request is granted. It can be joined to pg_stat_activity for session context. Inspecting locks

Official fact: pg_backend_pid() identifies the current backend, and pg_blocking_pids() helps identify processes blocking a specified backend. Session information functions

An original diagnostic query is:

SELECT pid, database, classid, objid, objsubid, mode, granted
FROM pg_locks
WHERE locktype = 'advisory'
ORDER BY pid, classid, objid;

Pair those observations with application logs containing job ID, logical resource, lock-key mapping version, acquisition time, and backend PID. Keep sensitive payloads out of routine coordination logs.

Interpret a granted lock as ownership evidence, not proof that the job is making progress. A worker may be idle, waiting for an external service, or stuck after losing its request context. Likewise, absence of a waiter does not prove absence of contention when clients use nonblocking try-lock calls.

Avoid terminating a backend simply because it is old. First identify the owning job and recovery consequence. Ending ownership does not tell a replacement worker whether prior external work succeeded.

Does advisory locking provide exactly-once external work?

Original failure analysis: A worker sends an export to object storage, then loses its connection before recording completion. Another worker eventually acquires the key. It cannot infer from successful acquisition whether the earlier upload happened.

Use a durable operation identity and a destination contract that tolerates replay. For example, write an export to a deterministic versioned object name, verify its contents, and record completion against that generation. The appropriate protocol depends on the destination's actual conditional-write and idempotency capabilities.

If an old worker can continue external writes after ownership is lost, mutual exclusion inside PostgreSQL is insufficient. A fencing design requires the destination to reject stale ownership generations; an advisory lock does not itself supply that enforcement.

In an interview, separate three claims: only one cooperating backend currently owns a key; the business operation has a durable completion record; the external destination rejects duplicates or stale writers. Explain which mechanism establishes each claim.

Practice ownership and recovery reasoning

The PostgreSQL-specific follow-ups are original additions, not claims about the technology used in the reported question.

Practice question	Advisory-lock follow-up
Debug a Concurrent Job Scheduler	Track logical worker identity separately from backend identity.
Identify and fix deadlock in locked code	Include advisory and row locks in one acquisition-order review.
Reason About Indexes, Kafka, and Database Locking	Explain the boundary between cooperative exclusion and database constraints.
Choose lock granularity for concurrent storage	Choose tenant, job, or global scope and defend contention tradeoffs.
Reviewing a Freight-Scheduler Codebase: Bugs, Data Structures, and Concurrency	Inspect exception paths, pool returns, duplicate starts, and durable completion.

Use the PostgreSQL interview guide to connect these ownership decisions to MVCC and transaction design. Practice tracing the backend, transaction, and business operation through one failure at a time.