Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id CD14B9F9C40 for ; Thu, 13 Sep 2007 06:12:27 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 65946-04 for ; Thu, 13 Sep 2007 06:12:18 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from easy2.in-chemnitz.de (easy2.in-chemnitz.de [88.198.17.232]) by postgresql.org (Postfix) with ESMTP id 224DD9F9BEC for ; Thu, 13 Sep 2007 06:12:23 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by easy2.in-chemnitz.de (Postfix) with ESMTP id 7E21BB1DCD for ; Thu, 13 Sep 2007 11:12:21 +0200 (CEST) Received: from easy2.in-chemnitz.de ([127.0.0.1]) by localhost (easy.in-chemnitz.de [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id rxjQIxYyiNhn for ; Thu, 13 Sep 2007 11:12:21 +0200 (CEST) Received: by easy2.in-chemnitz.de (Postfix, from userid 1013) id 6C54BB1DA1; Thu, 13 Sep 2007 11:12:21 +0200 (CEST) Date: Thu, 13 Sep 2007 11:12:21 +0200 From: Tino Schwarze To: pgsql-admin@postgresql.org Subject: Re: automatically detecting long timed locks Message-ID: <20070913091221.GC6423@easy2.in-chemnitz.de> References: <20070912204657.GC29139@easy2.in-chemnitz.de> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/88 X-Sequence-Number: 26849 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 : 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