Skip to content
Snippets Groups Projects
Select Git revision
  • benchmark-tools
  • postgres-lambda
  • master default
  • REL9_4_25
  • REL9_5_20
  • REL9_6_16
  • REL_10_11
  • REL_11_6
  • REL_12_1
  • REL_12_0
  • REL_12_RC1
  • REL_12_BETA4
  • REL9_4_24
  • REL9_5_19
  • REL9_6_15
  • REL_10_10
  • REL_11_5
  • REL_12_BETA3
  • REL9_4_23
  • REL9_5_18
  • REL9_6_14
  • REL_10_9
  • REL_11_4
23 results

lock.sgml

Blame
  • lock.sgml 8.99 KiB
    <!--
    $PostgreSQL: pgsql/doc/src/sgml/ref/lock.sgml,v 1.52 2009/01/12 08:54:25 petere Exp $
    PostgreSQL documentation
    -->
    
    <refentry id="SQL-LOCK">
     <refmeta>
      <refentrytitle id="sql-lock-title">LOCK</refentrytitle>
      <manvolnum>7</manvolnum>
      <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 ] [ ONLY ] <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.  If <literal>ONLY</> is specified, only that table is
          locked.  If <literal>ONLY</> is not specified, the table and all
          its descendant tables (if any) are locked.
         </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 at least one of <literal>UPDATE</>, <literal>DELETE</>, or
        <literal>TRUNCATE</> privileges.
       </para>
    
       <para>
        <command>LOCK TABLE</> is useless outside a transaction block: the lock
        would remain held only to the completion of the statement.  Therefore
        <productname>PostgreSQL</productname> reports an error if <command>LOCK</>
        is used outside a transaction block.
        Use
        <xref linkend="sql-begin" endterm="sql-begin-title"> and
        <xref linkend="sql-commit" endterm="sql-commit-title">
        (or <xref linkend="sql-rollback" endterm="sql-rollback-title">)
        to define a transaction block.
       </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>