To: Matt Mello <alien@spaceship.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: "deadlock detected" documentation 
In-reply-to: <3EC3C239.7070708@spaceship.com> 
References: <3EB82DA1.7020809@spaceship.com> <24809.1052282819@sss.pgh.pa.us>
	<3EC32935.5040005@spaceship.com> <7737.1053003625@sss.pgh.pa.us>
	<3EC3C239.7070708@spaceship.com>
Comments: In-reply-to Matt Mello <alien@spaceship.com>
	message dated "Thu, 15 May 2003 11:37:13 -0500"
Date: Thu, 15 May 2003 12:49:43 -0400
Message-ID: <10096.1053017383@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Matt Mello <alien@spaceship.com> writes:
> There are no triggers or rules except for the foreign keys.
> The txn table has several foreign keys.

You are probably getting deadlocks on the rows referenced by the foreign
keys, then.  As of the current release, each UPDATEd row will cause the
foreign-key triggers to grab a row lock (SELECT FOR UPDATE lock) on the
referenced row in the other table.  If you have concurrent updates
touching rows that reference the same other-table rows, it's possible to
get a deadlock depending on the order in which the updates happen to
occur.

There is no particularly good SQL-level workaround for this; the only
one I can think of is to do the updates one row at a time (or at least
one foreign-key value at a time), which sucks.

There is a patch in CVS tip that alleviates the problem substantially
by not taking a foreign-table lock when the foreign key value isn't
being changed by the UPDATE, which is commonly the case (although I'm
not entirely sure that it will help you, since you do seem to be
updating the referencing column).  If you want to try it, I believe Jan
Wieck posted a 7.3 version of the patch a month or so ago.

			regards, tom lane