Skip to Main Content
  • Questions
  • Deadlock while doing parallel operation

Breadcrumb

Question and Answer

Connor McDonald

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.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library