Re: referential Integrity and SHARE locks

On Thu, 2007-08-02 at 12:24 -0800, Stephan Szabo wrote:
> On Thu, 8 Feb 2007, Marc Munro wrote:
>
> > 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.

Yes. That is tricky. For my proposed scheme to work, I guess we'd have
to be able to drop those locks which were just acquired by the RI
triggers. Not too simple, I guess.

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

Hmmm. That does seem to be the final nail in the coffin. Consider the
proposal withdrawn, and thanks for explaining it all to me.

__
Marc