Re: automatically detecting long timed locks

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