2026-09-13 · 8 min read
- sql
- locking
- distributed-systems
Advisory locks are coordination primitives whose meaning is supplied by the application. Unlike row locks, they do not automatically protect a table or enforce a database constraint.
They can serialize migrations, singleton jobs, or per-resource operations when every participant derives the same key and obeys the same ownership contract.
Design a stable lock namespace
Specify key width, signedness, tenant scope, resource type, canonical identifier encoding, and collision handling. Do not rely on a process-randomized language hash or an undocumented truncation.
- Domain-separate keys for unrelated workflows.
- Include tenant identity where resources are tenant-local.
- Version key derivation before changing it.
- Avoid placing confidential identifiers in diagnostics.
Choose transaction or session lifetime
Transaction-scoped locks release with commit or rollback and fit work contained in one database transaction. Session-scoped locks survive transaction boundaries and must be released by the same physical connection.
A pool can return a connection while it still owns a session lock or route an unlock call to another session. Pin the connection for the complete ownership period and discard it when cleanup cannot be proven.
Control waits, ordering, and side effects
Prefer bounded acquisition with observable timeout behavior. When an operation requires several locks, define one global ordering to reduce deadlocks and handle partial acquisition cleanup.
- Re-check protected state after acquiring the lock.
- Keep transactions and ownership periods short.
- Use idempotency for effects outside the database.
- Treat connection loss and uncertain commit as distinct outcomes.
Test with real pool behavior
Use Flashman's SQL formatter for statements, UUID generator for disposable resource IDs, timestamp converter for wait timelines, diff for ownership histories, and hash for stable public key fixtures.
Test contention, collisions, nested helpers, multiple tenants, pool checkout changes, rollback, commit, cancellation, timeout, deadlock ordering, connection loss, process termination, deploy overlap, retries, and every supported database engine.