From da7540b9d17c09c3b2e49a7580e5f42dcc4a4bcd Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 17 Sep 2006 22:50:31 +0000 Subject: [PATCH] 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 ++++++++++++++++----------------- src/backend/commands/analyze.c | 38 ++-- 2 files changed, 201 insertions(+), 202 deletions(-) diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml index a9d7a9d5f9..d2deee2bfc 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. diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c index 2930eacb50..f9e41e3531 100644 --- a/src/backend/commands/analyze.c +++ b/src/backend/commands/analyze.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/analyze.c,v 1.97 2006/08/18 16:09:08 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/commands/analyze.c,v 1.98 2006/09/17 22:50:31 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -129,10 +129,14 @@ analyze_rel(Oid relid, VacuumStmt *vacstmt) CHECK_FOR_INTERRUPTS(); /* - * Open the relation, getting only a read lock on it. If the rel has - * been dropped since we last saw it, we don't need to process it. + * Open the relation, getting ShareUpdateExclusiveLock to ensure that + * two ANALYZEs don't run on it concurrently. (This also locks out + * a concurrent VACUUM, which doesn't matter much at the moment but + * might matter if we ever try to accumulate stats on dead tuples.) + * If the rel has been dropped since we last saw it, we don't need + * to process it. */ - onerel = try_relation_open(relid, AccessShareLock); + onerel = try_relation_open(relid, ShareUpdateExclusiveLock); if (!onerel) return; @@ -147,7 +151,7 @@ analyze_rel(Oid relid, VacuumStmt *vacstmt) ereport(WARNING, (errmsg("skipping \"%s\" --- only table or database owner can analyze it", RelationGetRelationName(onerel)))); - relation_close(onerel, AccessShareLock); + relation_close(onerel, ShareUpdateExclusiveLock); return; } @@ -162,7 +166,7 @@ analyze_rel(Oid relid, VacuumStmt *vacstmt) ereport(WARNING, (errmsg("skipping \"%s\" --- cannot analyze indexes, views, or special system tables", RelationGetRelationName(onerel)))); - relation_close(onerel, AccessShareLock); + relation_close(onerel, ShareUpdateExclusiveLock); return; } @@ -174,7 +178,7 @@ analyze_rel(Oid relid, VacuumStmt *vacstmt) */ if (isOtherTempNamespace(RelationGetNamespace(onerel))) { - relation_close(onerel, AccessShareLock); + relation_close(onerel, ShareUpdateExclusiveLock); return; } @@ -183,7 +187,7 @@ analyze_rel(Oid relid, VacuumStmt *vacstmt) */ if (RelationGetRelid(onerel) == StatisticRelationId) { - relation_close(onerel, AccessShareLock); + relation_close(onerel, ShareUpdateExclusiveLock); return; } @@ -317,7 +321,7 @@ analyze_rel(Oid relid, VacuumStmt *vacstmt) 0, 0); vac_close_indexes(nindexes, Irel, AccessShareLock); - relation_close(onerel, AccessShareLock); + relation_close(onerel, ShareUpdateExclusiveLock); return; } @@ -444,7 +448,8 @@ analyze_rel(Oid relid, VacuumStmt *vacstmt) /* * Close source relation now, but keep lock so that no one deletes it * before we commit. (If someone did, they'd fail to clean up the entries - * we made in pg_statistic.) + * we made in pg_statistic. Also, releasing the lock before commit would + * expose us to concurrent-update failures in update_attstats.) */ relation_close(onerel, NoLock); } @@ -1079,14 +1084,9 @@ compare_rows(const void *a, const void *b) * Note analyze_rel() has seen to it that we won't come here when * vacuuming pg_statistic itself. * - * Note: if two backends concurrently try to analyze the same relation, - * the second one is likely to fail here with a "tuple concurrently - * updated" error. This is slightly annoying, but no real harm is done. - * We could prevent the problem by using a stronger lock on the - * relation for ANALYZE (ie, ShareUpdateExclusiveLock instead - * of AccessShareLock); but that cure seems worse than the disease, - * especially now that ANALYZE doesn't start a new transaction - * for each relation. The lock could be held for a long time... + * Note: there would be a race condition here if two backends could + * ANALYZE the same table concurrently. Presently, we lock that out + * by taking a self-exclusive lock on the relation in analyze_rel(). */ static void update_attstats(Oid relid, int natts, VacAttrStats **vacattrstats) @@ -1202,7 +1202,7 @@ update_attstats(Oid relid, int natts, VacAttrStats **vacattrstats) else { /* No, insert new tuple */ - stup = heap_formtuple(sd->rd_att, values, nulls); + stup = heap_formtuple(RelationGetDescr(sd), values, nulls); simple_heap_insert(sd, stup); } -- 2.40.0