Re: Delete of child-entry locks father-table

> but they wrote:
>
> > > > 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>
>
> that is
>
> a) don't look at the lock table
> b) why does a delete from child lock parent
>
> It seems unambiguous to me - no lock table is involved and they say an
> operation on child locks parent - which it doesn't
>

Well, the OP noticed - as documented in note 223303.1 - that a delete on a child table takes a Row-SS lock on the parent table. Granted, the lock does not prevent DML on the parent table, but it still qualifies
as a table lock, isn't it ?

At any rate, I find the bigger picture more interesting. As I get it, the OP wanted to manually lock in exclusive mode some configuration (or definition or type) tables, to prevent concurrent changes by two users - not a completely stupid idea, IMO.

And then, he noticed that the routine OLTP work was blocked when deleting rows of tables that reference the manually locked configuration
tables, when the reference is (rightfully) enforced by an indexed foreign
key.

An interesting gotcha, dont you think ?

> so, I guess unless they clarify - we'll never really know :)

Well, I dont think the OP will dare to post anymore. He was told to read a few hundred pages before coming back...

(not saying that the advice is bad - in general)

Best regards, Tom

• Raoul