Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 7DD861337B53 for ; Wed, 17 Nov 2010 18:54:20 -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 72274-02 for ; Wed, 17 Nov 2010 22:54:12 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from lo.gmane.org (lo.gmane.org [80.91.229.12]) by mail.postgresql.org (Postfix) with ESMTP id AD76F1337B84 for ; Wed, 17 Nov 2010 18:54:12 -0400 (AST) Received: from list by lo.gmane.org with local (Exim 4.69) (envelope-from ) id 1PIqtG-00067T-Gx for pgsql-performance@postgresql.org; Wed, 17 Nov 2010 23:54:10 +0100 Received: from cpe-188-129-101-174.dynamic.amis.hr ([188.129.101.174]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Wed, 17 Nov 2010 23:54:10 +0100 Received: from ivoras by cpe-188-129-101-174.dynamic.amis.hr with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Wed, 17 Nov 2010 23:54:10 +0100 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-performance@postgresql.org From: Ivan Voras Subject: Re: Anyone seen this kind of lock pileup? Date: Wed, 17 Nov 2010 23:53:58 +0100 Lines: 19 Message-ID: References: <4CE412F4.4090101@agliodbs.com> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@dough.gmane.org X-Gmane-NNTP-Posting-Host: cpe-188-129-101-174.dynamic.amis.hr User-Agent: Mozilla/5.0 (X11; U; FreeBSD amd64; en-US; rv:1.9.2.12) Gecko/20101102 Thunderbird/3.1.6 In-Reply-To: <4CE412F4.4090101@agliodbs.com> 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/302 X-Sequence-Number: 41326 On 11/17/10 18:37, Josh Berkus wrote: > All, > > 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. Just a random guess: a timeout-supported livelock? (of course if there is any timeout-and-retry protocol going on and the timeout intervals are non-randomized).