Message-ID: <3EC3C77A.3010006@spaceship.com>
Date: Thu, 15 May 2003 11:59:38 -0500
From: Matt Mello <alien@spaceship.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4a) Gecko/20030401
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: "deadlock detected" documentation
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> <10096.1053017383@sss.pgh.pa.us>
In-Reply-To: <10096.1053017383@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
 > 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 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.

I see.  One item I forgot to mention ...  Both update statements have a 
where clause containing txn.terminalid=38.  Txn.terminalid is a foreign 
key column in that table (txn.terminalid->terminal.terminalid).  Could 
this be the source of the trouble?

If so, I can work around this for now, but the patch you mention sounds 
like it would work in almost all situations where this might adversely 
(and unexpectedly) affect us.

Thanks!

-- 
Matt Mello