Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Delete of child-entry locks father-table

Delete of child-entry locks father-table

From: <HerbertMueller3_at_gmx.at>
Date: 3 Dec 2006 06:05:41 -0800
Message-ID: <1165154741.142155.136070@n67g2000cwd.googlegroups.com>


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:

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

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

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US