Re: Foreign key quandries

On Sat, 2003-03-01 at 02:38, Stephan Szabo wrote:
> 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.

The foreign key cascade is explicitly deferred to the end of the
statement via the trigger queue, but there is no reason that the foreign
key code can't run immediately for each tuple removed.

For the defer check to commit case, have the code add the element to the
trigger queue by making a fake trigger wrapper -- that isn't really a
trigger (simplest method probably) and it should and will deadlock.

> > > 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 ...
--
Rod Taylor <rbt(at)rbt(dot)ca>

PGP Key: http://www.rbt.ca/rbtpub.asc