Hi all,
Currently all the row-level lock modes are described in the page for
SELECT query:
http://www.postgresql.org/docs/devel/static/explicit-locking.html#LOCKING-ROWS
However, after browsing the documentation, I noticed in the page
describing all the explicit locks of the system that there is a
portion dedicated to row-level locks and this section is not
mentioning at all FOR KEY SHARE and FOR NO KEY UPDATE. It seems that
this is something rather misleading for the user:
http://www.postgresql.org/docs/devel/static/explicit-locking.html#LOCKING-ROWS
Attached is a patch that refactors the whole and improves the documentation:
- Addition of a table showing the conflicts between each lock
- Moved description of each row-level lock mode to the explicit locking page
- Addition of a link in SELECT portion to redirect the user to the
explicit locking page
Regards,
--
Michael
From 974b360cc8bfdade607154c94d0a78f5cf466e0a Mon Sep 17 00:00:00 2001
From: Michael Paquier <mich...@otacoo.com>
Date: Fri, 10 Oct 2014 22:10:10 +0900
Subject: [PATCH] Refactor documentation of row-level locking
All the details about each row-level lock mode was referenced in
details in the page dedicated to SELECT query, what seems rather
incorrect because there is a section in MVCC section describing in
details all the locks that can be used in the system. Note that this
portion has not been updated for the implementation of FOR KEY SHARE
and FOR NO KEY UPDATE while it should have been the case, making the
information provided to user inconsistent and misleading. This patch
refactors the whole, adding a link in SELECT to redirect the user to
the page describing all the explicit locks of the system.
---
doc/src/sgml/mvcc.sgml | 172 +++++++++++++++++++++++++++++++++++++------
doc/src/sgml/ref/select.sgml | 60 +--------------
2 files changed, 152 insertions(+), 80 deletions(-)
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
index cd55be8..478279d 100644
--- a/doc/src/sgml/mvcc.sgml
+++ b/doc/src/sgml/mvcc.sgml
@@ -1106,30 +1106,107 @@ ERROR: could not serialize access due to read/write dependencies among transact
<para>
In addition to table-level locks, there are row-level locks, which
- can be exclusive or shared locks. An exclusive row-level lock on a
- specific row is automatically acquired when the row is updated or
- deleted. The lock is held until the transaction commits or rolls
- back, just like table-level locks. Row-level locks do
- not affect data querying; they block only <emphasis>writers to the same
- row</emphasis>.
+ are listed as below with the contexts in which they are used
+ automatically by <productname>PostgreSQL</productname>. The main
+ differences between one lock mode and another is the set of lock modes
+ with each conflicts (see <xref linkend="row-lock-compatibility">).
+ Two transactions cannot hold locks of conflicting modes on the same rows
+ of the same table at the same time. Also, a transaction never conflicts
+ with itself. Row-level locks do not affect data querying; they block only
+ <emphasis>writers to the same row</emphasis>.
</para>
- <para>
- To acquire an exclusive row-level lock on a row without actually
- modifying the row, select the row with <command>SELECT FOR
- UPDATE</command>. Note that once the row-level lock is acquired,
- the transaction can update the row multiple times without
- fear of conflicts.
- </para>
+ <variablelist>
+ <title>Row-level Lock Modes</title>
+ <varlistentry>
+ <term>
+ <literal>FOR UPDATE</literal>
+ </term>
+ <listitem>
+ <para>
+ <literal>FOR UPDATE</literal> causes the rows retrieved by the
+ <command>SELECT</command> statement to be locked as though for
+ update. This prevents them from being modified or deleted by
+ other transactions until the current transaction ends. That is,
+ other transactions that attempt <command>UPDATE</command>,
+ <command>DELETE</command>,
+ <command>SELECT FOR UPDATE</command>,
+ <command>SELECT FOR NO KEY UPDATE</command>,
+ <command>SELECT FOR SHARE</command> or
+ <command>SELECT FOR KEY SHARE</command>
+ of these rows will be blocked until the current transaction ends.
+ The <literal>FOR UPDATE</> lock mode
+ is also acquired by any <command>DELETE</> on a row, and also by an
+ <command>UPDATE</> that modifies the values on certain columns. Currently,
+ the set of columns considered for the <command>UPDATE</> case are those that
+ have a unique index on them that can be used in a foreign key (so partial
+ indexes and expressional indexes are not considered), but this may change
+ in the future.
+ Also, if an <command>UPDATE</command>, <command>DELETE</command>,
+ or <command>SELECT FOR UPDATE</command> from another transaction
+ has already locked a selected row or rows, <command>SELECT FOR
+ UPDATE</command> will wait for the other transaction to complete,
+ and will then lock and return the updated row (or no row, if the
+ row was deleted). Within a <literal>REPEATABLE READ</> or
+ <literal>SERIALIZABLE</> transaction,
+ however, an error will be thrown if a row to be locked has changed
+ since the transaction started. For further discussion see
+ <xref linkend="mvcc">.
+ </para>
+ </listitem>
+ </varlistentry>
- <para>
- To acquire a shared row-level lock on a row, select the row with
- <command>SELECT FOR SHARE</command>. A shared lock does not prevent
- other transactions from acquiring the same shared lock. However,
- no transaction is allowed to update, delete, or exclusively lock a
- row on which any other transaction holds a shared lock. Any attempt
- to do so will block until the shared lock(s) have been released.
- </para>
+ <varlistentry>
+ <term>
+ <literal>FOR NO KEY UPDATE</literal>
+ </term>
+ <listitem>
+ <para>
+ Behaves similarly to <literal>FOR UPDATE</>, except that the lock
+ acquired is weaker: this lock will not block
+ <literal>SELECT FOR KEY SHARE</> commands that attempt to acquire
+ a lock on the same rows. This lock mode is also acquired by any
+ <command>UPDATE</> that does not acquire a <literal>FOR UPDATE</> lock.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>FOR SHARE</literal>
+ </term>
+ <listitem>
+ <para>
+ Behaves similarly to <literal>FOR NO KEY UPDATE</>, except that it
+ acquires a shared lock rather than exclusive lock on each retrieved
+ row. A shared lock blocks other transactions from performing
+ <command>UPDATE</command>, <command>DELETE</command>,
+ <command>SELECT FOR UPDATE</command> or
+ <command>SELECT FOR NO KEY UPDATE</> on these rows, but it does not
+ prevent them from performing <command>SELECT FOR SHARE</command> or
+ <command>SELECT FOR KEY SHARE</command>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>FOR KEY SHARE</literal>
+ </term>
+ <listitem>
+ <para>
+ Behaves similarly to <literal>FOR SHARE</literal>, except that the
+ lock is weaker: <literal>SELECT FOR UPDATE</> is blocked, but not
+ <literal>SELECT FOR NO KEY UPDATE</>. A key-shared lock blocks
+ other transactions from performing <command>DELETE</command> or
+ any <command>UPDATE</command> that changes the key values, but not
+ other <command>UPDATE</>, and neither does it prevent
+ <command>SELECT FOR NO KEY UPDATE</>, <command>SELECT FOR SHARE</>,
+ or <command>SELECT FOR KEY SHARE</>.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
<para>
<productname>PostgreSQL</productname> doesn't remember any
@@ -1139,11 +1216,62 @@ ERROR: could not serialize access due to read/write dependencies among transact
UPDATE</command> modifies selected rows to mark them locked, and so
will result in disk writes.
</para>
+
+ <table tocentry="1" id="row-lock-compatibility">
+ <title> Conflicting Row-level Locks</title>
+ <tgroup cols="5">
+ <colspec colnum="2" colname="lockst">
+ <colspec colnum="5" colname="lockend">
+ <spanspec namest="lockst" nameend="lockend" spanname="lockreq">
+ <thead>
+ <row>
+ <entry morerows="1">Requested Lock Mode</entry>
+ <entry spanname="lockreq">Current Lock Mode</entry>
+ </row>
+ <row>
+ <entry>FOR UPDATE</entry>
+ <entry>FOR NO KEY UPDATE</entry>
+ <entry>FOR SHARE</entry>
+ <entry>FOR KEY SHARE</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry>FOR UPDATE</entry>
+ <entry align="center">X</entry>
+ <entry align="center">X</entry>
+ <entry align="center">X</entry>
+ <entry align="center">X</entry>
+ </row>
+ <row>
+ <entry>FOR NO KEY UPDATE</entry>
+ <entry align="center">X</entry>
+ <entry align="center">X</entry>
+ <entry align="center">X</entry>
+ <entry align="center"></entry>
+ </row>
+ <row>
+ <entry>FOR SHARE</entry>
+ <entry align="center">X</entry>
+ <entry align="center">X</entry>
+ <entry align="center"></entry>
+ <entry align="center"></entry>
+ </row>
+ <row>
+ <entry>FOR KEY SHARE</entry>
+ <entry align="center">X</entry>
+ <entry align="center"></entry>
+ <entry align="center"></entry>
+ <entry align="center"></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
</sect2>
<sect2 id="locking-pages">
<title>Page-level Locks</title>
-
+
<para>
In addition to table and row locks, page-level share/exclusive locks are
used to control read/write access to table pages in the shared buffer
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 473939a..3e5a2f5 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -1298,64 +1298,8 @@ KEY SHARE
</para>
<para>
- <literal>FOR UPDATE</literal> causes the rows retrieved by the
- <command>SELECT</command> statement to be locked as though for
- update. This prevents them from being modified or deleted by
- other transactions until the current transaction ends. That is,
- other transactions that attempt <command>UPDATE</command>,
- <command>DELETE</command>,
- <command>SELECT FOR UPDATE</command>,
- <command>SELECT FOR NO KEY UPDATE</command>,
- <command>SELECT FOR SHARE</command> or
- <command>SELECT FOR KEY SHARE</command>
- of these rows will be blocked until the current transaction ends.
- The <literal>FOR UPDATE</> lock mode
- is also acquired by any <command>DELETE</> on a row, and also by an
- <command>UPDATE</> that modifies the values on certain columns. Currently,
- the set of columns considered for the <command>UPDATE</> case are those that
- have a unique index on them that can be used in a foreign key (so partial
- indexes and expressional indexes are not considered), but this may change
- in the future.
- Also, if an <command>UPDATE</command>, <command>DELETE</command>,
- or <command>SELECT FOR UPDATE</command> from another transaction
- has already locked a selected row or rows, <command>SELECT FOR
- UPDATE</command> will wait for the other transaction to complete,
- and will then lock and return the updated row (or no row, if the
- row was deleted). Within a <literal>REPEATABLE READ</> or <literal>SERIALIZABLE</> transaction,
- however, an error will be thrown if a row to be locked has changed
- since the transaction started. For further discussion see <xref
- linkend="mvcc">.
- </para>
-
- <para>
- <literal>FOR NO KEY UPDATE</> behaves similarly, except that the lock
- acquired is weaker: this lock will not block
- <literal>SELECT FOR KEY SHARE</> commands that attempt to acquire
- a lock on the same rows. This lock mode is also acquired by any
- <command>UPDATE</> that does not acquire a <literal>FOR UPDATE</> lock.
- </para>
-
- <para>
- <literal>FOR SHARE</literal> behaves similarly, except that it
- acquires a shared rather than exclusive lock on each retrieved
- row. A shared lock blocks other transactions from performing
- <command>UPDATE</command>, <command>DELETE</command>, <command>SELECT
- FOR UPDATE</command> or <command>SELECT FOR NO KEY UPDATE</>
- on these rows, but it does not prevent them
- from performing <command>SELECT FOR SHARE</command> or
- <command>SELECT FOR KEY SHARE</command>.
- </para>
-
- <para>
- <literal>FOR KEY SHARE</> behaves similarly to <literal>FOR SHARE</literal>,
- except that the lock
- is weaker: <literal>SELECT FOR UPDATE</> is blocked, but
- not <literal>SELECT FOR NO KEY UPDATE</>. A key-shared
- lock blocks other transactions from performing <command>DELETE</command>
- or any <command>UPDATE</command> that changes the key values, but not
- other <command>UPDATE</>, and neither does it prevent
- <command>SELECT FOR NO KEY UPDATE</>, <command>SELECT FOR SHARE</>, or
- <command>SELECT FOR KEY SHARE</>.
+ For more information on each row-level lock mode, refer to
+ <xref linkend="locking-rows">.
</para>
<para>
--
2.1.2
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
