<!--
doc/src/sgml/ref/lock.sgml
PostgreSQL documentation
-->

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

 <refmeta>
  <refentrytitle>LOCK</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

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

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

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

    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 a view is locked, all relations appearing in the view definition
   query are also locked recursively with the same lock mode.
  </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
   <literal>READ COMMITTED</literal> 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</literal> 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</literal> lock mode conflicts with
   the <literal>ROW EXCLUSIVE</literal> 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
   <literal>REPEATABLE READ</literal> or <literal>SERIALIZABLE</literal>
   isolation level, you have to execute the <command>LOCK TABLE</command> statement
   before executing any <command>SELECT</command> or data modification statement.
   A <literal>REPEATABLE READ</literal> or <literal>SERIALIZABLE</literal> transaction's
   view of data will be frozen when its first
   <command>SELECT</command> or data modification statement begins.  A <command>LOCK
   TABLE</command> 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</literal> lock mode
   instead of <literal>SHARE</literal> 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</literal>
   mode, and then be unable to also acquire <literal>ROW EXCLUSIVE</literal>