Re: Foreign key quandries

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
large.