From: Jan Wieck <JanWieck@Yahoo.com>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: Matt Mello <alien@spaceship.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 21:01:43 -0400
Message-ID: <3EC97E77.40503@Yahoo.com> (raw)
References: <20030519152242.G42425-100000@megazone23.bigpanda.com>
Stephan Szabo wrote:
> [...]
Yupp
>>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?
>
>
> Not for checks since those don't change the table in question. It's
> possible for locks to cascade through referential action effects (although
> that effect is partially minimized by the bug fix mentioned for
> update unless the referencing column is itself the one being referenced)
Normally referential actions like ON DELETE CASCADE will cascade top
town, parent->child->grandchild. If an update to table A cascades
through a referential integrity constraint into an update to table B,
there is no possible deadlock through this action by itself. The primary
key colum(s) in A being updated must have a UNIQUE constraint (as per
SQL standard). Having a lock for that column(s) in A means (logically in
this context) having a lock on all referencing rows in B. So noone else
can attempt to update these rows "through this constraints referential
action".
The other way around there can be multiple path's upward from many
tables to common RI ancestors. But the checks done do not cascade up
since they only lock for update. With my patch they don't even do that
"if the referencing columns did not change.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
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: JanWieck@Yahoo.com, sszabo@megazone23.bigpanda.com, alien@spaceship.com, tgl@sss.pgh.pa.us
Subject: Re: "deadlock detected" / cascading locks
In-Reply-To: <3EC97E77.40503@Yahoo.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