识别和解决 Oracle ITL 死锁
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2899087/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Identifying and Resolving Oracle ITL Deadlock
提问by Allan
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.
我有一个 Oracle DB 包，它经常导致我认为是 ITL（感兴趣的事务列表）死锁。跟踪文件的相关部分如下。
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.
这个表上没有位图索引，所以这不是原因。据我所知，缺少“等待的行”加上“等待”列中的“S”可能表明这是 ITL 死锁。此外，该表经常被写入（大约同时进行 8 次插入或更新，每分钟 240 次），因此 ITL 死锁似乎很有可能。
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.
我已将表的 INITRANS 参数及其索引增加到 100，并将表上的 PCT_FREE 从 10 增加到 20（然后重建索引），但死锁仍在发生。僵局似乎最常发生在更新期间，但这可能只是巧合，因为我只跟踪了几次。
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?
我的问题有两个：
1）这实际上是 ITL 僵局吗？
2）如果是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 死锁问题，而是未索引外键的问题。由于 dpbradley 的回答，我发现了这一点，这让我意识到这不是 ITL 问题，并促使我找出“无行”死锁的其他原因可能是什么。
采纳答案by dpbradley
The best indication of ITL pressure is from the performance views:
ITL 压力的最佳指示来自性能视图：
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
显示 TX 争用等待，以及
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.)
（与所有v$视图一样，结果来自实例启动的时间点。）
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 等待，那么 INITRANS 和 PCTFREE 参数是要转动的主要旋钮（但 INITRANS = 100 对我来说听起来非常高，而且这些确实会占用空间）。
If ITL waits are not a problem, then the application code needs to be examined.
如果 ITL 等待没有问题，则需要检查应用程序代码。
回答by Applaya Technologies
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:
最好的选择是根据需要增加它（从默认 10 开始并增加 10）。如果您看到 ITL 等待时间减少，那么您已准备就绪。通常这些相关参数在 Oracle 和 SQL Server 中都是通过反复试验来调整的。除非资源非常繁忙，否则实时调整这些参数不会有太大问题。您可以使用以下查询在每次增量后查看 ITL 等待是否消失或大幅减少：
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.
由于使用此方法的 ITL 等待，我们对 Oracle 死锁场景进行了多次调整。注意：如果为索引修改了 inittrans，请确保重建索引。还要确保统计数据不陈旧。
For a quick check SQL Tuning Advisor can be utilized to see the full state of the query/index and statistics.
为了快速检查，可以使用 SQL Tuning Advisor 查看查询/索引和统计信息的完整状态。
