From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Matt Mello <alien@spaceship.com>
Cc: 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:28:06 -0700 (PDT)
Message-ID: <20030519152242.G42425-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <3EC944A9.1080404@spaceship.com>
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)
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: sszabo@megazone23.bigpanda.com, alien@spaceship.com, JanWieck@Yahoo.com, tgl@sss.pgh.pa.us
Subject: Re: "deadlock detected" / cascading locks
In-Reply-To: <20030519152242.G42425-100000@megazone23.bigpanda.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