Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit35fed51

Browse files
committed
Tweak row-level locking documentation
Move the meat of locking levels to mvcc.sgml, leaving only a link to itin the SELECT reference page.Michael Paquier, with some tweaks by Álvaro
1 parentc0828b7 commit35fed51

File tree

2 files changed

+153
-80
lines changed

2 files changed

+153
-80
lines changed

‎doc/src/sgml/mvcc.sgml

Lines changed: 151 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -1106,30 +1106,108 @@ ERROR: could not serialize access due to read/write dependencies among transact
11061106

11071107
<para>
11081108
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
1114-
row</emphasis>.
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.
11151117
</para>
11161118

1117-
<para>
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
1122-
fear of conflicts.
1123-
</para>
1119+
<variablelist>
1120+
<title>Row-level Lock Modes</title>
1121+
<varlistentry>
1122+
<term>
1123+
<literal>FOR UPDATE</literal>
1124+
</term>
1125+
<listitem>
1126+
<para>
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
1140+
same row,
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">.
1147+
</para>
1148+
<para>
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
1155+
in the future.
1156+
</para>
1157+
</listitem>
1158+
</varlistentry>
11241159

1125-
<para>
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.
1132-
</para>
1160+
<varlistentry>
1161+
<term>
1162+
<literal>FOR NO KEY UPDATE</literal>
1163+
</term>
1164+
<listitem>
1165+
<para>
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.
1171+
</para>
1172+
</listitem>
1173+
</varlistentry>
1174+
1175+
<varlistentry>
1176+
<term>
1177+
<literal>FOR SHARE</literal>
1178+
</term>
1179+
<listitem>
1180+
<para>
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>.
1189+
</para>
1190+
</listitem>
1191+
</varlistentry>
1192+
1193+
<varlistentry>
1194+
<term>
1195+
<literal>FOR KEY SHARE</literal>
1196+
</term>
1197+
<listitem>
1198+
<para>
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</>.
1207+
</para>
1208+
</listitem>
1209+
</varlistentry>
1210+
</variablelist>
11331211

11341212
<para>
11351213
<productname>PostgreSQL</productname> doesn't remember any
@@ -1139,11 +1217,62 @@ ERROR: could not serialize access due to read/write dependencies among transact
11391217
UPDATE</command> modifies selected rows to mark them locked, and so
11401218
will result in disk writes.
11411219
</para>
1220+
1221+
<table tocentry="1" id="row-lock-compatibility">
1222+
<title>Conflicting Row-level Locks</title>
1223+
<tgroup cols="5">
1224+
<colspec colnum="2" colname="lockst">
1225+
<colspec colnum="5" colname="lockend">
1226+
<spanspec namest="lockst" nameend="lockend" spanname="lockreq">
1227+
<thead>
1228+
<row>
1229+
<entry morerows="1">Requested Lock Mode</entry>
1230+
<entry spanname="lockreq">Current Lock Mode</entry>
1231+
</row>
1232+
<row>
1233+
<entry>FOR KEY SHARE</entry>
1234+
<entry>FOR SHARE</entry>
1235+
<entry>FOR NO KEY UPDATE</entry>
1236+
<entry>FOR UPDATE</entry>
1237+
</row>
1238+
</thead>
1239+
<tbody>
1240+
<row>
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>
1246+
</row>
1247+
<row>
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>
1253+
</row>
1254+
<row>
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>
1260+
</row>
1261+
<row>
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>
1267+
</row>
1268+
</tbody>
1269+
</tgroup>
1270+
</table>
11421271
</sect2>
11431272

11441273
<sect2 id="locking-pages">
11451274
<title>Page-level Locks</title>
1146-
1275+
11471276
<para>
11481277
In addition to table and row locks, page-level share/exclusive locks are
11491278
used to control read/write access to table pages in the shared buffer

‎doc/src/sgml/ref/select.sgml

Lines changed: 2 additions & 58 deletions
Original file line numberDiff line numberDiff line change
@@ -1298,64 +1298,8 @@ KEY SHARE
12981298
</para>
12991299

13001300
<para>
1301-
<literal>FOR UPDATE</literal> causes the rows retrieved by the
1302-
<command>SELECT</command> statement to be locked as though for
1303-
update. This prevents them from being modified or deleted by
1304-
other transactions until the current transaction ends. That is,
1305-
other transactions that attempt <command>UPDATE</command>,
1306-
<command>DELETE</command>,
1307-
<command>SELECT FOR UPDATE</command>,
1308-
<command>SELECT FOR NO KEY UPDATE</command>,
1309-
<command>SELECT FOR SHARE</command> or
1310-
<command>SELECT FOR KEY SHARE</command>
1311-
of these rows will be blocked until the current transaction ends.
1312-
The <literal>FOR UPDATE</> lock mode
1313-
is also acquired by any <command>DELETE</> on a row, and also by an
1314-
<command>UPDATE</> that modifies the values on certain columns. Currently,
1315-
the set of columns considered for the <command>UPDATE</> case are those that
1316-
have a unique index on them that can be used in a foreign key (so partial
1317-
indexes and expressional indexes are not considered), but this may change
1318-
in the future.
1319-
Also, if an <command>UPDATE</command>, <command>DELETE</command>,
1320-
or <command>SELECT FOR UPDATE</command> from another transaction
1321-
has already locked a selected row or rows, <command>SELECT FOR
1322-
UPDATE</command> will wait for the other transaction to complete,
1323-
and will then lock and return the updated row (or no row, if the
1324-
row was deleted). Within a <literal>REPEATABLE READ</> or <literal>SERIALIZABLE</> transaction,
1325-
however, an error will be thrown if a row to be locked has changed
1326-
since the transaction started. For further discussion see <xref
1327-
linkend="mvcc">.
1328-
</para>
1329-
1330-
<para>
1331-
<literal>FOR NO KEY UPDATE</> behaves similarly, except that the lock
1332-
acquired is weaker: this lock will not block
1333-
<literal>SELECT FOR KEY SHARE</> commands that attempt to acquire
1334-
a lock on the same rows. This lock mode is also acquired by any
1335-
<command>UPDATE</> that does not acquire a <literal>FOR UPDATE</> lock.
1336-
</para>
1337-
1338-
<para>
1339-
<literal>FOR SHARE</literal> behaves similarly, except that it
1340-
acquires a shared rather than exclusive lock on each retrieved
1341-
row. A shared lock blocks other transactions from performing
1342-
<command>UPDATE</command>, <command>DELETE</command>, <command>SELECT
1343-
FOR UPDATE</command> or <command>SELECT FOR NO KEY UPDATE</>
1344-
on these rows, but it does not prevent them
1345-
from performing <command>SELECT FOR SHARE</command> or
1346-
<command>SELECT FOR KEY SHARE</command>.
1347-
</para>
1348-
1349-
<para>
1350-
<literal>FOR KEY SHARE</> behaves similarly to <literal>FOR SHARE</literal>,
1351-
except that the lock
1352-
is weaker: <literal>SELECT FOR UPDATE</> is blocked, but
1353-
not <literal>SELECT FOR NO KEY UPDATE</>. A key-shared
1354-
lock blocks other transactions from performing <command>DELETE</command>
1355-
or any <command>UPDATE</command> that changes the key values, but not
1356-
other <command>UPDATE</>, and neither does it prevent
1357-
<command>SELECT FOR NO KEY UPDATE</>, <command>SELECT FOR SHARE</>, or
1358-
<command>SELECT FOR KEY SHARE</>.
1301+
For more information on each row-level lock mode, refer to
1302+
<xref linkend="locking-rows">.
13591303
</para>
13601304

13611305
<para>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp