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
signature.asc
Description: This is a digitally signed message part
