doc/src/sgml/ref/lock.sgml

   1 <!--
   2 doc/src/sgml/ref/lock.sgml
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="sql-lock">
   7  <indexterm zone="sql-lock">
   8   <primary>LOCK</primary>
   9  </indexterm>
  10
  11  <refmeta>
  12   <refentrytitle>LOCK</refentrytitle>
  13   <manvolnum>7</manvolnum>
  14   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  15  </refmeta>
  16
  17  <refnamediv>
  18   <refname>LOCK</refname>
  19   <refpurpose>lock a table</refpurpose>
  20  </refnamediv>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 LOCK [ TABLE ] [ ONLY ] <replaceable class="parameter">name</replaceable> [ * ] [, ...] [ IN <replaceable class="parameter">lockmode</replaceable> MODE ] [ NOWAIT ]
  25
  26 <phrase>where <replaceable class="parameter">lockmode</replaceable> is one of:</phrase>
  27
  28     ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
  29     | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE
  30 </synopsis>
  31  </refsynopsisdiv>
  32
  33  <refsect1>
  34   <title>Description</title>
  35
  36   <para>
  37    <command>LOCK TABLE</command> obtains a table-level lock, waiting
  38    if necessary for any conflicting locks to be released.  If
  39    <literal>NOWAIT</literal> is specified, <command>LOCK
  40    TABLE</command> does not wait to acquire the desired lock: if it
  41    cannot be acquired immediately, the command is aborted and an
  42    error is emitted.  Once obtained, the lock is held for the
  43    remainder of the current transaction.  (There is no <command>UNLOCK
  44    TABLE</command> command; locks are always released at transaction
  45    end.)
  46   </para>
  47
  48   <para>
  49    When a view is locked, all relations appearing in the view definition
  50    query are also locked recursively with the same lock mode.
  51   </para>
  52
  53   <para>
  54    When acquiring locks automatically for commands that reference
  55    tables, <productname>PostgreSQL</productname> always uses the least
  56    restrictive lock mode possible. <command>LOCK TABLE</command>
  57    provides for cases when you might need more restrictive locking.
  58    For example, suppose an application runs a transaction at the
  59    <literal>READ COMMITTED</literal> isolation level and needs to ensure that
  60    data in a table remains stable for the duration of the transaction.
  61    To achieve this you could obtain <literal>SHARE</literal> lock mode over the
  62    table before querying. This will prevent concurrent data changes
  63    and ensure subsequent reads of the table see a stable view of
  64    committed data, because <literal>SHARE</literal> lock mode conflicts with
  65    the <literal>ROW EXCLUSIVE</literal> lock acquired by writers, and your
  66    <command>LOCK TABLE <replaceable
  67    class="parameter">name</replaceable> IN SHARE MODE</command>
  68    statement will wait until any concurrent holders of <literal>ROW
  69    EXCLUSIVE</literal> mode locks commit or roll back. Thus, once you
  70    obtain the lock, there are no uncommitted writes outstanding;
  71    furthermore none can begin until you release the lock.
  72   </para>
  73
  74   <para>
  75    To achieve a similar effect when running a transaction at the
  76    <literal>REPEATABLE READ</literal> or <literal>SERIALIZABLE</literal>
  77    isolation level, you have to execute the <command>LOCK TABLE</command> statement
  78    before executing any <command>SELECT</command> or data modification statement.
  79    A <literal>REPEATABLE READ</literal> or <literal>SERIALIZABLE</literal> transaction's
  80    view of data will be frozen when its first
  81    <command>SELECT</command> or data modification statement begins.  A <command>LOCK
  82    TABLE</command> later in the transaction will still prevent concurrent writes
  83    &mdash; but it won't ensure that what the transaction reads corresponds to
  84    the latest committed values.
  85   </para>
  86
  87   <para>
  88    If a transaction of this sort is going to change the data in the
  89    table, then it should use <literal>SHARE ROW EXCLUSIVE</literal> lock mode
  90    instead of <literal>SHARE</literal> mode.  This ensures that only one
  91    transaction of this type runs at a time.  Without this, a deadlock
  92    is possible: two transactions might both acquire <literal>SHARE</literal>
  93    mode, and then be unable to also acquire <literal>ROW EXCLUSIVE</literal>
  94    mode to actually perform their updates.  (Note that a transaction's
  95    own locks never conflict, so a transaction can acquire <literal>ROW
  96    EXCLUSIVE</literal> mode when it holds <literal>SHARE</literal> mode &mdash; but not
  97    if anyone else holds <literal>SHARE</literal> mode.)  To avoid deadlocks,
  98    make sure all transactions acquire locks on the same objects in the
  99    same order, and if multiple lock modes are involved for a single
 100    object, then transactions should always acquire the most
 101    restrictive mode first.
 102   </para>
 103
 104   <para>
 105    More information about the lock modes and locking strategies can be
 106    found in <xref linkend="explicit-locking"/>.
 107   </para>
 108  </refsect1>
 109
 110  <refsect1>
 111   <title>Parameters</title>
 112
 113   <variablelist>
 114    <varlistentry>
 115     <term><replaceable class="parameter">name</replaceable></term>
 116     <listitem>
 117      <para>
 118       The name (optionally schema-qualified) of an existing table to
 119       lock. If <literal>ONLY</literal> is specified before the table name, only that
 120       table is locked. If <literal>ONLY</literal> is not specified, the table and all
 121       its descendant tables (if any) are locked.  Optionally, <literal>*</literal>
 122       can be specified after the table name to explicitly indicate that
 123       descendant tables are included.
 124      </para>
 125
 126      <para>
 127       The command <literal>LOCK TABLE a, b;</literal> is equivalent to
 128       <literal>LOCK TABLE a; LOCK TABLE b;</literal>. The tables are locked
 129       one-by-one in the order specified in the <command>LOCK
 130       TABLE</command> command.
 131      </para>
 132     </listitem>
 133    </varlistentry>
 134
 135    <varlistentry>
 136     <term><replaceable class="parameter">lockmode</replaceable></term>
 137     <listitem>
 138      <para>
 139       The lock mode specifies which locks this lock conflicts with.
 140       Lock modes are described in <xref linkend="explicit-locking"/>.
 141      </para>
 142
 143      <para>
 144       If no lock mode is specified, then <literal>ACCESS
 145       EXCLUSIVE</literal>, the most restrictive mode, is used.
 146      </para>
 147     </listitem>
 148    </varlistentry>
 149
 150    <varlistentry>
 151     <term><literal>NOWAIT</literal></term>
 152     <listitem>
 153      <para>
 154       Specifies that <command>LOCK TABLE</command> should not wait for
 155       any conflicting locks to be released: if the specified lock(s)
 156       cannot be acquired immediately without waiting, the transaction
 157       is aborted.
 158      </para>
 159     </listitem>
 160    </varlistentry>
 161   </variablelist>
 162  </refsect1>
 163
 164  <refsect1>
 165   <title>Notes</title>
 166
 167    <para>
 168     To lock a table, the user must have the right privilege for the specified
 169     <replaceable class="parameter">lockmode</replaceable>, or be the table's
 170     owner or a superuser.  If the user has
 171     <literal>UPDATE</literal>, <literal>DELETE</literal>, or
 172     <literal>TRUNCATE</literal> privileges on the table, any <replaceable
 173     class="parameter">lockmode</replaceable> is permitted. If the user has
 174     <literal>INSERT</literal> privileges on the table, <literal>ROW EXCLUSIVE
 175     MODE</literal> (or a less-conflicting mode as described in <xref
 176     linkend="explicit-locking"/>) is permitted. If a user has
 177     <literal>SELECT</literal> privileges on the table, <literal>ACCESS SHARE
 178     MODE</literal> is permitted.
 179    </para>
 180
 181    <para>
 182     The user performing the lock on the view must have the corresponding
 183     privilege on the view.  In addition, by default, the view's owner must
 184     have the relevant privileges on the underlying base relations, whereas the
 185     user performing the lock does not need any permissions on the underlying
 186     base relations.  However, if the view has
 187     <literal>security_invoker</literal> set to <literal>true</literal>
 188     (see <link linkend="sql-createview"><command>CREATE VIEW</command></link>),
 189     the user performing the lock, rather than the view owner, must have the
 190     relevant privileges on the underlying base relations.
 191    </para>
 192
 193    <para>
 194     <command>LOCK TABLE</command> is useless outside a transaction block: the lock
 195     would remain held only to the completion of the statement.  Therefore
 196     <productname>PostgreSQL</productname> reports an error if <command>LOCK</command>
 197     is used outside a transaction block.
 198     Use
 199     <link linkend="sql-begin"><command>BEGIN</command></link> and
 200     <link linkend="sql-commit"><command>COMMIT</command></link>
 201     (or <link linkend="sql-rollback"><command>ROLLBACK</command></link>)
 202     to define a transaction block.
 203    </para>
 204
 205   <para>
 206    <command>LOCK TABLE</command> only deals with table-level locks, and so
 207    the mode names involving <literal>ROW</literal> are all misnomers.  These
 208    mode names should generally be read as indicating the intention of
 209    the user to acquire row-level locks within the locked table.  Also,
 210    <literal>ROW EXCLUSIVE</literal> mode is a shareable table lock.  Keep in
 211    mind that all the lock modes have identical semantics so far as
 212    <command>LOCK TABLE</command> is concerned, differing only in the rules
 213    about which modes conflict with which. For information on how to
 214    acquire an actual row-level lock, see <xref linkend="locking-rows"/>
 215    and <xref linkend="sql-for-update-share"/>
 216    in the <xref linkend="sql-select"/> documentation.
 217   </para>
 218  </refsect1>
 219
 220  <refsect1>
 221   <title>Examples</title>
 222
 223   <para>
 224    Obtain a <literal>SHARE</literal> lock on a primary key table when going to perform
 225    inserts into a foreign key table:
 226
 227 <programlisting>
 228 BEGIN WORK;
 229 LOCK TABLE films IN SHARE MODE;
 230 SELECT id FROM films
 231     WHERE name = 'Star Wars: Episode I - The Phantom Menace';
 232 -- Do ROLLBACK if record was not returned
 233 INSERT INTO films_user_comments VALUES
 234     (_id_, 'GREAT! I was waiting for it for so long!');
 235 COMMIT WORK;
 236 </programlisting>
 237   </para>
 238
 239   <para>
 240    Take a <literal>SHARE ROW EXCLUSIVE</literal> lock on a primary key table when going to perform
 241    a delete operation:
 242
 243 <programlisting>
 244 BEGIN WORK;
 245 LOCK TABLE films IN SHARE ROW EXCLUSIVE MODE;
 246 DELETE FROM films_user_comments WHERE id IN
 247     (SELECT id FROM films WHERE rating &lt; 5);
 248 DELETE FROM films WHERE rating &lt; 5;
 249 COMMIT WORK;
 250 </programlisting></para>
 251  </refsect1>
 252
 253  <refsect1>
 254   <title>Compatibility</title>
 255
 256   <para>
 257    There is no <command>LOCK TABLE</command> in the SQL standard,
 258    which instead uses <command>SET TRANSACTION</command> to specify
 259    concurrency levels on transactions.  <productname>PostgreSQL</productname> supports that too;
 260    see <xref linkend="sql-set-transaction"/> for details.
 261   </para>
 262
 263   <para>
 264    Except for <literal>ACCESS SHARE</literal>, <literal>ACCESS EXCLUSIVE</literal>,
 265    and <literal>SHARE UPDATE EXCLUSIVE</literal> lock modes, the
 266    <productname>PostgreSQL</productname> lock modes and the
 267    <command>LOCK TABLE</command> syntax are compatible with those
 268    present in <productname>Oracle</productname>.
 269   </para>
 270  </refsect1>
 271 </refentry>