Re: Delete of child-entry locks father-table

HerbertMue wrote :

> Thanks for specifying in detail.
>
> But even now I do have the following question (don't look at the lock
> table and the "other bad code"). I only want to get answered this
> principal (fundamental, basic) question.
> <Question>
> Why does a DELETE of a child(slave)-entry causes a
> lock of the father (master, parent) table???
> </Question>

Dear,

Note that you are hitting a bug in Oracle's 9.2 documentation, adressed in metalink note 223303.1

<Excerpt>

Starting in version 9.2.0, a Row-SS lock is taken on the parent table for any DML issued against the child table. This will occur with or without an index on the foreign key column of the child table.

...

the lock is held until the transaction is committed or rolled back.

This change in behaviour was introduced in version 9.2.0 to address problems with parallel DML hanging in certain situations. This problem with PDML was addressed in Bug 1657607.

This new locking behaviour is an expected behaviour for the Oracle code. As only shared locks are involved, it does not prevent DML from being issued against either the child or parent tables. It will prevent operations that require an exclusive table level lock. However, as it generally considered to be bad design to have an application implementing exclusive table locks, the impact of the change should be minimal.

</Excerpt>

• Raoul