Skip site navigation (1) Skip section navigation (2)

Re: Foreign key quandries

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Rod Taylor <rbt(at)rbt(dot)ca>
Cc: PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Foreign key quandries
Date: 2003-03-01 05:44:07
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
On 1 Mar 2003, Rod Taylor wrote:

> I'm not sure I understand the question. The case as described simply has
> to deadlock because your approaching the same values with conflicting
> tasks from opposite directions.

Well, the problem is that two cases (one of which I think deadlock is
unnecessary in) are very similar.

As I see it:

T1: insert 2
T2: delete 2
T1: insert 2/update 2 (non-key fields)
 shouldn't need to deadlock.

T1: insert 2
T2: delete 2 & 3
   * delete 2's check blocks before
      checking 3
T1: insert 3
 should not need to deadlock I think

T1: insert 2
T2: delete 3
T2: delete 2
 (or delete 2 & 3 where 3's check goes then 2's check blocks)
T1: insert 3
 does need to deadlock

In the second case, both deletes have happened so the row the insert wants
to check against is marked for deletion, but since it's going to be
checking for the 3 row in the future, and will error if T1 commits, I
think it's safe for it to go through.

I'm trying to find a way to differentiate the second and third case given
that I'm running inside a constraint check on insert 3. It'd be easy if
transaction 1 could see that it's going to be checking for the 3 row
later, but I think that'd involve keeping around alot of information about
the rows that are affected in some shared way which could get really

In response to


pgsql-hackers by date

Next:From: Rod TaylorDate: 2003-03-01 07:03:01
Subject: Re: Foreign key quandries
Previous:From: Rod TaylorDate: 2003-03-01 05:11:37
Subject: Re: Foreign key quandries

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group