row-level lock on the specified row, so it succeeds in
     updating that row. However, the second <command>UPDATE</command>
     statement finds that the row it is attempting to update has
     already been locked, so it waits for the transaction that
     acquired the lock to complete. Transaction two is now waiting on
     transaction one to complete before it continues execution. Now,
     transaction one executes:

<screen>
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;
</screen>

     Transaction one attempts to acquire a row-level lock on the
     specified row, but it cannot: transaction two already holds such
     a lock. So it waits for transaction two to complete. Thus,
     transaction one is blocked on transaction two, and transaction
     two is blocked on transaction one: a deadlock
     condition. <productname>PostgreSQL</productname> will detect this
     situation and abort one of the transactions.
    </para>

    <para>
     The best defense against deadlocks is generally to avoid them by
     being certain that all applications using a database acquire
     locks on multiple objects in a consistent order. In the example
     above, if both transactions
     had updated the rows in the same order, no deadlock would have
     occurred. One should also ensure that the first lock acquired on
     an object in a transaction is the most restrictive mode that will be
     needed for that object.  If it is not feasible to verify this in
     advance, then deadlocks can be handled on-the-fly by retrying
     transactions that abort due to deadlocks.
    </para>

    <para>
     So long as no deadlock situation is detected, a transaction seeking
     either a table-level or row-level lock will wait indefinitely for
     conflicting locks to be released.  This means it is a bad idea for
     applications to hold transactions open for long periods of time
     (e.g., while waiting for user input).
    </para>
   </sect2>

   <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>
    </indexterm>

    <para>
     <productname>PostgreSQL</productname> provides a means for
     creating locks that have application-defined meanings.  These are
     called <firstterm>advisory locks</firstterm>, 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.
     For example, a common use of advisory locks is to emulate pessimistic
     locking strategies typical of so-called <quote>flat file</quote> 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 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