<literal><function>pg_advisory_xact_lock_shared(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>

</entry>

<entry><type>void</type></entry>

<entry>Obtain sharedadvisory lock for the current transaction</entry>

<entry>Obtain sharedtransaction level advisory lock</entry>

</row>

<row>

<entry>

<function>pg_advisory_lock</> locks an application-defined resource,

which can be identified either by a single 64-bit key value or two

32-bit key values (note that these two key spaces do not overlap).

The key type is specified in <literal>pg_locks.objid</>. If

another session already holds a lock on the same resource, the

function will wait until the resource becomes available. The lock

If another session already holds a lock on the same resource identifier,

this function will wait until the resource becomes available. The lock

is exclusive. Multiple lock requests stack, so that if the same resource

is locked three times it mustbe also unlocked three times to be

is locked three times it mustthen be unlocked three times to be

released for other sessions' use.

</para>

a shared rather than an exclusive lock.

</para>

<indexterm>

<primary>pg_advisory_unlock</primary>

</indexterm>

<para>

<function>pg_advisory_unlock</> will release a previously-acquired

exclusive session level advisory lock. It

returns <literal>true</> if the lock is successfully released.

If the lock was not held, it will return <literal>false</>,

and in addition, an SQL warning will be reported by the server.

</para>

<indexterm>

<primary>pg_advisory_unlock_shared</primary>

</indexterm>

<para>

<function>pg_advisory_unlock_shared</> works the same as

<function>pg_advisory_unlock</>,

except it releases a shared session level advisory lock.

</para>

<indexterm>

<primary>pg_advisory_unlock_all</primary>

</indexterm>

<para>

<function>pg_advisory_unlock_all</> will release all session level advisory

locks held by the current session. (This function is implicitly invoked

at session end, even if the client disconnects ungracefully.)

</para>

<indexterm>

<primary>pg_advisory_xact_lock</primary>

</indexterm>

cannot be released explicitly.

</para>

<indexterm>

<primary>pg_advisory_unlock</primary>

</indexterm>

<para>

<function>pg_advisory_unlock</> will release a previously-acquired

exclusive session level advisory lock. It

returns <literal>true</> if the lock is successfully released.

If the lock was not held, it will return <literal>false</>,

and in addition, an SQL warning will be raised by the server.

</para>

<indexterm>

<primary>pg_advisory_unlock_shared</primary>

</indexterm>

<para>

<function>pg_advisory_unlock_shared</> works the same as

<function>pg_advisory_unlock</>,

except it releases a shared session level advisory lock.

</para>

<indexterm>

<primary>pg_advisory_unlock_all</primary>

</indexterm>

<para>

<function>pg_advisory_unlock_all</> will release all session level advisory

locks held by the current session. (This function is implicitly invoked

at session end, even if the client disconnects ungracefully.)

</para>

</sect1>

<sect1 id="functions-trigger">

<sect2 id="advisory-locks">

<title>Advisory Locks</title>

<indexterm zone="advisory-locks">

<primary>advisory lock</primary>

</indexterm>

<indexterm zone="advisory-locks">

<primary>lock</primary>

<secondary>advisory</secondary>

called <firstterm>advisory locks</>, because the system does not

enforce their use &mdash; 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.</para>

that are an awkward fit for the MVCC model.

For example, a common use of advisory locks is to emulate pessimistic

locking strategies typical of so called <quote>flat file</> data

management systems.

While a flag stored in a table could be used for the same purpose,

advisory locks are faster, avoid table bloat, and are automatically

cleaned up by the server at the end of the session.

</para>

<para>

There are two different types of advisory locks in

<productname>PostgreSQL</productname>: session level and transaction level.

Once acquired, a session level advisory lock is held until explicitly

released or the session ends. Unlike standard locks, session level

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 session level 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.) Transaction level locks on

the other hand behave more like regular locks; they are automatically

released at the end of the transaction, and can not be explicitly unlocked.

Session and transaction level locks share the same lock space, which means

that a transaction level lock will prevent another session from obtaining

a session level lock on that same resource and vice versa.

Like all locks in <productname>PostgreSQL</productname>, a complete list of

advisory locks currently held by any session can be found in the

<link linkend="view-pg-locks"><structname>pg_locks</structname></link>

system view.

There are two ways to acquire an advisory lock in

<productname>PostgreSQL</productname>: 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. A lock can be acquired multiple

times by its owning process; for each completed lock request there must

be a corresponding unlock request before the lock is actually released.

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. This behavior

is often more convenient than the session-level behavior for short-term

usage of an advisory lock.

Session-level and transaction-level lock requests for the same advisory

lock identifier will block each other in the expected way.

If a session already holds a given advisory lock, additional requests by

it will always succeed, even if other sessions are awaiting the lock; this

statement is true regardless of whether the existing lock hold and new

request are at session level or transaction level.

</para>

<para>

Advisory locks are allocated out of a shared memory pool whose size

is defined by the configuration variables

Like all locks in

<productname>PostgreSQL</productname>, a complete list of advisory locks

currently held by any session can be found in the <link

linkend="view-pg-locks"><structname>pg_locks</structname></link> system

view.

</para>

<para>

Both advisory locks and regular locks are stored in a shared memory

pool whose size is defined by the configuration variables

<xref linkend="guc-max-locks-per-transaction"> and

<xref linkend="guc-max-connections">.

Care must be taken not to exhaust this

</para>

<para>

A common use of advisory locks is to emulate pessimistic locking

strategies typical of so called <quote>flat file</> data management

systems.

While a flag stored in a table could be used for the same purpose,

advisory locks are faster, avoid MVCC bloat, and can be automatically

cleaned up by the server at the end of the session.

In certain cases using this advisory locking method, especially in queries

In certain cases using advisory locking methods, especially in queries

involving explicit ordering and <literal>LIMIT</> clauses, care must be

taken to control the locks acquired because of the order in which SQL

expressions are evaluated. For example:

principle that autovacuum has a low locking priority (eg it must not block

DDL on the table).

User Locks

----------

User Locks (Advisory Locks)

---------------------------

User locks are handled totally on the application side as long term

cooperative locks which may extend beyond the normal transaction boundaries.

User locks and normal locks are completely orthogonal and they don't

interfere with each other.

There are two types of user locks: session leveland transaction level.

Sessionleveluser locks are not released at transaction end. They must

be releasedexplicitly by the application --- but they are released

leveluserlocks are released automatically at the end of the transaction

as like as other normal locks.

User locks can be acquired either at session levelor transaction level.

A session-levellock request is notautomaticallyreleased at transaction

end, but must beexplicitlyreleasedby the application. (However, any

userlock requests behave the same as normal lock requests, in that they

are released at transaction end and do not need explicit unlocking.

Locking during Hot Standby

--------------------------