2026-09-13 · 6 min read · Rahul Chitturi
- sql
- locking
- concurrency
Advisory locks let applications coordinate work using database-managed keys, but the database does not know which business invariant the key represents. A lock can appear ineffective when two callers derive different keys or can remain held when session-scoped ownership returns to a pool.
Transaction-scoped and session-scoped locks have different release behavior. Framework helpers can hide which physical connection executes each statement.
Define ownership and key derivation
Document the database function, lock namespace, key width, signedness, collision policy, wait mode, timeout, and whether ownership belongs to a transaction or session. Generate the same key through one shared routine.
- Include tenant boundaries in business lock keys.
- Avoid unstable language-specific hash functions.
- Use a deterministic acquisition order for multiple locks.
- Record success or timeout without logging sensitive names.
Keep the physical connection visible
Session locks must be released on the same connection that acquired them. Returning that connection to a pool before unlock can block unrelated work, while attempting unlock on another connection does nothing useful.
Prefer transaction-scoped locks when their lifetime matches the protected database operation. Connection loss, rollback, cancellation, and commit uncertainty still need explicit tests for the selected engine.
Build a two-worker reproduction
Use the SQL formatter for lock statements, UUID generator for synthetic resource identities, timestamp converter for wait timelines, and diff for expected ownership events.
Test key collisions, two tenants, nested helpers, pool checkout changes, rollback, commit, cancellation, statement timeout, connection loss, deadlock ordering, process crash, retry, and deployment overlap.