Re: Referential Integrity and SHARE locks

On Mon, 5 Feb 2007, Simon Riggs wrote:

> On Sun, 2007-02-04 at 09:38 +0000, Simon Riggs wrote:
> > > > The TODO I was requesting you consider was this:
> > > >
> > > > "Develop non-conflicting locking scheme to allow RI checks to co-exist
> > > > peacefully with non-PK UPDATEs on the referenced table".
> > > >
> > > > That is, IMHO, a general statement of an important unresolved issue with
> > > > our Referential Integrity implementation. That is in no way intended as
> > > > any form of negative commentary on the excellent detailed work that has
> > > > got us so far already.
> > >
> > > Well, if we really want to solve that completely then we really need
> > > column locking, or at least locking at the level of arbitrary (possibly
> > > overlapping) unique constraints, not just the PK because foreign keys
> > > don't necessarily reference the primary key. But the PK case is certainly
> > > the most common and it'd certainly be nice to cover that case.
>
> ...
>
> > It occurs to me that if we had visibility in unique indexes, this would
> > allow the index rows to be separately lockable to the main row. That's
> > exactly what we need here.
>
> I've implemented a work-around using this principle, utilising RULEs and
> a duplicated PK column-only table. This still allows FK checks to work
> correctly, yet doesn't require the backend hack Csaba mentioned.
>
> My feeling is that more work in this area is required, even if we can't
> yet agree a TODO item.

I actually like the general idea your TODO item had, although I would say
non-referenced column update rather than non-PK update. Even if we put it
far out due to questions about what would be acceptable implementation.