From: Matt Mello <alien@spaceship.com>
To: Jan Wieck <JanWieck@Yahoo.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: "deadlock detected" / cascading locks
Date: Mon, 19 May 2003 15:55:05 -0500
Message-ID: <3EC944A9.1080404@spaceship.com> (raw)
In-Reply-To: <3EC92E3F.6080703@Yahoo.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>
<10096.1053017383@sss.pgh.pa.us>
<3EC3C77A.3010006@spaceship.com>
<10286.1053018824@sss.pgh.pa.us>
<3EC3E53C.9070800@spaceship.com>
<3EC50CEB.9080202@spaceship.com>
<28936.1053112428@sss.pgh.pa.us>
<3EC92E3F.6080703@Yahoo.com>
Jan, can you explain more to a newby like me what you mean by "the
sessions set deferred modes differently?"
And, do these locks cascade? If I choose to do an update on table A,
and it has a foreign key to table B, which has a foreign key to table C,
does the update-induced lock on A cause a lock on B /and/ C?
My entire system is setup with foreign keys. Almost all of my tables
are part of a "tree" of foreign keys, which can all be traced back to
one root table. If a lock on a leaf table causes locks all the way back
to the root table, then foreign keys can't be used in my system (and it
seems they wouldn't be useful in many other systems ... where this also
true).
However, if the recent FK-lock bug fix stops this unnecessary locking,
things should work well, I guess. I have built and installed the
7.3STABLE tip, but not tested this situation yet.
Thanks again!
Jan Wieck wrote:
> Tom Lane wrote:
>
>> Matt Mello <alien@spaceship.com> writes:
>>
>>> The two situations I can think of are:
>>> 1) Two FK fields in a single row pointing to 2 different FK tables,
>>> for which any 2 updates get locks in opposite order (seems like a
>>> deadlock).
>>
>>
>>
>> I don't think this is possible, at least in 7.3. All backends are
>> guaranteed to run the triggers of a given table in the same order,
>> so the foreign-row locks should be acquired in the same column order
>> in all cases.
>
>
> Except the sessions set deferred modes differently.
>
>
> Jan
>
--
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, JanWieck@Yahoo.com, tgl@sss.pgh.pa.us
Subject: Re: "deadlock detected" / cascading locks
In-Reply-To: <3EC944A9.1080404@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