Re: Referential Integrity and SHARE locks

Csaba Nagy wrote:
> 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?

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

You say below the cut that you're not updating keys, so presumably it's 
other columns. Which leads me to something I've wondered for a while - 
why do we lock the whole row? Is it just a matter of "not optimised that 
yet" or is there a good reason why locking just some columns isn't 
practical.

--   Richard Huxton  Archonet Ltd