|The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 454 pages
RRP £14.95 ($19.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
11.3.4 Advisory Locks
PostgreSQL provides a means for creating locks that have application-defined meanings. These are called advisory locks, because the system does not enforce their use--it is up to the application to use them correctly. Advisory locks can be useful for locking strategies that are an awkward fit for the MVCC model. Once acquired, an advisory lock is held until explicitly released or the session ends. Unlike standard locks, advisory locks 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. The same lock can be acquired multiple times by its owning process: for each lock request there must be a corresponding unlock request before the lock is actually released. (If a session already holds a given lock, additional requests will always succeed, even if other sessions are awaiting the lock.) Like all locks in PostgreSQL, a complete list of advisory locks currently held by any session can be found in the pg_locks system view.
Advisory locks are allocated out of a shared memory pool whose size
is defined by the configuration variables
Care must be taken not to exhaust this
memory or the server will be unable to grant any locks at all.
This imposes an upper limit on the number of advisory locks
grantable by the server, typically in the tens to hundreds of thousands
depending on how the server is configured.
A common use of advisory locks is to emulate pessimistic locking
strategies typical of so called “flat file” data management
While a flag stored in a table could be used for the same purpose,
advisory locks are faster, avoid MVCC bloat, and are automatically
cleaned up by the server at the end of the session.
In certain cases using this advisory locking method, 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; -- ok
In the above queries, the second form is dangerous because the
LIMIT is not guaranteed to be applied before the locking
function is executed. This might cause some locks to be acquired
that the application was not expecting, and hence would fail to release
(until it ends the session).
From the point of view of the application, such locks
would be dangling, although still viewable in
The functions provided to manipulate advisory locks are described in Table 7-61.
|ISBN 9781906966041||The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference||See the print edition|