Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id D2CCC1337B4F
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 17 Nov 2010 17:59:09 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 37792-05
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 17 Nov 2010 21:59:02 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id DDB5A133616B
	for <pgsql-performance@postgresql.org>;
	Wed, 17 Nov 2010 17:59:01 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id oAHLwqsu024935;
	Wed, 17 Nov 2010 16:58:52 -0500 (EST)
To: Josh Berkus <josh@agliodbs.com>
cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Anyone seen this kind of lock pileup? 
In-reply-to: <4CE412F4.4090101@agliodbs.com> 
References: <4CE412F4.4090101@agliodbs.com>
Comments: In-reply-to Josh Berkus <josh@agliodbs.com>
	message dated "Wed, 17 Nov 2010 09:37:56 -0800"
Date: Wed, 17 Nov 2010 16:58:52 -0500
Message-ID: <24934.1290031132@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201011/298
X-Sequence-Number: 41322

Josh Berkus <josh@agliodbs.com> writes:
> Having an interesting issue on one 8.4 database.  Due to poor 
> application design, the application is requesting 8-15 exclusive 
> (update) locks on the same row on parallel connections pretty much 
> simultaneously (i.e. < 50ms apart).

> What's odd about this is that the resulting "lock pileup" takes a 
> mysterious 2-3.5 seconds to clear, despite the fact that none of the 
> connections are *doing* anything during that time, nor are there 
> deadlock errors.  In theory at least, the locks should clear out in 
> reverse order in less than a second; none of the individual statements 
> takes more than 10ms to execute.

Hmm ... can you extract a test case?  Or at least strace the backends
involved?

			regards, tom lane