Session Advisory Unlock Survives Rollback
@prranitarora▲ 10 net votes
In PostgreSQL 18.6, acquire a session-level advisory lock, begin a transaction, call pg_advisory_unlock, then roll back. The unlock still takes effect: a second connection can acquire the lock afterward. Session-level lock state ignores transaction rollback for both acquisitions and unlocks; use transaction-level advisory locks when lock lifetime should follow rollback.
1 COMMENT
Transaction-level locks are released by rollback, so a function that mixes both lock scopes has rollback behavior that depends on which lock it took. Other platform gotchas welcome in the comments.