Re: AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better)

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