Re: Referential Integrity and SHARE locks

On Sat, 2007-02-03 at 09:43 -0800, Stephan Szabo wrote:
> On Sat, 3 Feb 2007, Simon Riggs wrote:
>
> > On Fri, 2007-02-02 at 16:50 -0500, Tom Lane wrote:
> > > No, I don't. I think knowledge of which columns are in a PK is quite a
> > > few levels away from the semantics of row locking. To point out just
> > > one problem, what happens when you add or drop a PK? Or drop and
> > > replace with a different column set? Yes, I know dropping one requires
> > > exclusive lock on the table, but the transaction doing it could hold row
> > > locks within the table, and now it's very unclear what they mean.
> >
> > There are issues, yes. Dropping PKs is a very irregular occurrence nor
> > is it likely to be part of a complex transaction. It wouldn't bother me
> > to say that if a transaction already holds a RowExclusiveLock or a
> > RowShareLock it cannot upgrade to an AccessExclusiveLock.
>
> The lock check seems like a strange constraint, given that it's not
> necessarily going to be anything that conflicts with the row locks. I'm
> not sure there'd be a better idea given this sort of scheme, but it still
> seems strange.
>
> > 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.

IMHO generic column level locking would hardly ever be used. Locking for
RI seems to be 99% of the use case, which means we'd be OK if we found a
way of only locking an arbitary number of unique col groups. By
definition, each of these column groups is covered by a unique index.

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.

It also occurs to me that putting visibility in indexes doesn't prevent
us from optimizing away index inserts for UPDATEs. There is no
particular reason why the xmin and xmax of a unique index exactly
matches the xmin and xmax of the main row. [I said the opposite to Jim
Nasby a few days ago, regrettably]. The indexes would record the xmin
and xmax of the row, while the main heap would have the xmin and xmax of
the individual row versions.

If we did both HOT + visibility in unique indexes then we would be able
to eliminate the contention between INSERTs and UPDATEs with RI.

As Tom pointed out this would complicate deadlock detection, but then so
would any column-level locking scheme, so that isn't an argument against
any one in particular.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com