Skip to Main Content
  • Questions

  • ORA-00060 : Deadlock detected while waiting for resource in Multi-threaded Java Batch process

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Shardul.

Asked: September 25, 2017 - 6:17 am UTC

Last updated: September 27, 2017 - 4:56 pm UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Hi Tom

I have a multi-threaded batch process running on Production that fails due to "ORA-00060 : Deadlock detected while waiting for resource". I am getting following error message:

02:25:25,899 [CobolThread 34] ERROR Error executing update for rawSQL:
update TABLE_A s set s.INT_COL = s.INT_COL + 1 where s.PRIM_ID = ?
with 'string' parameter named 'primId' to : '8741104958' to indices: 1
java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource


The "PRIM_ID" used in updating the data in TABLE_A is unique for each record (Primary key), thus the distribution of work for each thread is unique (there is no chance of 2 or more threads updating the same row simultaneously).
There is no Bitmap-index on the table TABLE_A. The only index is Primary key Unique index.
Also, there are no foreign keys.

We had been facing the same problem with this batch process for MERGE Query which we later changed to conditional Update/Insert to avoid upsert race condition. However, the problem isn't solved yet.

Can you please help me with this problem?

Thanks
SD


and Chris said...

"there is no chance of 2 or more threads updating the same row simultaneously"

How exactly are you guaranteeing that?

Because the fact you've got a deadlock suggests that there are multiple sessions updating the same rows!

Even if your batch process works correctly, there may be other applications trying to change the same rows at the same time or other blocking operations.

Anyway, when you have a deadlock, the database writes details to a trace file on the server. Dig this out.

This will tell you which sessions were involved and why.

If you need help understanding this, check out:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1528515465282

Or put the contents in a review for this question and we'll see if we can help.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Shardul, September 27, 2017 - 11:01 am UTC

Thanks Chris

"How exactly are you guaranteeing that? "
100%.. The batch framework that we are using allows us to distribute the work evenly across the threads. Also, I have checked logs that would have printed duplicates. There is no evidence in the logs also.

At the time i raised this question,I hadn't received the AWR. Now that I have it with me, it is clear that the issue is "enq: TX - allocate ITL entry". I will try to tweak the INITRANS and PCTFREE parameters and see if it gets resolved.

Thanks for your support.
Chris Saxon
September 27, 2017 - 4:56 pm UTC

Have you seen the deadlock trace file? What did that tell you?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database