Thread: AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better)
AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better)
From
Bryce Nesbitt
Date:
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
beendoing 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,
andgenerally 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 | grantedrelation |
article_key_idx | 18891 | AccessShareLock | trelation | articles |
18891| AccessShareLock | trelation | articles_editorid_idx | 18891 | AccessShareLock | trelation
|articles_pkey | 18891 | AccessShareLock | trelation | articles_response_to_key_idx |
18891| AccessShareLock | trelation | articles_state_idx | 18891 | AccessShareLock | trelation
|article_words | 18891 | AccessShareLock | trelation | article_words_cw |
18891| AccessShareLock | trelation | article_words_wc | 18891 | AccessShareLock | trelation
|collection_context_key_idx | 18891 | AccessShareLock | trelation | collection_owner_key_idx |
18891| AccessShareLock | trelation | collections | 18891 | AccessShareLock | trelation
|context_publication_key_idx | 18891 | AccessShareLock | trelation | contexts |
18891| AccessShareLock | trelation | contexts | 3879 | AccessExclusiveLock | frelation
|contexts | 5477 | AccessShareLock | frelation | contexts |
5484| AccessShareLock | frelation | contexts | 5485 | AccessShareLock | frelation |
contexts | 5486 | AccessShareLock | frelation | contexts |
5487| AccessShareLock | frelation | contexts | 5489 | AccessShareLock | frelation |
contexts | 5493 | AccessShareLock | frelation | contexts |
5494| AccessShareLock | frelation | contexts | 5496 | AccessShareLock | frelation |
contexts | 5497 | AccessShareLock | frelation | contexts |
5498| AccessShareLock | frelation | contexts | 5499 | AccessShareLock | frelation |
contexts | 5500 | AccessShareLock | frelation | contexts |
5502| AccessShareLock | frelation | contexts | 5503 | AccessShareLock | frelation |
contexts | 5504 | AccessShareLock | frelation | contexts |
5505| AccessShareLock | frelation | contexts | 5506 | AccessShareLock | frelation |
contexts | 5507 | AccessShareLock | frelation | contexts |
5508| AccessShareLock | frelation | contexts | 5509 | AccessShareLock | frelation |
contexts | 5510 | AccessShareLock | frelation | contexts |
5511| AccessShareLock | frelation | contexts | 5512 | AccessShareLock | frelation |
contexts | 5515 | AccessShareLock | frelation | contexts |
5516| AccessShareLock | frelation | contexts | 5517 | AccessShareLock | frelation |
contexts | 5518 | AccessShareLock | frelation | contexts |
5519| AccessShareLock | frelation | contexts | 5520 | AccessShareLock | frelation |
contexts | 5521 | AccessShareLock | frelation | contexts |
5523| AccessShareLock | frelation | contexts | 5524 | AccessShareLock | frelation |
contexts_id_key | 18891 | AccessShareLock | trelation | contexts_pkey |
18891| AccessShareLock | trelation | contexts_publication_date_idx | 18891 | AccessShareLock | trelation
|contexts_site_key_ct_id_journal_key | 18891 | AccessShareLock | trelation | contexts_site_key_ct_type_idx |
18891| AccessShareLock | trelation | group_key_idx | 18891 | AccessShareLock | trelation
|parent_key_idx | 18891 | AccessShareLock | trelation | pg_class |
3911| AccessShareLock | trelation | pg_class_oid_index | 3911 | AccessShareLock | trelation |
pg_class_relname_nsp_index | 3911 | AccessShareLock | trelation | pg_locks |
3911| AccessShareLock | trelation | site_key_idx | 18891 | AccessShareLock | trelation |
virtual_ancestor_key_idx | 18891 | AccessShareLock | trelation | virtual_ancestors |
18891| AccessShareLock | trelation | virtual_ancestors_pkey | 18891 | AccessShareLock | trelation
|virtual_context_key_idx | 18891 | AccessShareLock | trelation | words |
18891| AccessShareLock | trelation | words_pkey | 18891 | AccessShareLock | trelation
|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 contextsJOIN 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
(SELECTDISTINCT
a1.context_key FROM virtual_ancestors a1, collections, virtual_ancestors a2WHERE a1.ancestor_key =
collections.context_keyAND collections.owner_key = a2.context_key AND a2.ancestor_key = '82034')AND
articles.indexedORDERBY contexts.publication_date DESC;
Bryce Nesbitt <bryce2@obviously.com> writes:
> 1) Why the AccessExclusiveLock on create table?
It has to install a trigger on the referenced table. There has been
some discussion that maybe CREATE TRIGGER could take just ExclusiveLock
and not AccessExclusiveLock, but it hasn't been done yet; and I'm not
sure how much that would help you anyway. It would only help if the
referenced table (contexts) is essentially read-only to the rest of
your workload, else it'll block anyhow.
> 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.
It's not a heavy operation in that case. The problem doubtless is that
it's backed up behind some other transaction that is sitting on a lock
on the contexts table. And then everything else backs up behind it.
> 3) Other than eliminating dynamic table creation, how can this operation
> be altered?
Get rid of long-running transactions that hold locks on the contexts
table.
regards, tom lane
Re: AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better)
From
Bryce Nesbitt
Date:
Tom Lane wrote: <blockquote cite="mid:13526.1253640939@sss.pgh.pa.us" type="cite"><pre wrap="">Bryce Nesbitt <a class="moz-txt-link-rfc2396E"href="mailto:bryce2@obviously.com"><bryce2@obviously.com></a> writes: </pre><blockquotetype="cite"><pre wrap="">1) Why the AccessExclusiveLock on create table? </pre></blockquote><pre wrap=""> It has to install a trigger on the referenced table. There has been some discussion that maybe CREATE TRIGGER could take just ExclusiveLock and not AccessExclusiveLock, but it hasn't been done yet; and I'm not sure how much that would help you anyway. It would only help if the referenced table (contexts) is essentially read-only to the rest of your workload, else it'll block anyhow.</pre></blockquote> Thanks for the great info.<br /><br /> In our case all the longrunning access is read-only. We have a poorly designed table that several postgres consultants have burned out on tryingto fix.<br /><br /> Most notably there are also zillions of short read-only references that presently block while thecreate table attempts to gain the AccessExclusiveLock. <br /><br /> -Bryce<br />
Re: AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better)
From
Erik Jones
Date:
On Sep 22, 2009, at 9:55 AM, Bryce Nesbitt wrote: > # 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 Note that pid 3879 can't get the AccessExclusiveLock because pid 18891 is already holding an AccessShareLock. pid 18891 also had a lot of AccessShareLocks on a bunch of other tables. What is it doing? Figure out/get rid of that and you're problem will go away. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k