lock.sgml

PostgreSQL7.4.6 for Linux

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ref/lock.sgml,v 1.38 2003/09/11 21:42:20 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 ]

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.  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
   isolation level read committed 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
   isolation level serializable, you have to execute the <command>LOCK
   TABLE</> statement before executing any data modification
   statement.  A serializable transaction's view of data will be
   frozen when its first data modification statement begins.  A later
   <command>LOCK TABLE</> will still prevent concurrent writes --- 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 --- 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 a, b;</> is equivalent to
      <literal>LOCK a; LOCK b;</>. The tables are locked one-by-one in
      the order specified in the <command>LOCK</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>
  </variablelist>
 </refsect1>

 <refsect1>
  <title>Notes</title>

   <para>
    <literal>LOCK ... 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</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</> 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.
  </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 < 5);
DELETE FROM films WHERE rating < 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>

<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->