Re: Mystique: Locks, Constraints and Indexes

In article <3jmh6g$4t7_at_zorn.mnet.medstroms.se>

           vutuc_at_mn.medstroms.se "Pauli Salmu" writes:

: Pauli Salmu <p.salmu_at_mn.medstroms.se> wrote:  

: > Then we got some help from the local Oracle representatives who said
: > that you probably must index every column that has a foreign key
: > constraint...

: 
:                                                   The question still
: remains: _why_ does it behave so?
: 

This is Oracle's trade-off between integrity, legality and performance.

Take table P (parent) and table C (child), where table P has only one row, table C has none. i.e. foreign key of child refers to primary/unique key of parent.

First user issues 'insert into table C, based on the single Parent row', and does not commit; second user issues 'delete the single Parent row'.

Problem:

    Should the second user be allowed to delete: in principal he     should because, from a read-consistent point of view, there are     no child rows for that parent row.

    But if he is allowed to delete then the first user will end up     with a child row without a parent.

Somewhere, a database implementor has to break the rules. Oracle's (and I guess everybody else's) solution is this:

      The first user is not allowed to delete the parent, even
      though he cannot see the impending child.  The problem
      is then how to minimise the cost of stopping the deletion.

Options:

    If there is an index on the foreign key (child), then the attempt     to delete the parent can examine that index very efficiently, and     register the uncommitted insert of the new child at reasonable cost.     Internally, Oracle is breaching read-consistency.

    If there is NO index on the foreign key (child), then the only way     Oracle can determine if there is an uncommitted child is to do a     tablescan of the child table - but that would be a bit expensive,     so use plan B, make it impossible for the parent to be deleted     as soon as the child is inserted, i.e. take out a share lock on     the parent table as part of the child insert.

       But, you ask, WHY lock the table, why not just lock the relevant
       parent row ?  Answer:  what if two people are trying to insert
       child rows at the same time - they can't both lock the row at the
       same time with a row-lock, so the only way to allow both inserts 
       to take place without interference is a table-lock.

Note: this is NOT a complete argument, it only addresses the most obvious part of the problem. There are 3 or 4 other strategies that could be used with varying degrees of convenience, cost, and risk, and working through all the possible "I do this, you do that, then he does the other" paths gets pretty messy. If anyone has a reference to an interesting paper on the subject, I would like to hear of it.

-- 
Jonathan Lewis