Re: Foreign key quandries

On 1 Mar 2003, Rod Taylor wrote:

> Gah, hit wrong key combination and the email sent early.
>
> Anyway, after that 'sleep' mess at the bottom is:
> T1 or T2: Sleeping too long -- lets run deadlock detection code
> T1 or T2: Kill off random participant of deadlock.
>
> The other participant is then allowed to continue their work.
>
> > Isn't the differentiation going to happen automatically?

The problem is that in case 2, both tuples 2 and 3 are already removed
before either foreign key check runs, so when T1 adds the value 3
row and checks the pk table it will find that its pk row has been
modified.  If the ordering went, delete 2 - check 2, delete 3 - check
3, this wouldn't be a problem, but then that'd fail in a
spec-non-compliant way if row 2 refered to row 3.

> > In case 2:
> >
> > T1: create fk tuple (uncommitted) -> value 2
*   T1: scan through pk table, no problems
> > T2: delete pk tuple value 2
*   T2: delete pk tuple value 3
> > T2:    scan through fk table, find uncommitted tuple value 2 ... sleep
> > T2:    scan through fk table, find uncommitted tuple value 2 ... sleep
> > T2:    scan through fk table, find uncommitted tuple value 2 ... sleep
> > T1: create fk tuple (uncommitted) -> value 3
*   T1: scan through pk table, find modified tuple value 3 ...