| 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 |
— 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 — 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>. |
| 170 |
If the user has <literal>MAINTAIN</literal>, |
| 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 < 5); |
| 248 |
DELETE FROM films WHERE rating < 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> |