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

Re: automatically detecting long timed locks

From: Tino Schwarze <postgresql(at)tisc(dot)de>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: automatically detecting long timed locks
Date: 2007-09-13 09:12:21
Message-ID: 20070913091221.GC6423@easy2.in-chemnitz.de (view raw or flat)
Thread:
Lists: pgsql-admin
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

In response to

Responses

pgsql-admin by date

Next:From: Ashish KaralkarDate: 2007-09-13 12:57:54
Subject: Installing PostgreSQL admin package
Previous:From: Yingqi TangDate: 2007-09-12 23:56:29
Subject: "Failed to connect to the database" error

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