From 0ac5ad5134f2769ccbaefec73844f8504c4d6182 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Wed, 23 Jan 2013 12:04:59 -0300 Subject: Improve concurrency of foreign key locking This patch introduces two additional lock modes for tuples: "SELECT FOR KEY SHARE" and "SELECT FOR NO KEY UPDATE". These don't block each other, in contrast with already existing "SELECT FOR SHARE" and "SELECT FOR UPDATE". UPDATE commands that do not modify the values stored in the columns that are part of the key of the tuple now grab a SELECT FOR NO KEY UPDATE lock on the tuple, allowing them to proceed concurrently with tuple locks of the FOR KEY SHARE variety. Foreign key triggers now use FOR KEY SHARE instead of FOR SHARE; this means the concurrency improvement applies to them, which is the whole point of this patch. The added tuple lock semantics require some rejiggering of the multixact module, so that the locking level that each transaction is holding can be stored alongside its Xid. Also, multixacts now need to persist across server restarts and crashes, because they can now represent not only tuple locks, but also tuple updates. This means we need more careful tracking of lifetime of pg_multixact SLRU files; since they now persist longer, we require more infrastructure to figure out when they can be removed. pg_upgrade also needs to be careful to copy pg_multixact files over from the old server to the new, or at least part of multixact.c state, depending on the versions of the old and new servers. Tuple time qualification rules (HeapTupleSatisfies routines) need to be careful not to consider tuples with the "is multi" infomask bit set as being only locked; they might need to look up MultiXact values (i.e. possibly do pg_multixact I/O) to find out the Xid that updated a tuple, whereas they previously were assured to only use information readily available from the tuple header. This is considered acceptable, because the extra I/O would involve cases that would previously cause some commands to block waiting for concurrent transactions to finish. Another important change is the fact that locking tuples that have previously been updated causes the future versions to be marked as locked, too; this is essential for correctness of foreign key checks. This causes additional WAL-logging, also (there was previously a single WAL record for a locked tuple; now there are as many as updated copies of the tuple there exist.) With all this in place, contention related to tuples being checked by foreign key rules should be much reduced. As a bonus, the old behavior that a subtransaction grabbing a stronger tuple lock than the parent (sub)transaction held on a given tuple and later aborting caused the weaker lock to be lost, has been fixed. Many new spec files were added for isolation tester framework, to ensure overall behavior is sane. There's probably room for several more tests. There were several reviewers of this patch; in particular, Noah Misch and Andres Freund spent considerable time in it. Original idea for the patch came from Simon Riggs, after a problem report by Joel Jacobson. Most code is from me, with contributions from Marti Raudsepp, Alexander Shulgin, Noah Misch and Andres Freund. This patch was discussed in several pgsql-hackers threads; the most important start at the following message-ids: AANLkTimo9XVcEzfiBR-ut3KVNDkjm2Vxh+t8kAmWjPuv@mail.gmail.com 1290721684-sup-3951@alvh.no-ip.org 1294953201-sup-2099@alvh.no-ip.org 1320343602-sup-2290@alvh.no-ip.org 1339690386-sup-8927@alvh.no-ip.org 4FE5FF020200002500048A3D@gw.wicourts.gov 4FEAB90A0200002500048B7D@gw.wicourts.gov --- doc/src/sgml/pgrowlocks.sgml | 15 +++-- doc/src/sgml/ref/select.sgml | 146 ++++++++++++++++++++++++++++--------------- 2 files changed, 105 insertions(+), 56 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/pgrowlocks.sgml b/doc/src/sgml/pgrowlocks.sgml index 390fa236d31..c7714d88774 100644 --- a/doc/src/sgml/pgrowlocks.sgml +++ b/doc/src/sgml/pgrowlocks.sgml @@ -43,12 +43,6 @@ pgrowlocks(text) returns setof record tid Tuple ID (TID) of locked row - - lock_type - text - Shared for shared lock, or - Exclusive for exclusive lock - locker xid @@ -64,6 +58,15 @@ pgrowlocks(text) returns setof record xid[] Transaction IDs of lockers (more than one if multitransaction) + + lock_type + text[] + Lock mode of lockers (more than one if multitransaction), + an array of Key Share, Share, + For No Key Update, No Key Update, + For Update, Update. + + pids integer[] diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 9963780c313..26d511fad8c 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -45,7 +45,7 @@ SELECT [ ALL | DISTINCT [ ON ( expressioncount | ALL } ] [ OFFSET start [ ROW | ROWS ] ] [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ] - [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ] + [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ] where from_item can be one of: @@ -178,7 +178,8 @@ TABLE [ ONLY ] table_name [ * ] - If FOR UPDATE or FOR SHARE + If FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE + or FOR KEY SHARE is specified, the SELECT statement locks the selected rows against concurrent updates. (See table_name [ * ] You must have SELECT privilege on each column used - in a SELECT command. The use of FOR UPDATE - or FOR SHARE requires + in a SELECT command. The use of FOR NO KEY UPDATE, + FOR UPDATE, + FOR SHARE or FOR KEY SHARE requires UPDATE privilege as well (for at least one column of each table so selected). @@ -873,8 +875,8 @@ SELECT DISTINCT ON (location) location, time, report select_statement UNION [ ALL | DISTINCT ] select_statement select_statement is any SELECT statement without an ORDER - BY, LIMIT, FOR UPDATE, or - FOR SHARE clause. + BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, + FOR SHARE, or FOR KEY 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 @@ -910,7 +912,8 @@ SELECT DISTINCT ON (location) location, time, report - Currently, FOR UPDATE and FOR SHARE cannot be + Currently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and + FOR KEY SHARE cannot be specified either for a UNION result or for any input of a UNION. @@ -925,8 +928,8 @@ SELECT DISTINCT ON (location) location, time, report select_statement INTERSECT [ ALL | DISTINCT ] select_statement select_statement is any SELECT statement without an ORDER - BY, LIMIT, FOR UPDATE, or - FOR SHARE clause. + BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, + FOR SHARE, or FOR KEY SHARE clause. @@ -957,7 +960,8 @@ SELECT DISTINCT ON (location) location, time, report - Currently, FOR UPDATE and FOR SHARE cannot be + Currently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and + FOR KEY SHARE cannot be specified either for an INTERSECT result or for any input of an INTERSECT. @@ -972,8 +976,8 @@ SELECT DISTINCT ON (location) location, time, report select_statement EXCEPT [ ALL | DISTINCT ] select_statement select_statement is any SELECT statement without an ORDER - BY, LIMIT, FOR UPDATE, or - FOR SHARE clause. + BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, + FOR SHARE, or FOR KEY SHARE clause. @@ -1000,7 +1004,8 @@ SELECT DISTINCT ON (location) location, time, report - Currently, FOR UPDATE and FOR SHARE cannot be + Currently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and + FOR KEY SHARE cannot be specified either for an EXCEPT result or for any input of an EXCEPT. @@ -1185,7 +1190,14 @@ FETCH { FIRST | NEXT } [ count ] { - <literal>FOR UPDATE</literal>/<literal>FOR SHARE</literal> Clause + <literal>FOR UPDATE</>, <literal>FOR NO KEY UPDATE</>/<literal>FOR SHARE</>/<literal>FOR KEY SHARE</> Clauses + + + FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE + and FOR KEY SHARE + are locking clauses; they affect how SELECT + locks rows as they are obtained from the table. + The FOR UPDATE clause has this form: @@ -1194,6 +1206,13 @@ FOR UPDATE [ OF table_name [, ...] + + The FOR NO KEY UPDATE clause has this form: + +FOR NO KEY UPDATE [ OF table_name [, ...] ] [ NOWAIT ] + + + The closely related FOR SHARE clause has this form: @@ -1201,14 +1220,31 @@ FOR SHARE [ OF table_name [, ...] ] + + Similarly, the FOR KEY SHARE clause has this form: + +FOR KEY SHARE [ OF table_name [, ...] ] [ NOWAIT ] + + + FOR UPDATE causes the rows retrieved by the SELECT 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 UPDATE, - DELETE, or SELECT FOR UPDATE + DELETE, + SELECT FOR UPDATE, + SELECT FOR SHARE or + SELECT FOR KEY SHARE of these rows will be blocked until the current transaction ends. + The FOR UPDATE lock mode + is also acquired by any DELETE on a row, and also by an + UPDATE that modifies the values on certain columns. Currently, + the set of columns considered for the UPDATE case are those that + have an 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 UPDATE, DELETE, or SELECT FOR UPDATE from another transaction has already locked a selected row or rows, SELECT FOR @@ -1220,13 +1256,33 @@ FOR SHARE [ OF table_name [, ...] ] linkend="mvcc">. + + FOR NO KEY UPDATE behaves similarly, except that the lock + acquired is weaker: this lock will not block + SELECT FOR KEY SHARE commands that attempt to acquire + a lock on the same rows. + + 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. + from performing SELECT FOR SHARE or + SELECT FOR KEY SHARE. + + + + FOR KEY SHARE behaves similarly to FOR SHARE, + except that the lock + is weaker: SELECT FOR UPDATE is blocked, but + not SELECT FOR NO KEY UPDATE. A key-shared + lock blocks other transactions from performing DELETE + or any UPDATE that changes the key values, but not + other UPDATE, and neither it does prevent + SELECT FOR UPDATE, SELECT FOR SHARE, or + SELECT FOR KEY SHARE. @@ -1243,41 +1299,39 @@ FOR SHARE [ OF table_name [, ...] ] - If specific tables are named in FOR UPDATE - or FOR SHARE, + If specific tables are named in a locking clause, then only rows coming from those tables are locked; any other tables used in the SELECT are simply read as - usual. A FOR UPDATE or FOR SHARE + usual. A locking clause without a table list affects all tables used in the statement. - If FOR UPDATE or FOR SHARE is + If a locking clause is applied to a view or sub-query, it affects all tables used in the view or sub-query. - However, FOR UPDATE/FOR SHARE + However, these clauses do not apply to WITH queries referenced by the primary query. If you want row locking to occur within a WITH query, specify - FOR UPDATE or FOR SHARE within the - WITH query. + a locking clause within the WITH query. - Multiple FOR UPDATE and FOR SHARE + Multiple locking clauses can be written if it is necessary to specify different locking behavior for different tables. If the same table is mentioned (or - implicitly affected) by both FOR UPDATE and - FOR SHARE clauses, then it is processed as - FOR UPDATE. Similarly, a table is processed + implicitly affected) by more than one locking clause, + then it is processed as if it was only specified by the strongest one. + Similarly, a table is processed as NOWAIT if that is specified in any of the clauses affecting it. - FOR UPDATE and FOR SHARE cannot be + The locking clauses cannot be used in contexts where returned rows cannot be clearly identified with individual table rows; for example they cannot be used with aggregation. - When FOR UPDATE or FOR SHARE + When a locking clause appears at the top level of a SELECT query, the rows that are locked are exactly those that are returned by the query; in the case of a join query, the rows locked are those that contribute to @@ -1288,13 +1342,13 @@ FOR SHARE [ OF table_name [, ...] ] LIMIT is used, locking stops once enough rows have been returned to satisfy the limit (but note that rows skipped over by OFFSET will get locked). Similarly, - if FOR UPDATE or FOR SHARE + if a locking clause is used in a cursor's query, only rows actually fetched or stepped past by the cursor will be locked. - When FOR UPDATE or FOR SHARE + When a locking clause appears in a sub-SELECT, the rows locked are those returned to the outer query by the sub-query. This might involve fewer rows than inspection of the sub-query alone would suggest, @@ -1307,11 +1361,9 @@ SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss WHERE col1 = 5; condition is not textually within the sub-query. - - - Avoid locking a row and then modifying it within a later savepoint or - PL/pgSQL exception block. A subsequent - rollback would cause the lock to be lost. For example: + + Previous releases failed to preserve a lock which is upgraded by a later + savepoint. For example, this code: BEGIN; SELECT * FROM mytable WHERE key = 1 FOR UPDATE; @@ -1319,23 +1371,15 @@ SAVEPOINT s; UPDATE mytable SET ... WHERE key = 1; ROLLBACK TO s; - After the ROLLBACK, the row is effectively unlocked, rather - than returned to its pre-savepoint state of being locked but not modified. - This hazard occurs if a row locked in the current transaction is updated - or deleted, or if a shared lock is upgraded to exclusive: in all these - cases, the former lock state is forgotten. If the transaction is then - rolled back to a state between the original locking command and the - subsequent change, the row will appear not to be locked at all. This is - an implementation deficiency which will be addressed in a future release - of PostgreSQL. - - + would fail to preserve the FOR UPDATE lock after the + ROLLBACK. This has been fixed in release 9.2. + It is possible for a SELECT command running at the READ COMMITTED transaction isolation level and using ORDER - BY and FOR UPDATE/SHARE to return rows out of + BY and a locking clause to return rows out of order. This is because ORDER BY is applied first. The command sorts the result, but might then block trying to obtain a lock on one or more of the rows. Once the SELECT unblocks, some @@ -1765,14 +1809,16 @@ SELECT distributors.* WHERE distributors.name = 'Westward'; - <literal>FOR UPDATE</> and <literal>FOR SHARE</> + <literal>FOR NO KEY UPDATE</>, <literal>FOR UPDATE</>, <literal>FOR SHARE</>, <literal>FOR KEY SHARE</> Although FOR UPDATE appears in the SQL standard, the standard allows it only as an option of DECLARE CURSOR. PostgreSQL allows it in any SELECT query as well as in sub-SELECTs, but this is an extension. - The FOR SHARE variant, and the NOWAIT option, + The FOR NO KEY UPDATE, FOR SHARE and + FOR KEY SHARE variants, + as well as the NOWAIT option, do not appear in the standard. -- cgit v1.2.3