Thanks for the question, Ankhush.
Asked: May 28, 2025 - 2:50 pm UTC
Last updated: June 04, 2025 - 7:04 am UTC
Version: Oracle 19 c
Viewed 1000+ times
You Asked
Hello,
I am executing a package on multiple threads. Our package is for deleting data in bulk.
Whenever I am running the package on multiple threads I am getting a deadlock.
On checking the trace file I found the delete statement which is causing deadlock. But the graph shows as
Rows waited on:
Session 38: no row
Session 70: no row
So I am not understanding the root cause of this deadlock.
There are indexes on all the foreign key columns for the table being deleted.
The deletion is based on primary key as well.
Can you please help what could be the probable issue?
and Connor said...
Assuming all the appropriate FK indexes are in place, a potential cause is an ITL shortfall. ("interested transaction list")
parallel worker 1
- locks rows in block X and then moves onto block Y and locks rows there
parallel worker 2
- locks different rows in block Y and then moves onto block X but can't find an ITL to use
You might need to look at bumping up INITRANS for the table and/or associated indexes.
We'd need to see the entire trace for more diagnosis.