<!--
$PostgreSQL: pgsql/doc/src/sgml/ref/lock.sgml,v 1.48 2006/09/16 00:30:19 momjian Exp $
PostgreSQL documentation
-->

<refentry id="SQL-LOCK">
 <refmeta>
  <refentrytitle id="sql-lock-title">LOCK</refentrytitle>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>LOCK</refname>
  <refpurpose>lock a table</refpurpose>
 </refnamediv>

 <indexterm zone="sql-lock">
  <primary>LOCK</primary>
 </indexterm>

 <refsynopsisdiv>
<synopsis>
LOCK [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ IN <replaceable class="PARAMETER">lockmode</replaceable> MODE ] [ NOWAIT ]

where <replaceable class="PARAMETER">lockmode</replaceable> is one of:

    ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
    | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <command>LOCK TABLE</command> obtains a table-level lock, waiting
   if necessary for any conflicting locks to be released.  If
   <literal>NOWAIT</literal> is specified, <command>LOCK
   TABLE</command> does not wait to acquire the desired lock: if it
   cannot be acquired immediately, the command is aborted and an
   error is emitted.  Once obtained, the lock is held for the
   remainder of the current transaction.  (There is no <command>UNLOCK
   TABLE</command> command; locks are always released at transaction
   end.)
  </para>

  <para>
   When acquiring locks automatically for commands that reference
   tables, <productname>PostgreSQL</productname> always uses the least
   restrictive lock mode possible. <command>LOCK TABLE</command>
   provides for cases when you might need more restrictive locking.
   For example, suppose an application runs a transaction at the
   Read Committed isolation level and needs to ensure that data in a
   table remains stable for the duration of the transaction. To
   achieve this you could obtain <literal>SHARE</> lock mode over the
   table before querying. This will prevent concurrent data changes
   and ensure subsequent reads of the table see a stable view of
   committed data, because <literal>SHARE</> lock mode conflicts with
   the <literal>ROW EXCLUSIVE</> lock acquired by writers, and your
   <command>LOCK TABLE <replaceable
   class="PARAMETER">name</replaceable> IN SHARE MODE</command>
   statement will wait until any concurrent holders of <literal>ROW
   EXCLUSIVE</literal> mode locks commit or roll back. Thus, once you
   obtain the lock, there are no uncommitted writes outstanding;
   furthermore none can begin until you release the lock.
  </para>

  <para>
   To achieve a similar effect when running a transaction at the Serializable
   isolation level, you have to execute the <command>LOCK TABLE</> statement
   before executing any <command>SELECT</> or data modification statement.
   A serializable transaction's view of data will be frozen when its first
   <command>SELECT</> or data modification statement begins.  A <command>LOCK
   TABLE</> later in the transaction will still prevent concurrent writes
   &mdash; but it won't ensure that what the transaction reads corresponds to
   the latest committed values.
  </para>
  
  <para>
   If a transaction of this sort is going to change the data in the
   table, then it should use <literal>SHARE ROW EXCLUSIVE</> lock mode
   instead of <literal>SHARE</> mode.  This ensures that only one
   transaction of this type runs at a time.  Without this, a deadlock
   is possible: two transactions might both acquire <literal>SHARE</>
   mode, and then be unable to also acquire <literal>ROW EXCLUSIVE</>
   mode to actually perform their updates.  (Note that a transaction's
   own locks never conflict, so a transaction can acquire <literal>ROW
   EXCLUSIVE</> mode when it holds <literal>SHARE</> mode &mdash; but not
   if anyone else holds <literal>SHARE</> mode.)  To avoid deadlocks,
   make sure all transactions acquire locks on the same objects in the
   same order, and if multiple lock modes are involved for a single
   object, then transactions should always acquire the most
   restrictive mode first.
  </para>

  <para>
   More information about the lock modes and locking strategies can be
   found in <xref linkend="explicit-locking">.
  </para>
 </refsect1>

 <refsect1>
  <title>Parameters</title>

  <variablelist>
   <varlistentry>
    <term><replaceable class="PARAMETER">name</replaceable></term>
    <listitem>
     <para>
      The name (optionally schema-qualified) of an existing table to
      lock.
     </para>

     <para>
      The command <literal>LOCK TABLE a, b;</> is equivalent to
      <literal>LOCK TABLE a; LOCK TABLE b;</>. The tables are locked
      one-by-one in the order specified in the <command>LOCK
      TABLE</command> command.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">lockmode</replaceable></term>
    <listitem>
     <para>
      The lock mode specifies which locks this lock conflicts with.
      Lock modes are described in <xref linkend="explicit-locking">.
     </para>

     <para>
      If no lock mode is specified, then <literal>ACCESS
      EXCLUSIVE</literal>, the most restrictive mode, is used.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>NOWAIT</literal></term>
    <listitem>
     <para>
      Specifies that <command>LOCK TABLE</command> should not wait for
      any conflicting locks to be released: if the specified lock(s)
      cannot be acquired immediately without waiting, the transaction
      is aborted.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

 <refsect1>
  <title>Notes</title>

   <para>
    <literal>LOCK TABLE ... IN ACCESS SHARE MODE</> requires <literal>SELECT</>
    privileges on the target table.  All other forms of <command>LOCK</>
    require <literal>UPDATE</> and/or <literal>DELETE</> privileges.
   </para>

   <para>
    <command>LOCK TABLE</command> is useful only inside a transaction
    block (<command>BEGIN</>/<command>COMMIT</> pair), since the lock
    is dropped as soon as the transaction ends.  A <command>LOCK
    TABLE</> command appearing outside any transaction block forms a
    self-contained transaction, so the lock will be dropped as soon as
    it is obtained.
   </para>

  <para>
   <command>LOCK TABLE</> only deals with table-level locks, and so
   the mode names involving <literal>ROW</> are all misnomers.  These
   mode names should generally be read as indicating the intention of
   the user to acquire row-level locks within the locked table.  Also,
   <literal>ROW EXCLUSIVE</> mode is a sharable table lock.  Keep in
   mind that all the lock modes have identical semantics so far as
   <command>LOCK TABLE</> is concerned, differing only in the rules
   about which modes conflict with which. For information on how to
   acquire an actual row-level lock, see <xref linkend="locking-rows">
   and the <xref linkend="sql-for-update-share"
   endterm="sql-for-update-share-title"> in the <command>SELECT</command>
   reference documentation.
  </para>
 </refsect1>
  
 <refsect1>
  <title>Examples</title>

  <para>
   Obtain a <literal>SHARE</> lock on a primary key table when going to perform
   inserts into a foreign key table:

<programlisting>
BEGIN WORK;
LOCK TABLE films IN SHARE MODE;
SELECT id FROM films 
    WHERE name = 'Star Wars: Episode I - The Phantom Menace';
-- Do ROLLBACK if record was not returned
INSERT INTO films_user_comments VALUES 
    (_id_, 'GREAT! I was waiting for it for so long!');
COMMIT WORK;
</programlisting>
  </para>

  <para>
   Take a <literal>SHARE ROW EXCLUSIVE</> lock on a primary key table when going to perform
   a delete operation:

<programlisting>
BEGIN WORK;
LOCK TABLE films IN SHARE ROW EXCLUSIVE MODE;
DELETE FROM films_user_comments WHERE id IN
    (SELECT id FROM films WHERE rating &lt; 5);
DELETE FROM films WHERE rating &lt; 5;
COMMIT WORK;
</programlisting>
  </para>
 </refsect1>

 <refsect1>
  <title>Compatibility</title>

  <para>
   There is no <command>LOCK TABLE</command> in the SQL standard,
   which instead uses <command>SET TRANSACTION</command> to specify
   concurrency levels on transactions.  <productname>PostgreSQL</productname> supports that too;
   see <xref linkend="SQL-SET-TRANSACTION"
   endterm="SQL-SET-TRANSACTION-TITLE"> for details.
  </para>

  <para>
   Except for <literal>ACCESS SHARE</>, <literal>ACCESS EXCLUSIVE</>,
   and <literal>SHARE UPDATE EXCLUSIVE</> lock modes, the
   <productname>PostgreSQL</productname> lock modes and the
   <command>LOCK TABLE</command> syntax are compatible with those
   present in <productname>Oracle</productname>.
  </para>
 </refsect1>
</refentry>