CREATE OR REPLACE PROCEDURE PROC1
AS
BEGIN
INSERT INTO TAB1 ( cust_id, cust_name)
VALUES (12, 'Some name');
PROC2;
END;
/
SHOW ERRORS;
CREATE OR REPLACE PROCEDURE PROC2
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO TAB2 ( item_id, cust_id, item_name)
VALUES (12, 12, 'Any name');
COMMIT;
END;
/
SHOW ERRORS;
EXECUTE PROC1;
Error:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "schema.PROC2", line 5
ORA-06512: at "schema.PROC1", line 6
ORA-06512: at line 1
I have referential integrity defined on TAB2 (cust_id) to TAB1(cust_id) . as per my understanding, Autonomous transaction creates separate transaction and in that situation it should raise parent key not found exception. Please help me to understand.
Also if I insert parent record in one session and without committing it if I insert child record in another session then session related to child record is hanging in place of giving error.
Note:This code is just for illustration purpose