Re: Delete of child-entry locks father-table

On Dec 3, 1:08 pm, "HerbertMue" <HerbertMuell..._at_gmx.at> 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.
> 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.
>
> 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.
>
> 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)

What version of Oracle?

• Mark D Powell --