Message-ID: <3EC97E77.40503@Yahoo.com>
Date: Mon, 19 May 2003 21:01:43 -0400
From: Jan Wieck <JanWieck@Yahoo.com>
Organization: Unorganized
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.0.2) Gecko/20030208 Netscape/7.02
MIME-Version: 1.0
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: Matt Mello <alien@spaceship.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	pgsql-sql@postgresql.org
Subject: Re: "deadlock detected" / cascading locks
References: <20030519152242.G42425-100000@megazone23.bigpanda.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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 #