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
signature.asc
Description: This is a digitally signed message part