Notes de Jérôme Prudent
Notes publiques ...

« Notes

Postgres advisory lock

Créée le 02 Mar 2026

concurrency doc-review postgres

  • #postgres #concurrency #doc-review
  • https://medium.com/thefreshwrites/advisory-locks-in-postgres-1f993647d061
  • a lock that is purely applicative
    • the lock is not tied to data (table, row, …)
  • emulate pessimistic locking strategies typical of so-called “flat file” data management systems
    • pessimistic locking: lock the data upfront
      • good for highly concurrent data modifications
    • optimistic locking: let concurrent modifications of same data but fails to commit if version is older than current version
      • good for low concurrent data modifications
    • like a flag
  • flyway uses advisory lock to avoid concurrent migration of the same migration system
  • do not honor transaction semantics
    • if unlock and tx rollbacks => the lock is acquired
    • if lock and tx rollbacks => the lock is freed
  • can be acquired
    • at session
    • at request level: lock is automatically released, no specific unlock
  • there are 2 classes of advisory locks
    • single integer: pg_advisory_lock(bigint) stored in objid (classid = 0)
    • two integers: pg_advisory_lock(int, int) stored in classid and objid
  • lock is stored in shared memory pool
  • SELECT pg_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100; -- danger!
    • the function can be executed before the limit, hence locking undesired ids 
      • SELECT pg_advisory_lock(q.id) FROM
(
  SELECT id FROM foo WHERE id > 12345 LIMIT 100
) q; -- ok