Identifying and Resolving Oracle ITL Deadlock

Question

I have an Oracle DB package that is routinely causing what I believe is an ITL (Interested Transaction List) deadlock. The relevant portion of a trace file is below.

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-0000cb52-00000000        22     131           S       23     143          SS
TM-0000ceec-00000000        23     143    SX             32     138    SX   SSX
TM-0000cb52-00000000        30     138    SX             22     131           S
session 131: DID 0001-0016-00000D1C session 143: DID 0001-0017-000055D5
session 143: DID 0001-0017-000055D5 session 138: DID 0001-001E-000067A0
session 138: DID 0001-001E-000067A0 session 131: DID 0001-0016-00000D1C
Rows waited on:
Session 143: no row
Session 138: no row
Session 131: no row

There are no bit-map indexes on this table, so that's not the cause. As far as I can tell, the lack of "Rows waited on" plus the "S" in the Waiter waits column likely indicates that this is an ITL deadlock. Also, the table is written to quite often (roughly 8 insert or updates concurrently, as often as 240 times a minute), so an ITL deadlock seems like a strong possibility.

I've increased the INITRANS parameter of the table and it's indexes to 100 and increased the PCT_FREE on the table from 10 to 20 (then rebuilt the indexes), but the deadlocks are still occurring. The deadlock seems to happen most often during an update, but that could just be a coincidence, as I've only traced it a couple of times.

My questions are two-fold:
1) Is this actually an ITL deadlock?
2) If it is an ITL deadlock, what else can be done to avoid it?

---

It turns out that this was not an ITL deadlock issue at all, but rather an issue with un-indexed foreign keys. I discovered this thanks to dpbradley's answer, which clued me into the fact that it wasn't an ITL issue and prompted me to find out what the other causes of a deadlock with "no rows" might be.

Saransarangi

You might want to consider posting this on serverfault. My initial reaction was that's where this belongs, but there is a programming flavor to the question as well. At any rate you may catch another pair of eyes there that aren't here. – Revolving

Just FYI. If there are deadlocks occurring due to ITL space issues, altering INITRANS on the table will not fix the problem. That parameter will affect only new blocks, not existing blocks. The only way to correct that is to rebuild the segments (tables, indexes) with the proper INITRANS parameter. – Halyard

Answer 1

The best indication of ITL pressure is from the performance views:

select event, total_waits, time_waited, average_wait
 from v$system_event
 where event like 'enq: TX%'
 order by 2 desc;

shows TX contention waits, and

select OBJECT_NAME, SUBOBJECT_NAME, TABLESPACE_NAME, 
       OBJECT_TYPE, STATISTIC_NAME, VALUE
  from v$segment_statistics 
  where statistic_name = 'ITL waits'
  and value > 0
  order by value desc;

shows the tables and indexes involved.

(Like all v$ views, the results are from the point in time when the instance was started.)

If this shows that you do indeed have ITL waits, then the INITRANS and PCTFREE parameters are the main knobs to turn (but INITRANS = 100 sounds pretty high to me and these do cost space).

If ITL waits are not a problem, then the application code needs to be examined.

Cerumen

The first query shows 23000+ "enq: TX - contention" waits, but the second query only shows 1 ITL wait (on an index for a table that I have not seen a deadlock on). If I understand correctly, this seems to suggest that this is not actually an ITL deadlock? – Saransarangi

Yes, and I see from your edit that you've since discovered the underlying problem. Unindexed FK's can certainly be a locking problem - there are plenty of scripts floating around that will report the unindexed foreign keys in a schema. – Cerumen

Answer 2

The best option is to increase it as needed (start from default 10 and increment by 10). If you see reduction in ITL waits, you're set. Usually these related parameters are adjusted by trial and error both in Oracle and SQL Server. Adjusting these parameters in real-time won't be that much of an issue, unless the resource is extremely busy. You can use the following query to see after each increment, if the ITL waits either go away or is highly reduced:

SELECT t.OWNER, t.OBJECT_NAME, t.OBJECT_TYPE, t.STATISTIC_NAME, t.VALUE
  FROM v$segment_statistics t
  WHERE t.STATISTIC_NAME = 'ITL waits' AND t.VALUE > 0
  ORDER BY t.value desc;

We have carried out several tunings for Oracle deadlock scenarios due to ITL waits using this method. NOTE: Make sure the index is rebuilt, if the initrans is modified for indexes. Also ensure that statistics are not stale.

For a quick check SQL Tuning Advisor can be utilized to see the full state of the query/index and statistics.

Hermon