In our production DB, deadlock was detected in database alert log/trace file. The trace file shows that the same session, process and serial # are locking each other out . The call stack and deadlock graph are shown below:
- It seems this situation happens when there is an unindexed foreign key involved. There is only one FK which is already indexed.
- The primary key of batch_detail_log is populated by a before insert trigger in batch_detail_log table using a sequence (with nocache/order) and there is a commit right after the insert. Does the commit rule out the possibility of a PK overlap during insert or it might be causing it?
Call Stack:
0x7f738778 3173 package body DBO.PKG_AUTONOMOUS_TRANS.P_CREATE_DETAIL_LOG
0xb588e990 3170 package body DBO.PKG_BENEFIT_WH.P_GENERATE_REPORT_CONTRIBUTION.P_INSERT_INTO_BATCH_DETAIL_LOG
0xb588e990 5847 package body DBO.PKG_BENEFIT_WH.P_GENERATE_REPORT_CONTRIBUTION
0xb588e990 6243 package body DBO.PKG_BENEFIT_WH.P_GENERATE_REPORT
0xa05b1648 93 procedure DBO.P_MODIFY_WR
0xbdff1d90 1 anonymous block
Deadlock graph:
Signature of procs and packages involved:
DBO.PKG_BENEFIT_WH.P_GENERATE_REPORT_CONTRIBUTION has a sub proc p_insert_into_batch_detail_log which is inserting into the detail log.
Signature:
PROCEDURE p_insert_into_batch_detail_log(…………….) IS
begin
dbo.pkg_autonomous_trans.p_create_detail_log(………..);
Signature of PKG_AUTONOMOUS_TRANS.P_CREATE_DETAIL_LOG:
procedure p_create_detail_log(………….) is
pragma autonomous_transaction;
BEGIN
insert into dbo.batch_detail_log…………..;
COMMIT;
What might be causing the deadlock? Can anybody please suggest a possible solution?
Thanks !!