pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: "deadlock detected" / cascading locks
3+ messages / 3 participants
[nested] [flat]
* Re: "deadlock detected" / cascading locks
@ 2003-05-19 20:55 Matt Mello <alien@spaceship.com>
2003-05-19 22:28 ` Re: "deadlock detected" / cascading locks Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 3+ messages in thread
From: Matt Mello @ 2003-05-19 20:55 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: "deadlock detected" / cascading locks
2003-05-19 20:55 Re: "deadlock detected" / cascading locks Matt Mello <alien@spaceship.com>
@ 2003-05-19 22:28 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-05-20 01:01 ` Re: "deadlock detected" / cascading locks Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Stephan Szabo @ 2003-05-19 22:28 UTC (permalink / raw)
To: Matt Mello <alien@spaceship.com>; +Cc: Jan Wieck <JanWieck@Yahoo.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
On Mon, 19 May 2003, Matt Mello wrote:
> Jan, can you explain more to a newby like me what you mean by "the
> sessions set deferred modes differently?"
If a constraint is deferred in one transaction and not in another the
order the locks are grabbed could be different. For example you have two
constraints A and B, and two transactions T1 and T2. T1 sets constraint A
to be deferred, T2 has constraint A being immediate. T1 will get the lock
for B first (at end of statement) and then get lock A second (at end of
transaction). T2 will get lock A first (at end of statement) and B second
(at end of statement). This could cause deadlock if both transactions do
something that locks the same rows for A and B and T1 gets the B lock,
then T2 gets the A lock, then T2 waits on T1 for the B lock, then T1 waits
for T2 on the A lock when it goes to commit.
> 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)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: "deadlock detected" / cascading locks
2003-05-19 20:55 Re: "deadlock detected" / cascading locks Matt Mello <alien@spaceship.com>
2003-05-19 22:28 ` Re: "deadlock detected" / cascading locks Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2003-05-20 01:01 ` Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 0 replies; 3+ messages in thread
From: Jan Wieck @ 2003-05-20 01:01 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Matt Mello <alien@spaceship.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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 #
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-05-20 01:01 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-19 20:55 Re: "deadlock detected" / cascading locks Matt Mello <alien@spaceship.com>
2003-05-19 22:28 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-05-20 01:01 ` Jan Wieck <JanWieck@Yahoo.com>
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