Anand Yadav wrote:
CREATE OR REPLACE PROCEDURE PROC1
INSERT INTO TAB1 ( cust_id, cust_name)
VALUES (12, 'Some name');
CREATE OR REPLACE PROCEDURE PROC2
INSERT INTO TAB2 ( item_id, cust_id, item_name)
VALUES (12, 12, 'Any name');
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
post complete excerpt from alert_SID.log file that shows whole ORA-00060 trace dump
ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. More info in file D:\ORACLE\diag\rdbms\o12cr102\o12cr102\trace\o12cr102_ora_5716.trc.
Resource Name process session holds waits process session holds waits
TX-00090037-0000014B-00000000-00000000 33 204 X 33 204 S
session 204: DID 0001-0021-00000027 session 204: DID 0001-0021-00000027
Rows waited on:
Session 204: no row
----- Information for the OTHER waiting sessions -----
----- End of information for the OTHER waiting sessions -----
Information for THIS session:
----- Current SQL Statement for this session (sql_id=g3ur8v65cfhh7) -----
INSERT INTO TAB2 VALUES (12, 12, 'Any name')
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
000007FF2EE2E500 5 procedure SCOTT.PROC1
000007FF2EF28630 7 procedure SCOTT.PROC
000007FF2ED63F18 1 anonymous block
Process global information:
process: 0x000007FF3D5E95B0, call: 0x000007FF32C63E28, xact: 0x000007FF390A6868, curses: 0x000007FF3D28AD68, usrses: 0x000007FF3D28AD68
SO: 0x000007FF3D5E95B0, type: 2, owner: 0x0000000000000000, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x000007FF3D5E95B0, name=process, file=ksu.h LINE:13949, pg=0 conuid=0
(process) Oracle pid:33, ser:20, calls cur/top: 0x000007FF32C63E28/0x000007FF3CF11B38
flags : (0x0) - icon_uid:0
flags2: (0x0), flags3: (0x10)
intr error: 0, call error: 0, sess error: 0, txn error 0
intr queue: empty
ksudlp FALSE at location: 0
(post info) last post received: 140 0 2
last post received-location: ksl2.h LINE:3108 ID:kslpsr
last process to post me: 0x7ff3d5da820 1 6
last post sent: 0 0 26
last post sent-location: ksa2.h LINE:290 ID:ksasnd
last process posted by me: 0x7ff3d5da820 1 6
waiter on post event: 0
(latch info) hold_bits=0x0
Process Group: DEFAULT, pseudo proc: 0x000007FF3D74DC10
O/S info: user: SYSTEM, term: J1013169W7LT, ospid: 5716
OSD pid info:
I'm not sure If I can add full content of trace file here. This error can be applicable on any database with just simple example.
>This error can be applicable on any database with just simple example
post reproducible test case.
1 person found this helpful
You've nearly explained the problem yourself by using two separate sessions (which strips the issue to a bare minimum).
The second session (child insert) can see (under the covers) that there is a parent key which may become available for referential integrity to be satisfied, so it waits until the first session commits or rolls back. If the first session commits the second session will continue, if the first session rolls back the second session will raise the referential integrity error.
You get a similar pattern of behaviour if you insert and commit the parent in the first session, then delete the parent from the first session and DON'T commit. Again the second session will wait for the first session to commit when it tries to insert a child row. In this case if the first session commits the second session will raise an error, if the first session rolls back the second session will continue.
Moving the processing into the autonomous transaction case - the autonomous transaction has to wait for the main transaction to commit or rollback, but the main transaction can't do either because it's waiting for the autonomous transaction to complete. Hence deadlock.
Thanks Jonathan for the nice explanation. It helped to clear my doubts