On Fri, 2007-02-02 at 10:51, Simon Riggs wrote: [snip] > Why do we need a SHARE lock at all, on the **referenc(ed)** table? > > It sounds like if we don't put a SHARE lock on the referenced table then > we can end the transaction in an inconsistent state if the referenced > table has concurrent UPDATEs or DELETEs. BUT those operations do impose > locking rules back onto the referencing tables that would not be granted > until after any changes to the referencing table complete, whereupon > they would restrict or cascade. So an inconsistent state doesn't seem > possible to me. > > What am I missing?
Well, here we do have a patch (deployed on production servers) which does not put the shared lock on the referenced table, and it lets in occasionally rows in the referencing tables which do not have parent rows in the referenced table. I'm not sure what is the mechanism, but it does happen, I can assure you. It happens rare enough that is not disturbing for us, compared to the deadlocks which happen without the patch - that's another matter... In our application we never update any key ids, so only deletes/inserts come in play, and I guess it happens when a referenced row is deleted just between a newly inserted child row checks that the parent row exists and the row is really inserted. Or something like that... Cheers, Csaba. ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
