Context
A background task acquires an advisory lock, returns its connection to the pool, and continues doing work. Another request later inherits that connection, while the original task tries to release the lock through a different one. The code treats a connection pool as interchangeable access to PostgreSQL, but session-level locks belong to database sessions. Lock lifetime must match the application's connection ownership.
Distinguish session and transaction scope
PostgreSQL documents session-level advisory locks that remain until explicitly released or the session ends, and transaction-level advisory locks released with transaction completion. Session-level lock changes do not follow ordinary transaction rollback semantics. The lock manager coordinates callers using the same key, but the application must define what that key represents and ensure participants follow the protocol.
An advisory lock does not automatically protect a table or stop an unrelated query from changing the same business data. It is cooperative. Identify every code path that must honour it and retain database constraints where they express the real invariant. A unique constraint may be more direct protection against duplicate records than a lock used inconsistently across services.
An illustrative scheduler collision
Imagine a report generator using a session lock to ensure one worker per customer. It acquires the lock through a short pool operation, then performs the report outside that connection. Cleanup borrows another connection and fails to release the original lock. Later reports remain blocked even though the generating process appears finished.
Trace the database session identifier, advisory key, application task, transaction boundary, and pool checkout interval. This reveals whether the lock is held by the session the application still owns. A log saying “lock acquired” without session context is not enough to explain a pooling problem.
Choose the smallest correct lifetime
Use transaction-level locking when the protected work fits a database transaction and the semantics match. Keep the transaction short enough to avoid unnecessary retention and contention. External network calls inside the transaction need separate review.
If session-level ownership is required, hold and release the same connection through the protected interval and handle failure deliberately. Review the pooler's mode if a proxy sits between the application and PostgreSQL. Transaction pooling can change whether a client retains a stable server session across operations.
Design the key namespace
Choose a stable mapping from business resource to advisory key. Include a namespace for different operation types so unrelated tasks do not block each other accidentally. Avoid a hash scheme whose collision consequences are not understood.
Document how keys are derived across languages and services. Two implementations using different encodings may believe they protect the same customer while obtaining different locks. Test the mapping with shared examples and include the intended scope in code review.
Handle contention as an outcome
Decide whether a worker waits, tries once, or exits with a reschedule result. Use a bounded wait consistent with the task's execution budget. An indefinitely waiting job can consume worker capacity and hide the actual owner.
Expose a meaningful contention metric and identify the holder through supported database evidence where practical. Do not terminate a session merely because it holds a lock; first establish whether it is performing valid work and what interruption would do to the application.
Separate ownership from external effects
A database connection can be lost while application code continues computing or calling another service. If another worker subsequently obtains the lock, the old worker may still attempt an external action. A database advisory lock alone does not necessarily fence that remote resource.
Use stable operation identifiers, authoritative state transitions, or a resource-enforced fencing design where the business requires it. The appropriate mechanism depends on the external action. Keep the lock's guarantee narrow and document the remaining uncertain-outcome procedure.
Rehearse cleanup and replacement
Test normal completion, exception, transaction rollback, cancellation, connection loss, and worker termination. Observe the lock state and the protected business result after each case. Include pool return and reuse explicitly.
Run two workers for the same key and two for different keys. Confirm the first pair coordinates as intended while independent work can proceed. This checks both safety and unnecessary serialisation, which a single-worker test cannot establish.
Quick diagnostic map
| Evidence | Interpretation | Next check |
|---|---|---|
| Lock survives rollback | Session lifetime differs | Inspect owning session |
| Unlock has no effect | Different pooled connection | Compare backend identifiers |
| Old worker writes later | External effect not fenced | Enforce resource invariant |
A focused operator exercise
Use two workers sharing one pool and a synthetic advisory-key namespace. Record the database session identifier for acquisition, protected work, and release. Run same-key and different-key operations, then return and reuse connections. This shows whether the code retains the intended session and whether independent resources are being serialised unnecessarily.
Exercise transaction rollback, application exception, cancellation, connection loss, and worker termination separately. Inspect advisory lock state and the business operation ledger after each. If a pooler is present, repeat through its configured mode rather than testing only direct connections. The chosen lock lifetime must remain meaningful across the actual client-to-server session mapping.
Finally, pause an old worker while ownership can move and test its attempted external effect after recovery. Verify the destination's invariant or reconciliation rule, not just lock reacquisition. The exercise passes when operators can identify the owner, explain the release condition, and prevent an abandoned session or stale worker from causing duplicate or indefinitely blocked business work.
Keep the runbook session-aware
Record key derivation, lock scope, pool mode, ownership evidence, timeout, and recovery action. Give operators a way to identify abandoned work without guessing from a task name. Review the design after introducing a pooler or changing connection handling.
Clouvenix can help match PostgreSQL advisory locking to application transaction and pool boundaries. The result is coordination whose lifetime remains understandable during errors and worker replacement.
Frequently asked questions
Will rolling back a transaction release a session advisory lock?
Session-level advisory locks have their own documented lifetime and do not follow ordinary transaction rollback semantics. Choose transaction-level locks when that lifetime matches the requirement, or explicitly manage the owning session.
Can an advisory lock replace a unique constraint?
Usually they solve different problems. A constraint enforces a database invariant even when another code path ignores the advisory protocol. Use the most direct invariant enforcement and add cooperative coordination only where it serves a clear purpose.
Explore Clouvenix postgresql administration for implementation and operational handover.