Advisory locks in Postgres
Postgres provides different lock models to control concurrent access to data in your base. It can be table-level, row-level and page-level locks. This locks are controlled by Postgres itself, cause their behavior defined at database level. But there is one more interesting lock type — Advisory.
Advisory locks are recommendatory. They have no implementation on Postgres level and must be controlled on application-level. Postgres just provides simple mechanism to store that locks in database and check their state. Advisory locks are faster, avoid table bloat, and are automatically cleaned up by the server at the end of the session.
Advisory locks can be acquired at session level or at transaction level.
Once acquired at session level, an advisory lock is held until explicitly released or the session ends. Unlike standard lock requests, session-level advisory lock requests do not honor transaction semantics: a lock acquired during a transaction that is later rolled back will still be held following the rollback, and likewise an unlock is effective even if the calling transaction fails later.
Transaction-level lock requests, on the other hand, behave more like regular lock requests: they are automatically released at the end of the transaction, and there is no explicit unlock operation.
In short, advisory locks can lock operations from application on database level instead of data.
How it works in words:
- You make request to acquire lock with some identifier in transaction A. This can be thought of as “lock the operation with id = X”
- You make some SQL requests to your database
- If any other transaction, e.g. B, will attempt to acquire lock with the same id, then it will wait until such lock will be released by A. You can specify the timeout, so transaction will wait some time and if lock won’t be unlocked B fails.
- In the end A releases lock with id = X, so it will become available to other transactions
Creation of advisory locks in Postgres is simple:
// Acquire lock at session level with some key
pg_advisory_lock(key bigint)
// Acquire lock at session level with key pair
pg_advisory_lock(key1 integer, key2 integer)
// Acquire lock at transaction level with some key
pg_advisory_xact_lock(key bigint)
// Acquire lock at transaction level with key pair
pg_advisory_xact_lock(key1 integer, key2 integer)Transaction-level locks will be unlocked automatically. For session-level locks you can use:
pg_advisory_unlock(key bigint)
pg_advisory_unlock(key1 integer, key2 integer)Before acquiring lock you can also check if this lock is already in use:
// For session level locks
pg_try_advisory_lock(key bigint )
pg_try_advisory_lock(key1 integer, key2 integer)
// For transaction level locks
pg_try_advisory_xact_lock(key bigint)
pg_try_advisory_xact_lock(key1 integer, key2 integer)These functions will return true if lock is already in use and false otherwise. Unlike lock creation functions, pg_try_* are not waiting and will return result immediately.
Advisory locks can cause problems if you use it in wrong way!
In certain cases using advisory locking methods, especially in queries involving explicit ordering and LIMIT clauses, care must be taken to control the locks acquired because of the order in which SQL expressions are evaluated. For example:
SELECT pg_advisory_lock(id) FROM foo WHERE id = 12345; -- ok
SELECT pg_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100; -- danger!
SELECT pg_advisory_lock(q.id) FROM
(
SELECT id FROM foo WHERE id > 12345 LIMIT 100
) q; -- okBoth advisory locks and regular locks are stored in a shared memory pool whose size is defined by the configuration variables max_locks_per_transaction and max_connections. Care must be taken not to exhaust this memory or the server will be unable to grant any locks at all.
In my own practice I used advisory locks to operate with users balance safely. So, I’d been locking all balance changing operations.
Thanks for reading.Happy learning 😄
Do support our publication by following it
Also refer to the following articles.
