From da7540b9d17c09c3b2e49a7580e5f42dcc4a4bcd Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 17 Sep 2006 22:50:31 +0000 Subject: Change ANALYZE to take ShareUpdateExclusiveLock not AccessShareLock on the table being analyzed. This prevents two ANALYZEs from running concurrently on the same table and possibly suffering concurrent-update failures while trying to store their results into pg_statistic. The downside is that a database-wide ANALYZE executed within a transaction block will hold ShareUpdateExclusiveLock on many tables simultaneously, which could lead to concurrency issues or even deadlock against another such ANALYZE. However, this seems a corner case of less importance than getting unexpected errors from a foreground ANALYZE when autovacuum elects to analyze the same table concurrently. Per discussion. --- doc/src/sgml/mvcc.sgml | 365 ++++++++++++++++++++++++------------------------- 1 file changed, 182 insertions(+), 183 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml index a9d7a9d5f98..d2deee2bfca 100644 --- a/doc/src/sgml/mvcc.sgml +++ b/doc/src/sgml/mvcc.sgml @@ -1,4 +1,4 @@ - + Concurrency Control @@ -77,7 +77,7 @@ - A transaction reads data written by a concurrent uncommitted transaction. + A transaction reads data written by a concurrent uncommitted transaction. @@ -89,9 +89,9 @@ - A transaction re-reads data it has previously read and finds that data - has been modified by another transaction (that committed since the - initial read). + A transaction re-reads data it has previously read and finds that data + has been modified by another transaction (that committed since the + initial read). @@ -103,9 +103,9 @@ - A transaction re-executes a query returning a set of rows that satisfy a - search condition and finds that the set of rows satisfying the condition - has changed due to another recently-committed transaction. + A transaction re-executes a query returning a set of rows that satisfy a + search condition and finds that the set of rows satisfying the condition + has changed due to another recently-committed transaction. @@ -125,79 +125,79 @@ - + Isolation Level - - - Dirty Read - - - Nonrepeatable Read - - - Phantom Read - + + + Dirty Read + + + Nonrepeatable Read + + + Phantom Read + - - Read uncommitted - - - Possible - - - Possible - - - Possible - + + Read uncommitted + + + Possible + + + Possible + + + Possible + - - Read committed - - - Not possible - - - Possible - - - Possible - + + Read committed + + + Not possible + + + Possible + + + Possible + - - Repeatable read - - - Not possible - - - Not possible - - - Possible - + + Repeatable read + + + Not possible + + + Not possible + + + Possible + - - Serializable - - - Not possible - - - Not possible - - - Not possible - + + Serializable + + + Not possible + + + Not possible + + + Not possible + @@ -547,174 +547,173 @@ SELECT SUM(value) FROM mytab WHERE class = 2; Table-level lock modes - ACCESS SHARE + ACCESS SHARE - - Conflicts with the ACCESS EXCLUSIVE lock - mode only. - - - - The commands SELECT and - ANALYZE acquire a lock of this mode on - referenced tables. In general, any query that only reads a table - and does not modify it will acquire this lock mode. - + + Conflicts with the ACCESS EXCLUSIVE lock + mode only. + + + + The SELECT command acquires a lock of this mode on + referenced tables. In general, any query that only reads a table + and does not modify it will acquire this lock mode. + - ROW SHARE + ROW SHARE - - Conflicts with the EXCLUSIVE and - ACCESS EXCLUSIVE lock modes. - - - - The SELECT FOR UPDATE and - SELECT FOR SHARE commands acquire a - lock of this mode on the target table(s) (in addition to - ACCESS SHARE locks on any other tables - that are referenced but not selected - ). - + + Conflicts with the EXCLUSIVE and + ACCESS EXCLUSIVE lock modes. + + + + The SELECT FOR UPDATE and + SELECT FOR SHARE commands acquire a + lock of this mode on the target table(s) (in addition to + ACCESS SHARE locks on any other tables + that are referenced but not selected + ). + - ROW EXCLUSIVE + ROW EXCLUSIVE - - Conflicts with the SHARE, SHARE ROW - EXCLUSIVE, EXCLUSIVE, and - ACCESS EXCLUSIVE lock modes. - - - - The commands UPDATE, - DELETE, and INSERT - acquire this lock mode on the target table (in addition to - ACCESS SHARE locks on any other referenced - tables). In general, this lock mode will be acquired by any - command that modifies the data in a table. - + + Conflicts with the SHARE, SHARE ROW + EXCLUSIVE, EXCLUSIVE, and + ACCESS EXCLUSIVE lock modes. + + + + The commands UPDATE, + DELETE, and INSERT + acquire this lock mode on the target table (in addition to + ACCESS SHARE locks on any other referenced + tables). In general, this lock mode will be acquired by any + command that modifies the data in a table. + - SHARE UPDATE EXCLUSIVE + SHARE UPDATE EXCLUSIVE - - Conflicts with the SHARE UPDATE EXCLUSIVE, - SHARE, SHARE ROW - EXCLUSIVE, EXCLUSIVE, and - ACCESS EXCLUSIVE lock modes. - This mode protects a table against - concurrent schema changes and VACUUM runs. - - - - Acquired by VACUUM (without ) - and by CREATE INDEX CONCURRENTLY. - + + Conflicts with the SHARE UPDATE EXCLUSIVE, + SHARE, SHARE ROW + EXCLUSIVE, EXCLUSIVE, and + ACCESS EXCLUSIVE lock modes. + This mode protects a table against + concurrent schema changes and VACUUM runs. + + + + Acquired by VACUUM (without ), + ANALYZE, and CREATE INDEX CONCURRENTLY. + - SHARE + SHARE - - Conflicts with the ROW EXCLUSIVE, - SHARE UPDATE EXCLUSIVE, SHARE ROW - EXCLUSIVE, EXCLUSIVE, and - ACCESS EXCLUSIVE lock modes. - This mode protects a table against concurrent data changes. - - - - Acquired by CREATE INDEX - (without ). - + + Conflicts with the ROW EXCLUSIVE, + SHARE UPDATE EXCLUSIVE, SHARE ROW + EXCLUSIVE, EXCLUSIVE, and + ACCESS EXCLUSIVE lock modes. + This mode protects a table against concurrent data changes. + + + + Acquired by CREATE INDEX + (without ). + - SHARE ROW EXCLUSIVE + SHARE ROW EXCLUSIVE - - Conflicts with the ROW EXCLUSIVE, - SHARE UPDATE EXCLUSIVE, - SHARE, SHARE ROW - EXCLUSIVE, EXCLUSIVE, and - ACCESS EXCLUSIVE lock modes. - - - + + Conflicts with the ROW EXCLUSIVE, + SHARE UPDATE EXCLUSIVE, + SHARE, SHARE ROW + EXCLUSIVE, EXCLUSIVE, and + ACCESS EXCLUSIVE lock modes. + + + This lock mode is not automatically acquired by any PostgreSQL command. - + - EXCLUSIVE + EXCLUSIVE - - Conflicts with the ROW SHARE, ROW - EXCLUSIVE, SHARE UPDATE - EXCLUSIVE, SHARE, SHARE - ROW EXCLUSIVE, EXCLUSIVE, and - ACCESS EXCLUSIVE lock modes. - This mode allows only concurrent ACCESS SHARE locks, - i.e., only reads from the table can proceed in parallel with a - transaction holding this lock mode. - - - + + Conflicts with the ROW SHARE, ROW + EXCLUSIVE, SHARE UPDATE + EXCLUSIVE, SHARE, SHARE + ROW EXCLUSIVE, EXCLUSIVE, and + ACCESS EXCLUSIVE lock modes. + This mode allows only concurrent ACCESS SHARE locks, + i.e., only reads from the table can proceed in parallel with a + transaction holding this lock mode. + + + This lock mode is not automatically acquired on user tables by any PostgreSQL command. However it is acquired on certain system catalogs in some operations. - + - ACCESS EXCLUSIVE + ACCESS EXCLUSIVE - - Conflicts with locks of all modes (ACCESS - SHARE, ROW SHARE, ROW - EXCLUSIVE, SHARE UPDATE - EXCLUSIVE, SHARE, SHARE - ROW EXCLUSIVE, EXCLUSIVE, and - ACCESS EXCLUSIVE). - This mode guarantees that the - holder is the only transaction accessing the table in any way. - - - - Acquired by the ALTER TABLE, DROP - TABLE, TRUNCATE, REINDEX, - CLUSTER, and VACUUM FULL - commands. This is also the default lock mode for LOCK - TABLE statements that do not specify a mode explicitly. - + + Conflicts with locks of all modes (ACCESS + SHARE, ROW SHARE, ROW + EXCLUSIVE, SHARE UPDATE + EXCLUSIVE, SHARE, SHARE + ROW EXCLUSIVE, EXCLUSIVE, and + ACCESS EXCLUSIVE). + This mode guarantees that the + holder is the only transaction accessing the table in any way. + + + + Acquired by the ALTER TABLE, DROP + TABLE, TRUNCATE, REINDEX, + CLUSTER, and VACUUM FULL + commands. This is also the default lock mode for LOCK + TABLE statements that do not specify a mode explicitly. + @@ -994,10 +993,10 @@ UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222; - Short-term share/exclusive page-level locks are used for - read/write access. Locks are released immediately after each - index row is fetched or inserted. However, note that GIN index - usually requires several inserts per one table row. + Short-term share/exclusive page-level locks are used for + read/write access. Locks are released immediately after each + index row is fetched or inserted. However, note that a GIN index + usually requires several inserts for each table row. -- cgit v1.2.3