Skip site navigation (1) Skip section navigation (2)

Exhaustive list of what takes what locks

From: Nikolas Everett <nik9000(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Exhaustive list of what takes what locks
Date: 2011-02-01 19:18:37
Message-ID: AANLkTiksaoSZ03m_jALCBwhfogZxNA5_wKo_3J6ydBfF@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Dear list,

Is there an exhaustive list of what takes what locks and how long they last?
 I'm asking because we just had some trouble doing a hot db change to an
8.3.6 system.  I know it is an old version but it is what I have to work
with.  You can reproduce it like so:

First:
DROP TABLE IF EXISTS foo;
DROP TABLE IF EXISTS account;

CREATE TABLE account (account_id SERIAL PRIMARY KEY, name CHARACTER VARYING
NOT NULL);
CREATE TABLE foo (account_id INTEGER NOT NULL REFERENCES account
(account_id), stuff CHARACTER VARYING);

In one connection:
INSERT INTO account (name) SELECT generate_series FROM GENERATE_SERIES(0,
10000000);

In another connection while that last one is running:
DROP TABLE foo;

And in another connection if you are feeling frisky:
   select
     pg_stat_activity.datname,pg_class.relname,pg_locks.transactionid,
pg_locks.mode, pg_locks.granted,
     pg_stat_activity.usename,pg_stat_activity.current_query,
pg_stat_activity.query_start,
     age(now(),pg_stat_activity.query_start) as "age",
pg_stat_activity.procpid
   from pg_stat_activity,pg_locks left
     outer join pg_class on (pg_locks.relation = pg_class.oid)
   where pg_locks.pid=pg_stat_activity.procpid order by query_start;

That query shows that the DROP takes an AccessExclusiveLock on account.
 This isn't totally unexpected but it is unfortunate because it means we
have to wait for a downtime window to maintain constraints even if they are
not really in use.

This isn't exactly how our workload actually works.  Ours is more deadlock
prone.  We have many connections all querying account and we do the
migration in a transaction.  It looks as though the AccessExclusiveLock is
held until the transaction terminates.

Nik Everett

Responses

pgsql-performance by date

Next:From: Greg SmithDate: 2011-02-01 19:24:44
Subject: Re: Any experience using "shake" defragmenter?
Previous:From: Benjamin KrajmalnikDate: 2011-02-01 17:22:21
Subject: Re: Configuration for a new server.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group