views:

208

answers:

1

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 and 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.

+1  A: 

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.

dpbradley
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?
Allan
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.
dpbradley