Обсуждение: automatically detecting long timed locks

Hi there,

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?

Thanks,

Tino.

PS: Version is 8.2.4.

--
www.spiritualdesign-chemnitz.de
www.lebensraum11.de

Tino Schwarze * Parkstraße 17h * 09120 Chemnitz

On 9/12/07, Tino Schwarze <postgresql@tisc.de> wrote:
> Hi there,
>
> 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.

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

Tino Schwarze <postgresql@tisc.de> writes:
> 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.

Dunno if this is of any use to you, but there is a "log_lock_waits"
feature in CVS HEAD that seems to be exactly what you want.  I couldn't
recommend running 8.3 in production yet, but it wouldn't be out of the
realm of reason to back-port the patch for that.

            regards, tom lane