From bedb78d386a47fd66b6cda2040e0a5fb545ee371 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 28 Apr 2005 21:47:18 +0000 Subject: Implement sharable row-level locks, and use them for foreign key references to eliminate unnecessary deadlocks. This commit adds SELECT ... FOR SHARE paralleling SELECT ... FOR UPDATE. The implementation uses a new SLRU data structure (managed much like pg_subtrans) to represent multiple- transaction-ID sets. When more than one transaction is holding a shared lock on a particular row, we create a MultiXactId representing that set of transactions and store its ID in the row's XMAX. This scheme allows an effectively unlimited number of row locks, just as we did before, while not costing any extra overhead except when a shared lock actually has to be shared. Still TODO: use the regular lock manager to control the grant order when multiple backends are waiting for a row lock. Alvaro Herrera and Tom Lane. --- doc/src/sgml/ref/select.sgml | 87 ++++++++++++++++++++++++++++++-------------- 1 file changed, 60 insertions(+), 27 deletions(-) (limited to 'doc/src/sgml/ref/select.sgml') diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 78e591acd7..9b8b90bb16 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -1,5 +1,5 @@ @@ -30,7 +30,7 @@ SELECT [ ALL | DISTINCT [ ON ( expressionexpression [ ASC | DESC | USING operator ] [, ...] ] [ LIMIT { count | ALL } ] [ OFFSET start ] - [ FOR UPDATE [ OF table_name [, ...] ] ] + [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] ] where from_item can be one of: @@ -142,10 +142,11 @@ where from_item can be one of: - The FOR UPDATE clause causes the - SELECT statement to lock the selected rows - against concurrent updates. (See below.) + If the FOR UPDATE or FOR SHARE + clause is specified, the + SELECT statement locks the selected rows + against concurrent updates. (See below.) @@ -153,7 +154,8 @@ where from_item can be one of: You must have SELECT privilege on a table to - read its values. The use of FOR UPDATE requires + read its values. The use of FOR UPDATE or + FOR SHARE requires UPDATE privilege as well. @@ -503,7 +505,8 @@ HAVING condition select_statement is any SELECT statement without an ORDER - BY, LIMIT, or FOR UPDATE clause. + BY, LIMIT, FOR UPDATE, or + FOR SHARE clause. (ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of @@ -537,8 +540,9 @@ HAVING condition - Currently, FOR UPDATE may not be specified either for - a UNION result or for any input of a UNION. + Currently, FOR UPDATE and FOR SHARE may not be + specified either for a UNION result or for any input of a + UNION. @@ -552,7 +556,8 @@ HAVING condition select_statement is any SELECT statement without an ORDER - BY, LIMIT, or FOR UPDATE clause. + BY, LIMIT, FOR UPDATE, or + FOR SHARE clause. @@ -581,8 +586,9 @@ HAVING condition - Currently, FOR UPDATE may not be specified either for - an INTERSECT result or for any input of an INTERSECT. + Currently, FOR UPDATE and FOR SHARE may not be + specified either for an INTERSECT result or for any input of + an INTERSECT. @@ -596,7 +602,8 @@ HAVING condition select_statement is any SELECT statement without an ORDER - BY, LIMIT, or FOR UPDATE clause. + BY, LIMIT, FOR UPDATE, or + FOR SHARE clause. @@ -621,8 +628,9 @@ HAVING condition - Currently, FOR UPDATE may not be specified either for - an EXCEPT result or for any input of an EXCEPT. + Currently, FOR UPDATE and FOR SHARE may not be + specified either for an EXCEPT result or for any input of + an EXCEPT. @@ -789,8 +797,8 @@ OFFSET start - - <literal>FOR UPDATE</literal> Clause + + <literal>FOR UPDATE</literal>/<literal>FOR SHARE</literal> Clause The FOR UPDATE clause has this form: @@ -799,6 +807,13 @@ FOR UPDATE [ OF table_name [, ...] + + The closely related FOR SHARE clause has this form: + +FOR SHARE [ OF table_name [, ...] ] + + + FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for @@ -817,26 +832,44 @@ FOR UPDATE [ OF table_name [, ...] - If specific tables are named in FOR UPDATE, + FOR SHARE behaves similarly, except that it + acquires a shared rather than exclusive lock on each retrieved + row. A shared lock blocks other transactions from performing + UPDATE, DELETE, or SELECT + FOR UPDATE on these rows, but it does not prevent them + from performing SELECT FOR SHARE. + + + + It is currently not allowed for a single SELECT + statement to include both FOR UPDATE and + FOR SHARE. + + + + If specific tables are named in FOR UPDATE + or FOR SHARE, then only rows coming from those tables are locked; any other tables used in the SELECT are simply read as usual. - FOR UPDATE cannot be used in contexts where - returned rows can't be clearly identified with individual table - rows; for example it can't be used with aggregation. + FOR UPDATE and FOR SHARE cannot be + used in contexts where returned rows can't be clearly identified with + individual table rows; for example they can't be used with aggregation. It is possible for a SELECT command using both - LIMIT and FOR UPDATE + LIMIT and FOR UPDATE/SHARE clauses to return fewer rows than specified by LIMIT. - This is because LIMIT selects a number of rows, - but might then block requesting a FOR UPDATE lock. - Once the SELECT unblocks, the query qualification might not - be met and the row not be returned by SELECT. + This is because LIMIT is applied first. The command + selects the specified number of rows, + but might then block trying to obtain lock on one or more of them. + Once the SELECT unblocks, the row might have been deleted + or updated so that it does not meet the query WHERE condition + anymore, in which case it will not be returned. -- cgit v1.2.1