Re: Delete of child-entry locks father-table

HerbertMue wrote:
> Thank you for your fast answers.
> Unfortunately it is not so easy.
> Because in the task are involved many tables (dependencies).
> My approach is to lock all involved tables before doing the task.

Usually not a necessary thing to do when working with oracle.

> So I can ensure that I work with the same data the whole task long and
> that noone changes the data, which will be made visible for production
> during executing this task.

To me at least it doesn't sound like you have a good understanding of how oracle implements read consistency and locking.

It is very rare to see a lock table command in a properly designed oracle application. There is almost always a better way to do it.

>
> The select is no phantom read. It is that what is selected for
> displaying the user the changes, which will be made visible for
> production in the next step.
>
> But my main problem is that a DELETE of a child(slave)-entry causes a
> lock of the father (master, parent) table.
> I can not imagine any reason for that.

I would recommend that you acquire Tom Kyte's latest book. He has a complete discussion of this whole area.

>
> Look at the following example:
> TEST_CONFIG is parent
> TEST_PRODUCTION is child
>
> With "standard"-user:
> DELETE FROM TEST_PRODUCTION WHERE 1 = 0;
> 0 rows Deleted.
>
> With sys-user:
> SELECT ob.object_type, ob.object_name,
> DECODE (lk.TYPE,
> 'TX', 'Transaction',
> 'TM', 'DML',
> 'UL', 'PL/SQL User Lock',
> lk.TYPE
> ) lock_type,
> DECODE (lk.lmode,
> 0, 'None',
> 1, 'Null',
> 2, 'Row-S (SS)',
> 3, 'Row-X (SX)',
> 4, 'Share',
> 5, 'S/Row-X (SSX)',
> 6, 'Exclusive',
> TO_CHAR (lk.lmode)
> ) mode_held,
> DECODE (lk.request,
> 0, 'None',
> 1, 'Null',
> 2, 'Row-S (SS)',
> 3, 'Row-X (SX)',
> 4, 'Share',
> 5, 'S/Row-X (SSX)',
> 6, 'Exclusive',
> TO_CHAR (lk.request)
> ) mode_requested,
> se.lockwait, TO_CHAR (lk.id1) lock_id1, TO_CHAR (lk.id2)
> lock_id2,
> lk.BLOCK, ob.owner, lk.SID, se.username
> FROM v$lock lk, dba_objects ob, v$session se
> WHERE lk.TYPE IN ('TM', 'UL')
> AND lk.SID = se.SID
> AND lk.id1 = ob.object_id;
>
>
> The result is the following:
> OBJECT_TYPE OBJECT_NAME LOCK_TY MODE_HELD
> ------------- --------------- ------- ---------------
> TABLE TEST_CONFIG DML Row-S (SS)
> TABLE TEST_PRODUCTION DML Row-X (SX)
Received on Sun Dec 03 2006 - 13:26:58 CST