Home » SQL & PL/SQL » SQL & PL/SQL » Delete on child-table locks father-table
Delete on child-table locks father-table [message #207006] Sun, 03 December 2006 17:32 Go to next message
HerbertMue
Messages: 2
Registered: December 2006
Junior Member
If I delete data from a child-table, then the father tables get locked.
I can not understand the reason for locking the father-table when deleting child-entries.

Here the code for table-generation and data-setup:
---------------------------------------------------
-- table-creation
---------------------------------------------------
DROP TABLE TEST_PRODUCTION;

DROP TABLE TEST_CONFIG;

CREATE TABLE TEST_CONFIG
(
CONFIGID NUMBER(38) NOT NULL,
DESCRIPTION VARCHAR2(1000) NOT NULL,
VALIDFROM DATE,
VALIDTO DATE,
PREVID NUMBER(38),
USERNAME VARCHAR2(100) NOT NULL,
CONSTRAINT PK_TEST_CONFIG PRIMARY KEY (CONFIGID)
);

CREATE UNIQUE INDEX FK_TEST_CONFIG_PREVID ON TEST_CONFIG
(PREVID);

ALTER TABLE TEST_CONFIG ADD (
FOREIGN KEY (PREVID)
REFERENCES TEST_CONFIG (CONFIGID)
ON DELETE SET NULL DEFERRABLE INITIALLY IMMEDIATE);

CREATE TABLE TEST_PRODUCTION
(
PRODUCTIONID NUMBER(38) NOT NULL,
CONFIGID NUMBER(38) NOT NULL,
VALUE NUMBER(38,6),
PRODUCTIONDATE DATE,
CONSTRAINT PK_TEST_PRODUCTION PRIMARY KEY (PRODUCTIONID)
);

CREATE INDEX FK_TEST_PRODUCTION_CONFIGID ON TEST_PRODUCTION
(CONFIGID);

ALTER TABLE TEST_PRODUCTION ADD (
FOREIGN KEY (CONFIGID)
REFERENCES TEST_CONFIG (CONFIGID)
DEFERRABLE INITIALLY IMMEDIATE);
---------------------------------------------------
-- data setup
---------------------------------------------------
BEGIN
DELETE FROM TEST_PRODUCTION;
DELETE FROM TEST_CONFIG;
FOR i IN 1..10 LOOP
INSERT INTO TEST_CONFIG (CONFIGID, DESCRIPTION, VALIDFROM, VALIDTO,
PREVID, USERNAME)
VALUES (i, 'Description'||i, sysdate-100, null, null,
'User'||MOD(i,4));
END LOOP;
COMMIT;
END;
/

BEGIN
FOR i IN 1..10000 LOOP
INSERT INTO TEST_PRODUCTION (PRODUCTIONID, CONFIGID, VALUE,
PRODUCTIONDATE)
VALUES (i, MOD(i,10)+1, i/45*MOD(i,234), sysdate-90+TRUNC(i/150));
END LOOP;
COMMIT;
END;
/


If I execute the following delete-statement...

DELETE FROM TEST_PRODUCTION WHERE ID > 1000;


... and then I execute the following statement (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;


I get the following result:

OBJECT_TYPE OBJECT_NAME LOCK_TY MODE_HELD
------------- --------------- ------- ---------------
TABLE TEST_CONFIG DML Row-S (SS)
TABLE TEST_PRODUCTION DML Row-X (SX)


My question:
Why does a delete of child-entries lock the parent-table???

[Updated on: Sun, 03 December 2006 17:36]

Report message to a moderator

Re: Delete on child-table locks father-table [message #207021 is a reply to message #207006] Sun, 03 December 2006 22:54 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I think that is a table-level share lock. It will stop you locking the parent-table in exclusive mode, but not much else - you should even be able to lock all of the rows in exclusive mode.

In a different session, try:
delete from TEST_CONFIG;


If that succeeds, try:
lock table TEST_CONFIG in exclusive mode

This one should definitely fail.

The lock is necessary. At the time the DELETE occurs, but before a COMMIT, we must be able to roll back and ensure that referential integrity is maintained. This would not be possible if someone were to DROP the parent table in the meantime.

The purpose of this lock is to ensure that noone does any nasty DDL on the parent table until the transaction is committed.

Ross Leishman
Re: Delete on child-table locks father-table [message #207149 is a reply to message #207006] Mon, 04 December 2006 05:28 Go to previous message
HerbertMue
Messages: 2
Registered: December 2006
Junior Member
Thanks for your very helpfully reply.

delete from TEST_CONFIG; succeeds in meaning of locking.
But fails because of the foreign key constraint.

lock table TEST_CONFIG in exclusive mode.
fails definitely (in any case).

[Updated on: Mon, 04 December 2006 05:30]

Report message to a moderator

Previous Topic: Interchange of Columns
Next Topic: Help Needed for ideal approach!
Goto Forum:
  


Current Time: Sun Dec 04 00:31:40 CST 2016

Total time taken to generate the page: 0.06201 seconds