2 $PostgreSQL: pgsql/doc/src/sgml/ref/lock.sgml,v 1.44 2004/11/15 06:32:15 neilc Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-LOCK">
8 <refentrytitle id="sql-lock-title">LOCK</refentrytitle>
9 <refmiscinfo>SQL - Language Statements</refmiscinfo>
13 <refname>LOCK</refname>
14 <refpurpose>lock a table</refpurpose>
17 <indexterm zone="sql-lock">
18 <primary>LOCK</primary>
23 LOCK [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ IN <replaceable class="PARAMETER">lockmode</replaceable> MODE ] [ NOWAIT ]
25 where <replaceable class="PARAMETER">lockmode</replaceable> is one of:
27 ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
28 | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE
33 <title>Description</title>
36 <command>LOCK TABLE</command> obtains a table-level lock, waiting
37 if necessary for any conflicting locks to be released. If
38 <literal>NOWAIT</literal> is specified, <command>LOCK
39 TABLE</command> does not wait to acquire the desired lock: if it
40 cannot be acquired immediately, the command is aborted and an
41 error is emitted. Once obtained, the lock is held for the
42 remainder of the current transaction. (There is no <command>UNLOCK
43 TABLE</command> command; locks are always released at transaction
48 When acquiring locks automatically for commands that reference
49 tables, <productname>PostgreSQL</productname> always uses the least
50 restrictive lock mode possible. <command>LOCK TABLE</command>
51 provides for cases when you might need more restrictive locking.
52 For example, suppose an application runs a transaction at the
53 isolation level read committed and needs to ensure that data in a
54 table remains stable for the duration of the transaction. To
55 achieve this you could obtain <literal>SHARE</> lock mode over the
56 table before querying. This will prevent concurrent data changes
57 and ensure subsequent reads of the table see a stable view of
58 committed data, because <literal>SHARE</> lock mode conflicts with
59 the <literal>ROW EXCLUSIVE</> lock acquired by writers, and your
60 <command>LOCK TABLE <replaceable
61 class="PARAMETER">name</replaceable> IN SHARE MODE</command>
62 statement will wait until any concurrent holders of <literal>ROW
63 EXCLUSIVE</literal> mode locks commit or roll back. Thus, once you
64 obtain the lock, there are no uncommitted writes outstanding;
65 furthermore none can begin until you release the lock.
69 To achieve a similar effect when running a transaction at the
70 isolation level serializable, you have to execute the <command>LOCK
71 TABLE</> statement before executing any data modification
72 statement. A serializable transaction's view of data will be
73 frozen when its first data modification statement begins. A later
74 <command>LOCK TABLE</> will still prevent concurrent writes — but it
75 won't ensure that what the transaction reads corresponds to the
76 latest committed values.
80 If a transaction of this sort is going to change the data in the
81 table, then it should use <literal>SHARE ROW EXCLUSIVE</> lock mode
82 instead of <literal>SHARE</> mode. This ensures that only one
83 transaction of this type runs at a time. Without this, a deadlock
84 is possible: two transactions might both acquire <literal>SHARE</>
85 mode, and then be unable to also acquire <literal>ROW EXCLUSIVE</>
86 mode to actually perform their updates. (Note that a transaction's
87 own locks never conflict, so a transaction can acquire <literal>ROW
88 EXCLUSIVE</> mode when it holds <literal>SHARE</> mode — but not
89 if anyone else holds <literal>SHARE</> mode.) To avoid deadlocks,
90 make sure all transactions acquire locks on the same objects in the
91 same order, and if multiple lock modes are involved for a single
92 object, then transactions should always acquire the most
93 restrictive mode first.
97 More information about the lock modes and locking strategies can be
98 found in <xref linkend="explicit-locking">.
103 <title>Parameters</title>
107 <term><replaceable class="PARAMETER">name</replaceable></term>
110 The name (optionally schema-qualified) of an existing table to
115 The command <literal>LOCK TABLE a, b;</> is equivalent to
116 <literal>LOCK TABLE a; LOCK TABLE b;</>. The tables are locked
117 one-by-one in the order specified in the <command>LOCK
118 TABLE</command> command.
124 <term><replaceable class="parameter">lockmode</replaceable></term>
127 The lock mode specifies which locks this lock conflicts with.
128 Lock modes are described in <xref linkend="explicit-locking">.
132 If no lock mode is specified, then <literal>ACCESS
133 EXCLUSIVE</literal>, the most restrictive mode, is used.
139 <term><literal>NOWAIT</literal></term>
142 Specifies that <command>LOCK TABLE</command> should not wait for
143 any conflicting locks to be released: if the specified lock
144 cannot be immediately acquired without waiting, the transaction
156 <literal>LOCK TABLE ... IN ACCESS SHARE MODE</> requires <literal>SELECT</>
157 privileges on the target table. All other forms of <command>LOCK</>
158 require <literal>UPDATE</> and/or <literal>DELETE</> privileges.
162 <command>LOCK TABLE</command> is useful only inside a transaction
163 block (<command>BEGIN</>/<command>COMMIT</> pair), since the lock
164 is dropped as soon as the transaction ends. A <command>LOCK
165 TABLE</> command appearing outside any transaction block forms a
166 self-contained transaction, so the lock will be dropped as soon as
171 <command>LOCK TABLE</> only deals with table-level locks, and so
172 the mode names involving <literal>ROW</> are all misnomers. These
173 mode names should generally be read as indicating the intention of
174 the user to acquire row-level locks within the locked table. Also,
175 <literal>ROW EXCLUSIVE</> mode is a sharable table lock. Keep in
176 mind that all the lock modes have identical semantics so far as
177 <command>LOCK TABLE</> is concerned, differing only in the rules
178 about which modes conflict with which. For information on how to
179 acquire an actual row-level lock, see <xref linkend="locking-rows">
180 and the <xref linkend="sql-for-update"
181 endterm="sql-for-update-title"> in the <command>SELECT</command>
182 reference documentation.
187 <title>Examples</title>
190 Obtain a <literal>SHARE</> lock on a primary key table when going to perform
191 inserts into a foreign key table:
195 LOCK TABLE films IN SHARE MODE;
197 WHERE name = 'Star Wars: Episode I - The Phantom Menace';
198 -- Do ROLLBACK if record was not returned
199 INSERT INTO films_user_comments VALUES
200 (_id_, 'GREAT! I was waiting for it for so long!');
206 Take a <literal>SHARE ROW EXCLUSIVE</> lock on a primary key table when going to perform
211 LOCK TABLE films IN SHARE ROW EXCLUSIVE MODE;
212 DELETE FROM films_user_comments WHERE id IN
213 (SELECT id FROM films WHERE rating < 5);
214 DELETE FROM films WHERE rating < 5;
221 <title>Compatibility</title>
224 There is no <command>LOCK TABLE</command> in the SQL standard,
225 which instead uses <command>SET TRANSACTION</command> to specify
226 concurrency levels on transactions. <productname>PostgreSQL</productname> supports that too;
227 see <xref linkend="SQL-SET-TRANSACTION"
228 endterm="SQL-SET-TRANSACTION-TITLE"> for details.
232 Except for <literal>ACCESS SHARE</>, <literal>ACCESS EXCLUSIVE</>,
233 and <literal>SHARE UPDATE EXCLUSIVE</> lock modes, the
234 <productname>PostgreSQL</productname> lock modes and the
235 <command>LOCK TABLE</command> syntax are compatible with those
236 present in <productname>Oracle</productname>.
241 <!-- Keep this comment at the end of the file
246 sgml-minimize-attributes:nil
247 sgml-always-quote-attributes:t
250 sgml-parent-document:nil
251 sgml-default-dtd-file:"../reference.ced"
252 sgml-exposed-tags:nil
253 sgml-local-catalogs:"/usr/lib/sgml/catalog"
254 sgml-local-ecat-files:nil