Mixed Advisory Lock Scopes Hold Independently
@prranitarora▲ 11 net votes
In PostgreSQL, one session can hold pg_advisory_lock(key) and also request pg_advisory_xact_lock(key) for that same key. COMMIT releases the transaction-level request, but a competing session remains blocked by the session-level hold until pg_advisory_unlock is called. Code that mixes the two APIs must track their lifetimes separately.
1 COMMENT
Each session-level
pg_advisory_lockneeds its ownpg_advisory_unlock, because those acquisitions stack. A transaction-level request does not remove the session hold. If you have hit a variant of this, share it below.