From: Matt Mello <alien@spaceship.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: "deadlock detected" documentation
Date: Thu, 15 May 2003 11:59:38 -0500
Message-ID: <3EC3C77A.3010006@spaceship.com> (raw)
In-Reply-To: <10096.1053017383@sss.pgh.pa.us>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: alien@spaceship.com, tgl@sss.pgh.pa.us
Subject: Re: "deadlock detected" documentation
In-Reply-To: <3EC3C77A.3010006@spaceship.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox