Re: referential Integrity and SHARE locks

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. I am not proposing reducing the number of
locks taken, but rather changing the order in which the locks are taken.

<concerned frown> What am I missing? </concerned frown>

__
Marc