Re: AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better) - Mailing list pgsql-sql
| From | Erik Jones |
|---|---|
| Subject | Re: AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better) |
| Date | |
| Msg-id | 1AE076FD-0189-4DE3-B26C-C6B433061706@engineyard.com Whole thread Raw |
| In response to | AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better) (Bryce Nesbitt <bryce2@obviously.com>) |
| List | pgsql-sql |
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