Re: Delete of child-entry locks father-table

From: HerbertMue <HerbertMueller3_at_gmx.at>
Date: 3 Dec 2006 10:08:54 -0800
Message-ID: <1165169334.256391.282640@n67g2000cwd.googlegroups.com>

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)

Received on Sun Dec 03 2006 - 12:08:54 CST