lock.sgml 8.9 KB
Newer Older
1
<!--
2
$PostgreSQL: pgsql/doc/src/sgml/ref/lock.sgml,v 1.44 2004/11/15 06:32:15 neilc Exp $
3
PostgreSQL documentation
4 5
-->

6 7
<refentry id="SQL-LOCK">
 <refmeta>
8
  <refentrytitle id="sql-lock-title">LOCK</refentrytitle>
9 10
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>
11

12
 <refnamediv>
13 14
  <refname>LOCK</refname>
  <refpurpose>lock a table</refpurpose>
15
 </refnamediv>
16

Peter Eisentraut's avatar
Peter Eisentraut committed
17 18 19 20
 <indexterm zone="sql-lock">
  <primary>LOCK</primary>
 </indexterm>

21
 <refsynopsisdiv>
22
<synopsis>
23
LOCK [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ IN <replaceable class="PARAMETER">lockmode</replaceable> MODE ] [ NOWAIT ]
24 25 26

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

27 28 29
    ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
    | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE
</synopsis>
30
 </refsynopsisdiv>
31

32 33
 <refsect1>
  <title>Description</title>
Bruce Momjian's avatar
Bruce Momjian committed
34

35
  <para>
36 37 38 39
   <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
40
   cannot be acquired immediately, the command is aborted and an
41 42 43 44
   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.)
45
  </para>
Bruce Momjian's avatar
Bruce Momjian committed
46

47
  <para>
48 49 50
   When acquiring locks automatically for commands that reference
   tables, <productname>PostgreSQL</productname> always uses the least
   restrictive lock mode possible. <command>LOCK TABLE</command>
51
   provides for cases when you might need more restrictive locking.
52 53 54 55
   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
56
   table before querying. This will prevent concurrent data changes
57 58 59 60 61 62 63 64 65
   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.
Vadim B. Mikheev's avatar
Vadim B. Mikheev committed
66
  </para>
67

68
  <para>
69 70 71 72 73
   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
74
   <command>LOCK TABLE</> will still prevent concurrent writes &mdash; but it
75 76
   won't ensure that what the transaction reads corresponds to the
   latest committed values.
77
  </para>
Vadim B. Mikheev's avatar
Vadim B. Mikheev committed
78 79
  
  <para>
80 81 82 83 84 85 86 87
   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
88
   EXCLUSIVE</> mode when it holds <literal>SHARE</> mode &mdash; but not
89 90 91 92 93
   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.
Vadim B. Mikheev's avatar
Vadim B. Mikheev committed
94
  </para>
95

96
  <para>
97 98
   More information about the lock modes and locking strategies can be
   found in <xref linkend="explicit-locking">.
99
  </para>
100
 </refsect1>
101

102 103 104 105 106 107 108 109 110 111 112 113 114
 <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>
115 116 117 118
      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.
119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136
     </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>
137 138 139 140 141 142 143 144 145 146 147 148

   <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
      cannot be immediately acquired without waiting, the transaction
      is aborted.
     </para>
    </listitem>
   </varlistentry>
149 150
  </variablelist>
 </refsect1>
151

152 153
 <refsect1>
  <title>Notes</title>
154

155
   <para>
156
    <literal>LOCK TABLE ... IN ACCESS SHARE MODE</> requires <literal>SELECT</>
157 158
    privileges on the target table.  All other forms of <command>LOCK</>
    require <literal>UPDATE</> and/or <literal>DELETE</> privileges.
159
   </para>
160

Vadim B. Mikheev's avatar
Vadim B. Mikheev committed
161
   <para>
162 163 164 165 166 167
    <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.
168
   </para>
169

170
  <para>
171 172 173 174 175 176 177
   <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
178 179 180 181 182
   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"
   endterm="sql-for-update-title"> in the <command>SELECT</command>
   reference documentation.
183
  </para>
184
 </refsect1>
185
  
186 187
 <refsect1>
  <title>Examples</title>
Vadim B. Mikheev's avatar
Vadim B. Mikheev committed
188

189
  <para>
190
   Obtain a <literal>SHARE</> lock on a primary key table when going to perform
191 192
   inserts into a foreign key table:

193
<programlisting>
194 195 196 197 198 199 200 201
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;
202
</programlisting>
203
  </para>
Vadim B. Mikheev's avatar
Vadim B. Mikheev committed
204 205

  <para>
206
   Take a <literal>SHARE ROW EXCLUSIVE</> lock on a primary key table when going to perform
207 208
   a delete operation:

209
<programlisting>
210 211 212 213 214 215
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;
216
</programlisting>
Vadim B. Mikheev's avatar
Vadim B. Mikheev committed
217
  </para>
218
 </refsect1>
219

220 221
 <refsect1>
  <title>Compatibility</title>
222
	  
223 224 225
  <para>
   There is no <command>LOCK TABLE</command> in the SQL standard,
   which instead uses <command>SET TRANSACTION</command> to specify
226
   concurrency levels on transactions.  <productname>PostgreSQL</productname> supports that too;
227 228 229
   see <xref linkend="SQL-SET-TRANSACTION"
   endterm="SQL-SET-TRANSACTION-TITLE"> for details.
  </para>
230

231 232 233 234 235 236 237
  <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>
238
 </refsect1>
239
</refentry>
240 241 242 243 244 245 246 247 248 249 250 251 252

<!-- 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
253
sgml-local-catalogs:"/usr/lib/sgml/catalog"
254 255 256
sgml-local-ecat-files:nil
End:
-->