Deadlock in DELETE parent/INSERT child-situation
Have created the following situation.
1 Simple parent table with PK-constraint/index
CREATE TABLE TESTP ( ID NUMBER NOT NULL);
ALTER TABLE TESTP ADD (PRIMARY KEY (ID));
INSERT INTO TESTP VALUES(1);
COMMIT;
2. Simple child table
CREATE TABLE TESTC ( P_ID NUMBER )
ALTER TABLE TESTC ADD (FOREIGN KEY (P_ID) REFERENCES TESTP (ID));
CREATE INDEX TESTC_TESTP_FK_IND ON TESTC(P_ID)
3. From 2 separate session I issue the following SQL:
- INSERT INTO testc (p_id) VALUES (1);
- DELETE FROM testp WHERE id = 1;
4. When these 2 statements are issued at exactly the same moment this situation runs into:
ORA-0060 Deadlock while waiting for resource.
Can anyone explain why this happens?
0