From: Tino Schwarze <postgresql@tisc.de>
To: pgsql-admin@postgresql.org
Subject: Re: automatically detecting long timed locks
Date: Thu, 13 Sep 2007 11:12:21 +0200
Message-ID: <20070913091221.GC6423@easy2.in-chemnitz.de> (raw)
In-Reply-To: <dcc563d10709121502o50221f23m4cd1b420b649b429@mail.gmail.com>
References: <20070912204657.GC29139@easy2.in-chemnitz.de>
<dcc563d10709121502o50221f23m4cd1b420b649b429@mail.gmail.com>
On Wed, Sep 12, 2007 at 05:02:56PM -0500, Scott Marlowe wrote:
> > is there an easy way to detect locks which are held for a very long
> > time? We've got some problems with a database here where a lock is held
> > for 1 or 2 hours though the operation should be very quick. A lot of
> > other processes are then waiting for the lock to become available.
> >
> > We would like to query for "lock on table xyz being held for more than
> > 60 seconds" or the other way around "query has been waiting for lock on
> > table xyz for more than 60 seconds".
> >
> > Of course, we get log entries like "LOG: duration: 8544285.789 ms
> > execute <unnamed>: lock table "xyz" in exclusive mode", but this is
> > AFTER the lock got acquired. We'd like to notice if lock acquisition
> > takes very long so we can look around and figure out what's wrong.
> >
> > How can we achieve this?
>
> I use something like this:
>
> select *, age(transactionid) from pg_locks where locktype='transactionid';
>
> to see which transactions are old. the higher the age the older the
> transaction is.
Ok, thanks - now I get a list of held locks and the transaction's age
with the query:
select c.relname,l.transaction,l.pid,l.granted,l.mode,
age(l.transaction) as age
from pg_class c,pg_locks l
where l.relation = c.oid
order by age desc, l.pid, l.mode
But is there also a way to get the age of the lock itself? We have got
the following workload here:
1. open transaction
2. perform complex work (may take several minutes)
3. lock a table exclusively
4. quickly insert into the table
5. close transaction
So I'd like to watch for transactions waiting for the lock in step 3 - I
do not care for the time it took to perform the work.
Maybe I'll have to code up some perl which remembers the transaction age
if the lock is acquired, then calculates lock holding time? Hm, or I'll
just watch for not yet granted locks and set a threshold if there are
more than x transactions waiting.
Any further hints or ideas?
Thanks,
Tino.
--
www.spiritualdesign-chemnitz.de
www.lebensraum11.de
Tino Schwarze * Parkstraße 17h * 09120 Chemnitz
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-admin@postgresql.org
Cc: postgresql@tisc.de
Subject: Re: automatically detecting long timed locks
In-Reply-To: <20070913091221.GC6423@easy2.in-chemnitz.de>
* 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