| <!-- |
| $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 |
| — 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 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 < 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> |