PostgreSQL Advisory Locks: Avoid Session Surprises in Job Workers

PostgreSQL advisory locks can coordinate cooperating workers, but their lifetime matters. A session lock follows a database session, not an application request. With connection pooling, returning a connection does not necessarily end that session. A lock can therefore survive longer than the code that believes it owns it.

For short database work, a transaction-level advisory lock often provides a clearer boundary. The PostgreSQL explicit-locking documentation describes both lifetimes. Neither form automatically makes an external job execute exactly once.

Define the thing being coordinated

Choose a narrow resource: for example, rebuilding one tenant’s summary for one reporting period. Do not start with a global lock merely because it is easier to name. A global lock can serialize unrelated tenants and turn one slow job into a platform-wide queue.

Create a documented key namespace. A lock identifier must mean the same thing to every cooperating worker and must not collide accidentally with an unrelated feature. If you derive numeric keys from names, define the mapping and collision policy explicitly.

Advisory locks are cooperative. Code that ignores the convention can still modify the same records. Use ordinary constraints and transactions for invariants that must hold regardless of whether every caller remembers the lock.

Prefer a visible transaction boundary for short work

An illustrative transaction might be:

BEGIN;
SELECT pg_try_advisory_xact_lock(4102, 73);
-- Continue only if the returned boolean is true.
-- Perform the short, database-local update here.
COMMIT;

The two integer values are invented namespace and resource examples. Do not paste them into production without your own mapping. The application must inspect the returned boolean: a failed try-lock is not permission to continue anyway.

Run all protected statements on the same database connection within that transaction. A helper that starts another connection for the actual work defeats the intended boundary. On an unsuccessful acquisition, roll back or complete the no-work transaction cleanly.

Understand session-lock behavior

Session-level locks remain until explicitly released or the session ends. PostgreSQL documents that they do not simply disappear because the transaction that acquired them rolls back. Repeated acquisitions by the owning session also require corresponding releases.

That behavior can be useful for a dedicated long-lived coordinator connection. It is much harder to reason about when arbitrary application requests borrow and return pooled sessions. The next borrower may inherit a session whose lock state the application layer no longer understands.

Do not “fix” this by indiscriminately releasing every advisory lock on every shared connection. Another legitimate component may depend on one. Prefer a design with explicit ownership and a lifetime aligned to the actual work.

Test through the real pool mode

Build a two-worker staging test using the same pooling mode as production. Worker A acquires the chosen lock and pauses. Worker B tries the same key and should receive the expected contention result. Then finish or roll back A and verify that B can proceed.

Repeat with connection recycling, cancellation, and an application exception. If the pool uses transaction-level multiplexing, do not assume a later statement runs on the same server session unless the documented transaction behavior guarantees it.

Inspect pg_locks with the appropriate database permissions when diagnosing retained locks. Record the owning session and application context rather than guessing from an HTTP request ID alone.

Keep network calls out of a long open transaction

Holding a database transaction while waiting for an external report service or email provider can retain locks and resources for an unpredictable time. A transaction-level lock is not a reason to wrap an entire multi-minute workflow in one transaction.

Instead, consider a short claim-and-state transition followed by separately managed work, with recovery rules appropriate to the job. The correct design depends on whether work can be retried and how completion is recorded.

If a worker calls an external service successfully and crashes before saving completion, the next worker may repeat that call. The advisory lock has coordinated concurrency, but it has not removed the crash window. Verify external effects using the service’s supported safeguards and your durable job state.

Choose waiting and failure policy deliberately

A try-lock can let duplicate workers exit or defer without blocking. A waiting lock can be appropriate when work must run after the current owner finishes. Set timeouts and monitor contention so a stuck owner does not look like normal demand.

Track acquisition attempts, contention, hold duration, and work outcomes. A high contention rate may mean the scheduler repeatedly dispatches the same task or that the key is broader than intended. Do not increase worker count before understanding which one it is.

Add a test for two different resource keys as well. Independent jobs should proceed concurrently; accidentally reusing one constant key can make a correct-looking lock turn into an avoidable global bottleneck.

Conclusion

Align advisory-lock lifetime with the work and the real connection pool. Transaction-level locks simplify short database operations; session locks require explicit ownership. Neither replaces durable job state, database constraints, or verification of external effects.