1108
1108
In addition to table-level locks, there are row-level locks, which
1109
can be exclusive or shared locks. An exclusive row-level lock on a
1110
specific row is automatically acquired when the row is updated or
1111
deleted. The lock is held until the transaction commits or rolls
1112
back, just like table-level locks. Row-level locks do
1113
not affect data querying; they block only <emphasis>writers to the same
1118
To acquire an exclusive row-level lock on a row without actually
1119
modifying the row, select the row with <command>SELECT FOR
1120
UPDATE</command>. Note that once the row-level lock is acquired,
1121
the transaction can update the row multiple times without
1126
To acquire a shared row-level lock on a row, select the row with
1127
<command>SELECT FOR SHARE</command>. A shared lock does not prevent
1128
other transactions from acquiring the same shared lock. However,
1129
no transaction is allowed to update, delete, or exclusively lock a
1130
row on which any other transaction holds a shared lock. Any attempt
1131
to do so will block until the shared lock(s) have been released.
1109
are listed as below with the contexts in which they are used
1110
automatically by <productname>PostgreSQL</productname>. See
1111
<xref linkend="row-lock-compatibility"> for a complete table of
1112
row-level lock conflicts. Note that a transaction can hold
1113
conflicting locks on the same row, even in different subtransactions;
1114
but other than that, two transactions can never hold conflicting locks
1115
on the same row. Row-level locks do not affect data querying; they
1116
block only <emphasis>writers and lockers</emphasis> to the same row.
1120
<title>Row-level Lock Modes</title>
1123
<literal>FOR UPDATE</literal>
1127
<literal>FOR UPDATE</literal> causes the rows retrieved by the
1128
<command>SELECT</command> statement to be locked as though for
1129
update. This prevents them from being locked, modified or deleted by
1130
other transactions until the current transaction ends. That is,
1131
other transactions that attempt <command>UPDATE</command>,
1132
<command>DELETE</command>,
1133
<command>SELECT FOR UPDATE</command>,
1134
<command>SELECT FOR NO KEY UPDATE</command>,
1135
<command>SELECT FOR SHARE</command> or
1136
<command>SELECT FOR KEY SHARE</command>
1137
of these rows will be blocked until the current transaction ends;
1138
conversely, <command>SELECT FOR UPDATE</command> will wait for a
1139
concurrent transaction that has run any of those commands on the
1141
and will then lock and return the updated row (or no row, if the
1142
row was deleted). Within a <literal>REPEATABLE READ</> or
1143
<literal>SERIALIZABLE</> transaction,
1144
however, an error will be thrown if a row to be locked has changed
1145
since the transaction started. For further discussion see
1146
<xref linkend="applevel-consistency">.
1149
The <literal>FOR UPDATE</> lock mode
1150
is also acquired by any <command>DELETE</> on a row, and also by an
1151
<command>UPDATE</> that modifies the values on certain columns. Currently,
1152
the set of columns considered for the <command>UPDATE</> case are those that
1153
have a unique index on them that can be used in a foreign key (so partial
1154
indexes and expressional indexes are not considered), but this may change
1162
<literal>FOR NO KEY UPDATE</literal>
1166
Behaves similarly to <literal>FOR UPDATE</>, except that the lock
1167
acquired is weaker: this lock will not block
1168
<literal>SELECT FOR KEY SHARE</> commands that attempt to acquire
1169
a lock on the same rows. This lock mode is also acquired by any
1170
<command>UPDATE</> that does not acquire a <literal>FOR UPDATE</> lock.
1177
<literal>FOR SHARE</literal>
1181
Behaves similarly to <literal>FOR NO KEY UPDATE</>, except that it
1182
acquires a shared lock rather than exclusive lock on each retrieved
1183
row. A shared lock blocks other transactions from performing
1184
<command>UPDATE</command>, <command>DELETE</command>,
1185
<command>SELECT FOR UPDATE</command> or
1186
<command>SELECT FOR NO KEY UPDATE</> on these rows, but it does not
1187
prevent them from performing <command>SELECT FOR SHARE</command> or
1188
<command>SELECT FOR KEY SHARE</command>.
1195
<literal>FOR KEY SHARE</literal>
1199
Behaves similarly to <literal>FOR SHARE</literal>, except that the
1200
lock is weaker: <literal>SELECT FOR UPDATE</> is blocked, but not
1201
<literal>SELECT FOR NO KEY UPDATE</>. A key-shared lock blocks
1202
other transactions from performing <command>DELETE</command> or
1203
any <command>UPDATE</command> that changes the key values, but not
1204
other <command>UPDATE</>, and neither does it prevent
1205
<command>SELECT FOR NO KEY UPDATE</>, <command>SELECT FOR SHARE</>,
1206
or <command>SELECT FOR KEY SHARE</>.
1135
1213
<productname>PostgreSQL</productname> doesn't remember any
1140
1218
will result in disk writes.
1221
<table tocentry="1" id="row-lock-compatibility">
1222
<title>Conflicting Row-level Locks</title>
1224
<colspec colnum="2" colname="lockst">
1225
<colspec colnum="5" colname="lockend">
1226
<spanspec namest="lockst" nameend="lockend" spanname="lockreq">
1229
<entry morerows="1">Requested Lock Mode</entry>
1230
<entry spanname="lockreq">Current Lock Mode</entry>
1233
<entry>FOR KEY SHARE</entry>
1234
<entry>FOR SHARE</entry>
1235
<entry>FOR NO KEY UPDATE</entry>
1236
<entry>FOR UPDATE</entry>
1241
<entry>FOR KEY SHARE</entry>
1242
<entry align="center"></entry>
1243
<entry align="center"></entry>
1244
<entry align="center"></entry>
1245
<entry align="center">X</entry>
1248
<entry>FOR SHARE</entry>
1249
<entry align="center"></entry>
1250
<entry align="center"></entry>
1251
<entry align="center">X</entry>
1252
<entry align="center">X</entry>
1255
<entry>FOR NO KEY UPDATE</entry>
1256
<entry align="center"></entry>
1257
<entry align="center">X</entry>
1258
<entry align="center">X</entry>
1259
<entry align="center">X</entry>
1262
<entry>FOR UPDATE</entry>
1263
<entry align="center">X</entry>
1264
<entry align="center">X</entry>
1265
<entry align="center">X</entry>
1266
<entry align="center">X</entry>
1273
<sect2 id="locking-pages">
1274
<title>Page-level Locks</title>
1144
1277
In addition to table and row locks, page-level share/exclusive locks are
1145
1278
used to control read/write access to table pages in the shared buffer
added
removed
doc/src/sgml/mvcc.sgml
