Advisory Lock Functions

? advisory_docs.patch Index: src/sgml/func.sgml =================================================================== RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.338 diff -u -p -r1.338 func.sgml --- src/sgml/func.sgml 16 Sep 2006 00:30:13 -0000 1.338 +++ src/sgml/func.sgml 19 Sep 2006 18:19:56 -0000 @@ -10586,6 +10586,191 @@ SELECT * FROM pg_stat_file('filename'); SELECT (pg_stat_file('filename')).modification; + + + The functions shown in manage + advisory locks. For details about proper usage of these functions, see + . + + + + pg_column_size + + + + Advisory Lock Functions + + + Name Return Type Description + + + + + + + pg_advisory_lock(value bigint) + + void + Obtain exclusive advisory lock + + + + pg_advisory_lock(value1 int, value2 int) + + void + Obtain exclusive advisory lock if available + + + + + pg_advisory_lock_shared(value bigint) + + void + Obtain shared advisory lock + + + + pg_advisory_lock_shared(value1 int, value2 int) + + void + Obtain shared advisory lock + + + + + pg_try_advisory_lock(value bigint) + + boolean + Obtain exclusive advisory lock if available + + + + pg_try_advisory_lock(value1 int, value2 int) + + boolean + Obtain exclusive advisory lock if available + + + + + pg_try_advisory_lock_shared(value bigint) + + boolean + Obtain shared advisory lock if available + + + + pg_try_advisory_lock_shared(value1 int, value2 int) + + boolean + Obtain shared advisory lock if available + + + + + pg_advisory_unlock(value bigint) + + boolean + Release an exclusive advisory lock + + + + pg_advisory_unlock(value1 int, value2 int) + + boolean + Release an exclusive advisory lock + + + + + pg_advisory_unlock_shared(value bigint) + + boolean + Release a shared advisory lock + + + + pg_advisory_unlock_shared(value1 int, value2 int) + + boolean + Release a shared advisory lock + + + + + pg_advisory_unlock_all() + + boolean + Releases all advisory locks held by the current session + + + + +

+ + + pg_advisory_lock + + + pg_advisory_lock locks an application defined resource + (either defined by value or a combination of value1 and value2). The + function will wait until the resource becomes available. The lock + is exclusive. The locks can stack so that if the same resource is + locked three times it must be also unlocked three times to be completely + released. + + + + pg_advisory_lock_shared + + + pg_advisory_lock_shared as pg_advisory_lock, + except the lock can be shared with other sessions locking shared. + + + + pg_try_advisory_lock + + + pg_try_advisory_lock as pg_advisory_lock, + except the function will not wait until the lock is available. It will return + true if it is available (success) and false if it is not. + + + + pg_try_advisory_lock_shared + + + pg_try_advisory_lock_shared as + pg_advisory_lock_shared, except the function will not wait + until the lock is available. It will return true if it is available + (success) and false if it is not. + + + + pg_advisory_unlock + + + pg_advisory_unlock will release an exclude advisory lock. It + will return true if the lock is released. If no lock is available, it will + return false. In addition, an SQL warning will be raised by the backend. + + + + pg_advisory_unlock_shared + + + pg_advisory_unlock_shared as pg_advisory_unlock, + except to release a shared advisory lock. + + + + pg_advisory_unlock_all + + + pg_advisory_unlock_all as will release all advisory locks + held by the current session. + Index: src/sgml/mvcc.sgml =================================================================== RCS file: /projects/cvsroot/pgsql/doc/src/sgml/mvcc.sgml,v retrieving revision 2.62 diff -u -p -r2.62 mvcc.sgml --- src/sgml/mvcc.sgml 18 Sep 2006 12:11:36 -0000 2.62 +++ src/sgml/mvcc.sgml 19 Sep 2006 18:19:57 -0000 @@ -25,10 +25,10 @@ - Unlike traditional database systems which use locks for concurrency control, - PostgreSQL - maintains data consistency by using a multiversion model - (Multiversion Concurrency Control, MVCC). + PostgreSQL provides a rich set of tools + for developers to manage concurrent access to data. Internal + integrity data consistency is maintained by using a multiversion + model (Multiversion Concurrency Control, MVCC). This means that while querying a database each transaction sees a snapshot of data (a database version) as it was some @@ -36,7 +36,11 @@ This protects the transaction from viewing inconsistent data that could be caused by (other) concurrent transaction updates on the same data rows, providing transaction isolation - for each database session. + for each database session. MVCC, by eschewing + explicit locking methodologies of traditional database systems, + minimizes lock contention in order to allow for reasonable + performance in multiuser environments. + @@ -52,7 +56,9 @@ PostgreSQL for applications that cannot adapt easily to MVCC behavior. However, proper use of MVCC will generally provide better - performance than locks. + performance than locks. In addition, application defined advisory + locks provide a mechanism for acquiring locks that are not scoped + to a transaction. @@ -859,6 +865,66 @@ UPDATE accounts SET balance = balance - (e.g., while waiting for user input). + + + Advisory Locks + + + advisory + + + + PostgreSQL provides a means for + creating locks that have an application defined meaning, called + advisory locks. Advisory locks are suitable for locking strategies + that are an awkward fit for the MVCC model. They have a lifetime + that is scoped to the end of the session or the lock is released, + whichever is sooner. Unlike standard locks, advisory locks do not + honor transaction semantics. For example, a lock acquired during a + transaction that is rolled back will still be held following the + rollback. The same lock can be acquired multiple times by its + owning process: for each lock there must be a corresponding unlock + for the lock to be fully released. Like all locks in + PostgreSQL, a complete list of advisory + locks held by the system can be listed in the system catalog + pg_locks. + + + + Advisory locks are allocated out of a shared buffer pool whose size + is defined by the configuration variables max_connections and + max_locks_per_transaction. Care must be given not to exhaust this + memory or the server will not be able to grant any locks at all. + This imposes an aper limit on the amount of advisory locks + grantable by the server in the tens or 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 systems. + While a table based flag could be used for the same purpose, + advisory locks are faster, avoid MVCC bloat, and are cleaned up + by the server at the end of the session. + Integer based keys are especially well suited for this type of lock + management. In certain cases using this method, especially in queries + involving explicit ordering and limit, care must be given to control + the amount of locks required because of the order in which the SQL + expressions are evaluated. For example: + +SELECT pg_advisory_lock(id, 0, 0) FROM foo WHERE id = 12345; -- ok +SELECT pg_advisory_lock(id, 0, 0) FROM foo WHERE id > 12345 LIMIT 100; -- danger! +SELECT pg_advisory_lock(q.id, 0, 0) FROM +( + select * foo WHERE id > 12345 LIMIT 100; +) q; -- ok + + In the above queries, the second form is dangerous because the limit + expression is not guaranteed to evaluate before the locking function. + In this case, from the point of view of the application, the locks + would be dangling, although still viewable in pg_locks. + +