pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tino Schwarze <postgresql@tisc.de>
To: pgsql-admin@postgresql.org
Subject: automatically detecting long timed locks
Date: Wed, 12 Sep 2007 22:46:57 +0200
Message-ID: <20070912204657.GC29139@easy2.in-chemnitz.de> (raw)

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



view thread (4+ messages)  latest in thread

Message-ID: <20070912204657.GC29139@easy2.in-chemnitz.de>
Permalink:  ../20070912204657.GC29139@easy2.in-chemnitz.de/
Also on:    postgresql.org/message-id/20070912204657.GC29139@easy2.in-chemnitz.de

 · 

reply

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: <20070912204657.GC29139@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