Delete of child-entry locks father-table

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.

Can someone explane that to me???

Here is my full example:

• Create Tables

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);

• Clear Config-Table and set up
• Insert 10 config-entries, which are visible for production
• (because validfrom is not null) 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; /
• set up production-table with some "random" data
• do some production 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; /
• Insert 3 new config-entries, which are not visible for production
• (because validfrom is null) BEGIN FOR i IN 11..13 LOOP INSERT INTO TEST_CONFIG (CONFIGID, DESCRIPTION, VALIDFROM, VALIDTO, PREVID, USERNAME) VALUES (i, 'Description'||i, null, null, null, 'User'||MOD(i,2)); END LOOP; COMMIT; END; /

--insure that the configuration can not be edited anymore
LOCK TABLE TEST_CONFIG IN EXCLUSIVE MODE NOWAIT;
--show user the changes since last "make entries valid"
--show user the entries which he/she will make valid
SELECT description, username
  FROM TEST_CONFIG
 WHERE VALIDFROM IS NULL;
--ask user if he/she wants to make the shown changes valid for
production
--if yes: make changes valid with the following code
--if no: commit to release the lock on config-table
DECLARE
  validfromdate DATE := SYSDATE;
BEGIN
  UPDATE TEST_CONFIG
     SET VALIDFROM = validfromdate
   WHERE VALIDFROM IS NULL;
  COMMIT;
END;

• now, production will see the following configuration SELECT * FROM TEST_CONFIG WHERE validfrom is not null;

  ---

• job, which delete no longer needed data
• this job run once every day DECLARE deletioninterval NUMBER := 50; deletiondate DATE := SYSDATE - deletioninterval; BEGIN DELETE FROM TEST_PRODUCTION WHERE productiondate < deletiondate; COMMIT; END;

  ---

If the deletion job and the "make valid task" run at the same time than one task do not work, because the config-table is locked. Received on Sun Dec 03 2006 - 08:05:41 CST