pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed From: Erik Jones <ejones@engineyard.com>
To: Bryce Nesbitt <bryce2@obviously.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better)
Date: Tue, 6 Oct 2009 22:25:44 -0700
Message-ID: <1AE076FD-0189-4DE3-B26C-C6B433061706@engineyard.com> (raw )
In-Reply-To: <4AB90198.2010109@obviously.com >
References: <4AB90198.2010109@obviously.com >
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
view thread (5+ messages)
Message-ID: <1AE076FD-0189-4DE3-B26C-C6B433061706@engineyard.com>
Permalink: ../1AE076FD-0189-4DE3-B26C-C6B433061706@engineyard.com/
Also on: postgresql.org/message-id/1AE076FD-0189-4DE3-B26C-C6B433061706@engineyard.com
copy link · copy postgr.es
reply 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: ejones@engineyard.com, bryce2@obviously.com
Subject: Re: AccessExclusiveLock on CREATE TABLE REFERENCES deadlocks (formatted better)
In-Reply-To: <1AE076FD-0189-4DE3-B26C-C6B433061706@engineyard.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