本文发表在 rolia.net 枫下论坛DROP TABLE CHILD;

DROP TABLE PARENT;

CREATE TABLE PARENT (COL1 NUMBER);

ALTER TABLE PARENT ADD CONSTRAINT PARENT_PK PRIMARY KEY (COL1);

CREATE TABLE CHILD (COL1 NUMBER);

CREATE INDEX CHILD_IX_01 ON CHILD (COL1);

ALTER TABLE CHILD ADD CONSTRAINT CHILD_FK_01 FOREIGN KEY (COL1) REFERENCES PARENT;

INSERT INTO PARENT VALUES (1);

INSERT INTO CHILD VALUES (1);

COMMIT;
-----------------------
test 1:
txn 1:

delete parent where col1=1; -- I got an exception, because child data found
-----------------------
test2:

txn1:

delete child where col1=1;

delete parent where col1=1; -- everything is fine.

txn2:

insert child values (1); -- txn2 got stuck here, because txn1 is not finished and oracle doesn't know the parent key is really deleted or not

txn1:
commit; -- txn2 got an exception immediately, because parent key not found

-----------------------
test3:
-- the same as test2, except that we rollback txn1 instead of commit.
-- at this time, insert in txn2 suceeded immediately.

-----------------------
test4:

We create the foreign key on child as ON DELETE CASCADE, then:

txn1:
delete parent where col1=1; -- the child is also deleted.

txn2:
insert child values (1); -- txn2 got stuck here, the same as test2

txn1:
commit or rollback, the behavior is the same as test2 and test3
-----------------------
more tests:

I switched the order of txn1 and txn2: insert the child record in txn2 first, then try to delete parent in txn1. txn1 got stuck when txn2 is not completed.

------------------------
As far as I can see, the behavior is reasonable. But I don't know what happens inside. The bug is about test1: when ANY child record is found, oracle should throw an exception immediately. But what it really does is: it places a row level lock on the parent table, then more locks on the child table, while we expect it to stop at the first row of child table. The reason it's scanning all child records rather than the first record is unknown, but Oracle already bugged it as #5504961.更多精彩文章及讨论，请光临枫下论坛 rolia.net