pg_advisory_xact_lock(bigint) acquires a transaction-scoped advisory lock — released automatically on commit or rollback. Used to serialize application-level operations that don’t map cleanly to row locks (e.g. all writes to a location’s child rows across multiple tables).
Session-scoped vs transaction-scoped in a connection pool
Prefer pg_advisory_xact_lock over pg_advisory_lock when using a connection pool.
Session-scoped locks (pg_advisory_lock) are tied to the database connection, not the transaction. In a pool, connections are reused across requests. If the lock holder panics and pg_advisory_unlock() never runs, the lock stays alive on the pooled connection indefinitely — blocking all future writers on that key for as long as the connection lives in the pool.
Transaction-scoped locks are released with the transaction. Go’s defer tx.Rollback() guarantees the transaction ends even on panic, so the lock is always released.
Warning
Session-scoped advisory locks leak on panic in connection pools — always use
pg_advisory_xact_lockunless you have explicit control over connection lifetime.
Namespacing bigint lock keys
pg_advisory_xact_lock takes a bigint, so string keys must be hashed. A non-obvious issue: if you later add advisory locks for a different domain, two different string keys could hash to the same int64 and produce false contention.
Fix: prepend a namespace prefix before hashing.
// MD5 of "location_external_id:{id}", first 8 bytes as big-endian int64
h := md5.Sum([]byte("location_external_id:" + externalID))
key := int64(binary.BigEndian.Uint64(h[:8]))The prefix scopes the key space per lock domain, making collisions negligible even as lock usage grows.
Tip
Namespace your lock keys — prepend a domain prefix before hashing so future advisory lock usages on other key spaces don’t produce false contention.
See also
- sqlserver-deadlock-detection — deadlock detection and victim selection in SQL Server
- postgres-returning-tcp-session-stall — another Postgres gotcha: TCP stall from
UPDATE ... RETURNING