Thread: Re: referential Integrity and SHARE locks

Simon Riggs started this thread with the question:
 . . . Why do we need a SHARE lock at all, on the **referenc(ed)** table? . . .

The root problem addressed by this thread seems to be that using share
locks in this way increases the likelihood of deadlock, and causes
blocking when no blocking is actually needed.

I would like to make a few observations leading to two alternative
proposals for dealing with this issue.

Deadlocks arise because of differences in the order in which locks are
taken.  If we have a parent table P, and a child C, and we modify two
children of the same P, locks will be taken in the order C1, P, C2.
Another process modifying only C2, will cause locks to be taken in the
order C2, P, leading to the possibility of deadlock.  With the current
system of RI, this sort of deadlock arises far too easily with the
result that RI is often disabled.

It is solely the order in which the locks are taken that causes the
problem.  If the RI constraints could lock the parent records before
locking the child, the possibility of deadlock would be much reduced.

Proposal 1: Alter the way RI triggers fire, so that they complete before
locking the row against which they fire.


Having a background in Oracle, I found myself considering how this is
not usually a problem with Oracle databases.  If I understand it
correctly, in Oracle the referential integrity constraints are
implemented by locking the index associated with the constraint, rather
than the records themselves.

Proposal 2: Lock the index associated with the parent record, rather
than the parent record itself.  Updates to indexed fields, and deletions
of records would need to also take such locks, but this should be enough
to allow non-referenced fields to be updated in a parent, even while
transactions are modifying its children.


__
Marc

"Marc Munro" <marc@bloodnok.com> writes:

> Proposal 1: Alter the way RI triggers fire, so that they complete before
> locking the row against which they fire.

It's kind of hard to know what records the user will choose to update before
he actually does the update...

> Proposal 2: Lock the index associated with the parent record, rather
> than the parent record itself.  

That doesn't help in our case because each version of a record has an index
entry. So even updates to unrelated fields imply index modifications. Worse,
deleting and updating don't remove the old index entries so even if you've
locked them you won't prevent people from doing exactly those operations
you're trying to avoid.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com

On Tue, 2007-06-02 at 23:47 +0000, Gregory Stark wrote:
> "Marc Munro" <marc@bloodnok.com> writes:
>
> > Proposal 1: Alter the way RI triggers fire, so that they complete before
> > locking the row against which they fire.
>
> It's kind of hard to know what records the user will choose to update before
> he actually does the update...

The RI triggers currently fire when a record is updated.  Under my
proposal they would fire in the same way but before the record is locked
rather than after.  Or am I missing your point?

> > Proposal 2: Lock the index associated with the parent record, rather
> > than the parent record itself.
>
> That doesn't help in our case because each version of a record has an index
> entry. So even updates to unrelated fields imply index modifications. Worse,
> deleting and updating don't remove the old index entries so even if you've
> locked them you won't prevent people from doing exactly those operations
> you're trying to avoid.

I guess my proposal was incomplete.  Obviously, before deleting, or
updating an indexed column, a lock would have to be taken on the index.
I believe this would suffice to guarantee referential integrity without
blocking updates that leave the referred indexes unchanged.

What you say about each version of a record having an index entry
confuses me.  I thought there was one index entry that lead to a chain
of tuples.  If this is not the case, I don't see how the current
exclusive locks on indexes work to enforce uniqueness.  Could you point
me to somewhere in the code or the documentation that explains this?

It still seems to me that if we can lock an index entry to guarantee
uniqueness, we can also lock it to implement RI constraints.

__
Marc

Marc Munro <marc@bloodnok.com> writes:
> The RI triggers currently fire when a record is updated.  Under my
> proposal they would fire in the same way but before the record is locked
> rather than after.  Or am I missing your point?

IOW, some other transaction could update or delete the tuple meanwhile?
Doesn't seem very promising.
        regards, tom lane