识别和解决Oracle ITL死锁 [英] Identifying and Resolving Oracle ITL Deadlock

问题描述

我有一个Oracle DB软件包，该软件包通常导致我认为是ITL(感兴趣的事务列表)的死锁.跟踪文件的相关部分如下.

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

此表上没有位图索引，所以这不是原因.据我所知，在Waiter waits列中缺少"Row waited on"加上"S"可能表明这是一个ITL僵局.另外，该表的写入频率非常高(大约8次并发插入或更新，每分钟最多240次)，因此ITL死锁的可能性很大.

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.

我已将表的INITRANS参数及其索引增加到100，并将表上的PCT_FREE从10增加到20(然后重建了索引)，但是仍然出现死锁.僵局似乎最经常发生在更新过程中，但这可能只是一个巧合，因为我只追踪了几次.

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.

我的问题有两个方面:
1)这实际上是一个ITL僵局吗?
2)如果是ITL死锁，还可以采取其他措施来避免它?

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?

事实证明，这根本不是一个ITL死锁问题，而是一个带有未索引外键的问题.我发现这要归功于dpbradley的回答，这使我意识到这不是ITL问题，并促使我找出导致无行"的死锁的其他原因.

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.

推荐答案

ITL压力的最佳指示是从性能视图:

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;

显示TX争用等待，并且

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;

显示涉及的表和索引.

(就像所有v$视图一样，结果来自实例启动时的时间点.)

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

如果这表明您确实有ITL等待，那么INITRANS和PCTFREE参数是要打开的主要旋钮(但是INITRANS = 100对我来说听起来很高，这些确实需要一定的空间).

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

如果ITL等待不是问题，则需要检查应用程序代码.

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

这篇关于识别和解决Oracle ITL死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！