Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 95EA69FB1D9 for ; Wed, 18 Apr 2007 12:07:15 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 91743-06 for ; Wed, 18 Apr 2007 12:07:09 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from qb-out-0506.google.com (qb-out-0506.google.com [72.14.204.237]) by postgresql.org (Postfix) with ESMTP id 4865F9FA603 for ; Wed, 18 Apr 2007 12:07:09 -0300 (ADT) Received: by qb-out-0506.google.com with SMTP id c8so527162qbc for ; Wed, 18 Apr 2007 08:07:08 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; b=ElKpg/AJCZn0iGPJMS9bwiNBNlB4uvdKKKuWBUOkFNaY3Fxk/IgHGd+wEWKV4W1/4l/qTWiUJqa6L9GR99FxpPkQtxLpelx2CCywYKbXYmmT3UKaS24ZfVu83rxrzsN661QpmxRxZDVXnObxZ/FMD07wWZxV8KTSppfvaFMxfnQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=G05s6RbvO2cJPqL+PIgIHETwex07WTT5DPiX9rQE+jm48JR2878n/1XghqTaEOg+01sT8YCu5n//FavfoJmbYiVD9piK8f//5ia2HYErMvcaEbaHQBQsVbISwvy/NILs11+ZOoiW4fYx/VQc6que4DReDJx5IXJNKSX5OGDmw1g= Received: by 10.70.113.16 with SMTP id l16mr1081810wxc.1176908828346; Wed, 18 Apr 2007 08:07:08 -0700 (PDT) Received: by 10.70.89.7 with HTTP; Wed, 18 Apr 2007 08:07:08 -0700 (PDT) Message-ID: <357fa7590704180807j1906d42u1f0f539ac561dc3e@mail.gmail.com> Date: Wed, 18 Apr 2007 11:07:08 -0400 From: "Steven Flatt" To: pgsql-performance@postgresql.org Subject: Foreign Key Deadlocking MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_52216_1863810.1176908828286" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/331 X-Sequence-Number: 24208 ------=_Part_52216_1863810.1176908828286 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi, we're using Postgres 8.1.4. We've been seeing deadlock errors of this form, sometimes as often as several times per hour: Apr 17 13:39:50 postgres[53643]: [4-1] ERROR: deadlock detected Apr 17 13:39:50 postgres[53643]: [4-2] DETAIL: Process 53643 waits for ShareLock on transaction 111283280; blocked by process 53447. Apr 17 13:39:50 postgres[53643]: [4-3] Process 53447 waits for ShareLock on transaction 111282124; blocked by process 53242. Apr 17 13:39:50 postgres[53643]: [4-4] Process 53242 waits for ShareLock on transaction 111282970; blocked by process 53240. Apr 17 13:39:50 postgres[53643]: [4-5] Process 53240 waits for ShareLock on transaction 111282935; blocked by process 53168. Apr 17 13:39:50 postgres[53643]: [4-6] Process 53168 waits for ShareLock on transaction 111282707; blocked by process 53643. The deadlocks almost always seem to involve 4 or 5 processes. After observing the behaviour of the locks table, and searching the newsgroup and elsewhere, I'm fairly certain I know what the problem is. There is extremely high update activity by a dozen or more processes on a table which has FK references into two other tables. Each process may update 10s or 100s of rows and there is really no predictable access pattern. This blurb is from a previous discussion I found: ----- postgres performs a lock (share lock) on the tuples to which the foreign keys point, apparently to prevent other transactions from modifying the foreign key before this transaction commits. it is practically impossible to cause the references to be always in the same order, so a deadlock can occur. ----- I also see claims that this problem is fixed in 8.2, and if the fix is what I think it is, it's also in 8.1.6. Release 8.1.6 Changes * Fix bug causing needless deadlock errors on row-level locks (Tom) Upgrading to 8.2 is not realistic at this point of our project cycle, but if the fix is indeed in 8.1.6, I can push to upgrade to 8.1.latest. Can someone confirm that I've identified the right fix? Thanks, Steve ------=_Part_52216_1863810.1176908828286 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Hi, we're using Postgres 8.1.4.
 
We've been seeing deadlock errors of this form, sometimes as often as several times per hour:
 
Apr 17 13:39:50 postgres[53643]: [4-1] ERROR:  deadlock detected
Apr 17 13:39:50 postgres[53643]: [4-2] DETAIL:  Process 53643 waits for ShareLock on transaction 111283280; blocked by process 53447.
Apr 17 13:39:50 postgres[53643]: [4-3]     Process 53447 waits for ShareLock on transaction 111282124; blocked by process 53242.
Apr 17 13:39:50 postgres[53643]: [4-4]     Process 53242 waits for ShareLock on transaction 111282970; blocked by process 53240.
Apr 17 13:39:50 postgres[53643]: [4-5]     Process 53240 waits for ShareLock on transaction 111282935; blocked by process 53168.
Apr 17 13:39:50 postgres[53643]: [4-6]     Process 53168 waits for ShareLock on transaction 111282707; blocked by process 53643.
 
The deadlocks almost always seem to involve 4 or 5 processes.
 
After observing the behaviour of the locks table, and searching the newsgroup and elsewhere, I'm fairly certain I know what the problem is.  There is extremely high update activity by a dozen or more processes on a table which has FK references into two other tables.  Each process may update 10s or 100s of rows and there is really no predictable access pattern.
 
This blurb is from a previous discussion I found:
-----
postgres performs a lock (share lock) on the tuples to which the foreign keys point, apparently to prevent other transactions from modifying the foreign key before this transaction commits. it is practically impossible to cause the references to be always in the same order, so a deadlock can occur.
-----
 
I also see claims that this problem is fixed in 8.2, and if the fix is what I think it is, it's also in 8.1.6.
 
Release 8.1.6
Changes
* Fix bug causing needless deadlock errors on row-level locks (Tom)
 
Upgrading to 8.2 is not realistic at this point of our project cycle, but if the fix is indeed in 8.1.6, I can push to upgrade to 8.1.latest.
 
Can someone confirm that I've identified the right fix?
 
Thanks,
Steve
 
------=_Part_52216_1863810.1176908828286--