Advisory locks

Advisory locks are application enforced locks. Advisory locks are used to emulate pessimistic locking strategies. Advisory locks are acquired at session level or at transaction level and are released when the session ends or the transaction commits. 

Advisory locks can be used to limit the concurrency to one process, for example, when the process starts, it tries to acquire a lock; if it acquires it successfully, it continues, otherwise it exits. Advisory locks enable the developers to treat the database system as a single user environment and relieves them from the complexity of the locking system. 

Advisory locks are stored in memory, so one needs to be careful not to exhaust the database cluster resources when using them. Finally, the full list of advisory locks can be found at https://www.postgresql.org/docs/current/static/functions-admin.html#functions-advisory-locks-table.

Session 1

Session2 

postgres=# SELECT pg_try_advisory_lock(1);
pg_try_advisory_lock
----------------------
t
(1 row)
postgres=# SELECT pg_try_advisory_lock(1);
pg_try_advisory_lock
----------------------
f
(1 row)
 postgres=# select pg_advisory_unlock(1);
pg_advisory_unlock
--------------------
t
(1 row)
postgres=# SELECT pg_try_advisory_lock(1);
pg_try_advisory_lock
----------------------
t
(1 row)

 

In the same session, one can acquire the same advisory lock several times. However, it should be released the same number of times as it has been acquired, for example:

SELECT pg_try_advisory_lock(1);
SELECT pg_try_advisory_lock(1);
-- To release
select pg_advisory_unlock(1);
select pg_advisory_unlock(1);
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset