From: Bryce Nesbitt <bryce2@obviously.com>
To: pgsql-sql@postgresql.org
Subject: AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better)
Date: Tue, 22 Sep 2009 09:55:52 -0700
Message-ID: <4AB90198.2010109@obviously.com> (raw)
Dear Postgres Gurus;
I've just diagnosed a PostgreSQL 8.3.4 server which, about once a month, would deadlock shortly after 11pm. It had been doing this for years,
and the prior response was simply to reboot everything. The culprit boils down to:
# create table cache_table_20090921 (
site_key int NOT NULL REFERENCES contexts(context_key),
blah_blah TEXT NULL,
PRIMARY KEY (site_key)
);
Without the REFERENCES this is a very fast operation and always works.
Adding "DEFERRABLE INITIALLY DEFERRED" or "ON DELETE CASCADE" (as the original did) makes no difference. With any REFERENCES, the create table may block a long time (up to 45 minutes and counting), all while attempting to get an AccessExclusiveLock. The server quickly backs up,
simple select statements block, the server maxes out the number of incoming apache connections, things start swapping, and generally it all gets into a huge tangle:
# SELECT l.locktype,c.relname,l.pid,l.mode,granted from pg_locks l,pg_class c where l.relation=c.oid order by relname,granted;
locktype | relname | pid | mode | granted
relation | article_key_idx | 18891 | AccessShareLock | t
relation | articles | 18891 | AccessShareLock | t
relation | articles_editorid_idx | 18891 | AccessShareLock | t
relation | articles_pkey | 18891 | AccessShareLock | t
relation | articles_response_to_key_idx | 18891 | AccessShareLock | t
relation | articles_state_idx | 18891 | AccessShareLock | t
relation | article_words | 18891 | AccessShareLock | t
relation | article_words_cw | 18891 | AccessShareLock | t
relation | article_words_wc | 18891 | AccessShareLock | t
relation | collection_context_key_idx | 18891 | AccessShareLock | t
relation | collection_owner_key_idx | 18891 | AccessShareLock | t
relation | collections | 18891 | AccessShareLock | t
relation | context_publication_key_idx | 18891 | AccessShareLock | t
relation | contexts | 18891 | AccessShareLock | t
relation | contexts | 3879 | AccessExclusiveLock | f
relation | contexts | 5477 | AccessShareLock | f
relation | contexts | 5484 | AccessShareLock | f
relation | contexts | 5485 | AccessShareLock | f
relation | contexts | 5486 | AccessShareLock | f
relation | contexts | 5487 | AccessShareLock | f
relation | contexts | 5489 | AccessShareLock | f
relation | contexts | 5493 | AccessShareLock | f
relation | contexts | 5494 | AccessShareLock | f
relation | contexts | 5496 | AccessShareLock | f
relation | contexts | 5497 | AccessShareLock | f
relation | contexts | 5498 | AccessShareLock | f
relation | contexts | 5499 | AccessShareLock | f
relation | contexts | 5500 | AccessShareLock | f
relation | contexts | 5502 | AccessShareLock | f
relation | contexts | 5503 | AccessShareLock | f
relation | contexts | 5504 | AccessShareLock | f
relation | contexts | 5505 | AccessShareLock | f
relation | contexts | 5506 | AccessShareLock | f
relation | contexts | 5507 | AccessShareLock | f
relation | contexts | 5508 | AccessShareLock | f
relation | contexts | 5509 | AccessShareLock | f
relation | contexts | 5510 | AccessShareLock | f
relation | contexts | 5511 | AccessShareLock | f
relation | contexts | 5512 | AccessShareLock | f
relation | contexts | 5515 | AccessShareLock | f
relation | contexts | 5516 | AccessShareLock | f
relation | contexts | 5517 | AccessShareLock | f
relation | contexts | 5518 | AccessShareLock | f
relation | contexts | 5519 | AccessShareLock | f
relation | contexts | 5520 | AccessShareLock | f
relation | contexts | 5521 | AccessShareLock | f
relation | contexts | 5523 | AccessShareLock | f
relation | contexts | 5524 | AccessShareLock | f
relation | contexts_id_key | 18891 | AccessShareLock | t
relation | contexts_pkey | 18891 | AccessShareLock | t
relation | contexts_publication_date_idx | 18891 | AccessShareLock | t
relation | contexts_site_key_ct_id_journal_key | 18891 | AccessShareLock | t
relation | contexts_site_key_ct_type_idx | 18891 | AccessShareLock | t
relation | group_key_idx | 18891 | AccessShareLock | t
relation | parent_key_idx | 18891 | AccessShareLock | t
relation | pg_class | 3911 | AccessShareLock | t
relation | pg_class_oid_index | 3911 | AccessShareLock | t
relation | pg_class_relname_nsp_index | 3911 | AccessShareLock | t
relation | pg_locks | 3911 | AccessShareLock | t
relation | site_key_idx | 18891 | AccessShareLock | t
relation | virtual_ancestor_key_idx | 18891 | AccessShareLock | t
relation | virtual_ancestors | 18891 | AccessShareLock | t
relation | virtual_ancestors_pkey | 18891 | AccessShareLock | t
relation | virtual_context_key_idx | 18891 | AccessShareLock | t
relation | words | 18891 | AccessShareLock | t
relation | words_pkey | 18891 | AccessShareLock | t
relation | words_word | 18891 | AccessShareLock | t
# select pg_size_pretty(pg_total_relation_size('contexts_pkey'));
35MB
# select pg_size_pretty(pg_total_relation_size('contexts'));
787MB
# select pg_cancel_backend(3879)
t
And all is well again.
-----------------------------------------------------------------------------------------------------------------
So my questions are various
1) Why the AccessExclusiveLock on create table?
2) Why is the foreign key check a heavy operation, since a new table
will have zero foreign keys, it can't possibly violate the constraint yet.
3) Other than eliminating dynamic table creation, how can this operation
be altered?
4) Is there more data I should collect about the lock (e.g. do I have a
good lock summary select statement)?
Note that process 18891 in the example is doing a select:
--------------
SELECT contexts.context_key FROM contexts
JOIN articles ON
(articles.context_key=contexts.context_key)
WHERE contexts.context_key IN
(SELECT context_key FROM article_words JOIN words using (word_key) WHERE word = 'four')
AND contexts.context_key IN
(SELECT context_key FROM article_words JOIN words using (word_key) WHERE word = 'step')
AND contexts.context_key IN
(SELECT context_key FROM article_words JOIN words using (word_key) WHERE word = 'control')
AND contexts.context_key IN
(SELECT context_key FROM article_words JOIN words using (word_key) WHERE word = 'process')
AND contexts.context_key IN
(SELECT context_key FROM article_words JOIN words using (word_key) WHERE word = 'business')
AND contexts.context_key IN (SELECT DISTINCT
a1.context_key FROM virtual_ancestors a1, collections, virtual_ancestors a2
WHERE a1.ancestor_key = collections.context_key AND collections.owner_key = a2.context_key AND a2.ancestor_key = '82034')
AND articles.indexed
ORDER BY contexts.publication_date DESC;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bryce2@obviously.com
Subject: Re: AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better)
In-Reply-To: <4AB90198.2010109@obviously.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox