RE: ITL deadlocks question

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 24 Jan 2014 20:00:39 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DD8197_at_exmbx05.thus.corp>

No.

Unless it's changed recently, a session will try each ITL slot in turn for a few seconds and then stop on one of them once it's gone through the entire list. If you're lucky the time it takes to cover the list means it will find a slot even if there were none when it started looking. You need to recreate the table with initrans set to at least 8 to be safe.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of McPeak, Matt [vxsmimmcp_at_subaru.com] Sent: 24 January 2014 19:24
To: Oracle Mailinglist
Subject: ITL deadlocks question

I have a situation where the users like to submit eight (8) copies of an I/O intensive job simultaneously – one job for each of our product lines.

The job operates on tables that are not partitioned (or otherwise physically separated) by product line, so that one database block may contain rows for many different product lines.

Occasionally, some of the processes are failing due to ITL deadlocks.

My question is: suppose you have:

Block 1  => ITL: txn A, txn B  with txn C waiting.
Block 2  => ITL: txn B, txn C  with txn A waiting…
Block 3  => ITL: txn C, txn *R*  with txn B waiting…

Is Oracle’s ITL deadlock detection smart enough to realize that, in block #1 for example, txn C is waiting for *either* txn A *or* txn B to end, but that it need not wait for both?

In other words, is it smart enough to know that the situation above is *not* a deadlock? (Because txn R can still end, then txn B, then both txn C and txn A can continue.)

The two tables involved are each in their own single table hash cluster, if that matters.

Thanks in advance for any help!

Matt

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 24 2014 - 21:00:39 CET