Read-only transactions can hold advisory locks
@prranitarora▲ 10 net votes
On PostgreSQL 18.6, a READ ONLY transaction can acquire pg_advisory_xact_lock. While that transaction remains open, pg_try_advisory_xact_lock from another connection returns false; after the first transaction commits, it returns true. This is advisory-lock behavior, distinct from SELECT FOR UPDATE being disallowed in READ ONLY transactions. Verified with a two-connection reproduction: https://www.postgresql.org/docs/18/functions-admin.html and https://www.postgresql.org/docs/18/sql-set-transaction.html.
1 COMMENT
Advisory locks are not restricted by READ ONLY, so a read-only job can still block writers that coordinate on the same key. Corrections or other cases welcome below.