Re: referential Integrity and SHARE locks

On Thu, 8 Feb 2007, Marc Munro wrote:

> On Thu, 2007-08-02 at 10:06 -0800, Stephan Szabo wrote:
> > On Thu, 8 Feb 2007, Marc Munro wrote:
> . . .
> > >
> > > That other transaction, T1, would have run the same RI triggers and so
> > > would have the same parent records locked.
> >
> > That's not true in the case of delete, since the referencing table
> > triggers are on insert and update. . . .
>
> Let me see if I have this scenario right:
>
> Transaction T1 updates child record C1, with RI causing the parent P1 to
> be locked before the child.
>
> In the meantime transaction T2, successfully deletes C1 as it has not
> yet been locked.
>
> (Please tell me if I have misunderstood what you are saying)
>
> Yes in this case, T1 must abort because the record it was going to
> update has disappeared from underneath it. I don't see how this is
> significantly different from the same race for the record if the table
> had no RI constraints. The only difference that I can see, is that T1
> now has some locks that it must relinquish as the transaction aborts.
>
> > . . . Second, the parent record locks are not
> > exclusive which means that both can be granted, so I don't see how this
> > stops the second from continuing before the first.
>
> I don't think this does stop the second from continuing before the
> first. What will stop it, is the eventual lock that is taken on the
> child (triggering) record.

But at that point, you've already had to compose the new row in order to
call the trigger for the ri check, right? So, one of those new rows will
be out of date by the time it actually gets the lock. Presumably that
means that you need to recalculate the new row, but you've already done a
check and gotten a lock based on the old new row.

Also, another big problem is the fact that SQL requires that the action
already have happened before the check in cases where the constraint
references the same table. The row being updated or inserted might
reference a row that will be updated or inserted by a later action of the
same statement.