Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Gururaj.

Asked: August 31, 2018 - 7:07 pm UTC

Last updated: September 13, 2018 - 12:47 am UTC

Version: Oracle 12c

Viewed 10K+ times! This question is

You Asked

Hi Tom,
We are facing one issue where we are running jobs in parallel basically same procedure called with different parameters.Proc merges into a target table , so when we are trying to run in parallel. We are seeing enq : Tx ITL transaction wait. On weekdays after waiting for few minutes it throws error as Ora 00060 deadlock waiting for resource. But on weeekend even though there are ITL waits seen but it doesnt throw deadlock issue . What could be the reason ? Is there any fixed time the query waits before it throws deadlock issue or how is it working on weekend ?
Regards

and Connor said...

Good discussion on this here

https://asktom.oracle.com/pls/asktom/asktom.search?tag=deadlock-enq-tx-allocate-itl-entry

You'll need to bump up initrans or take some of the other remedial actions covered in the above entry.

In terms of why you might get a dead lock, that's easy to see when you think that blocks can be processed in any order.

So session 1 might do:

- consume last ITL slot on block 10
- cannot get an ITL slow on block 20

whilst session 2 does:

- consume last ITL slot on block 20
- cannot get last ITL slot on block 10

and voila...dead lock. Whereas if it just happened that session 1 was doing block 20 first, then block 10, then it would would wait for session 2 to commit, but not deadlock.

Deadlock detection is done on a timer (typically 3 seconds I think) so between and 0 and 3 seconds is how long it will take for a deadlock to be detected and one of the statements cancelled.

Rating

  (2 ratings)

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

Comments

Initrans bumping

Gururaj M J, September 08, 2018 - 1:48 pm UTC

The table mentioned below is a partitioned table we increased the Initrans to 10 at table level. We are having around 7 sessions running in parallel affecting the merge on same table. We are still getting the enq: TX - allocate ITL entry.
I was thinking INITRANS 10 means we can run 10 session in parallel.
Connor McDonald
September 10, 2018 - 4:19 am UTC

INITRANS 10 gets you 10 slots on *new* blocks, but you'll only get those slots on existing blocks *if* there is sufficient free space in the block to do so.

Initrans for old blocks

Gururaj M J, September 11, 2018 - 7:14 am UTC

As we process most of the history data, we will be hitting older blocks most of the time and we are getting ITL waits and deadlock even though we had changed the INITRANS.
Is there any solution for the older blocks to have reserved space for INITRANS.
Connor McDonald
September 13, 2018 - 12:47 am UTC

You need to reorg the table to free up that space. Here's a video demo of that


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