【故障处理】队列等待之TX - allocate ITL entry引起的死锁处理

一.1  前言部分

一.1.1  导读和注意事项

各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：

① enq: TX - allocate ITL entry等待事件的解决

② 一般等待事件的解决办法

③ 队列等待的基本知识

④ ITL死锁解决

⑤ ITL死锁模拟

⑥ Merge语句的非关联形式的查询优化

  Tips：

① 本文在ITpub（http://blog.itpub.net/26736162）、博客园(http://www.cnblogs.com/lhrbest)和微信公众号（xiaomaimiaolhr）有同步更新

② 文章中用到的所有代码，相关软件，相关资料请前往小麦苗的云盘下载（http://blog.itpub.net/26736162/viewspace-1624453/）

③ 若文章代码格式有错乱，推荐使用搜狗、360或QQ浏览器，也可以下载pdf格式的文档来查看，pdf文档下载地址：http://blog.itpub.net/26736162/viewspace-1624453/，另外itpub格式显示有问题，可以去博客园地址阅读

④ 本篇BLOG中命令的输出部分需要特别关注的地方我都用灰色背景和粉红色字体来表示，比如下边的例子中，thread 1的最大归档日志号为33，thread 2的最大归档日志号为43是需要特别关注的地方；而命令一般使用黄色背景和红色字体标注；对代码或代码输出部分的注释一般采用蓝色字体表示。

本文如有错误或不完善的地方请大家多多指正，ITPUB留言或QQ皆可，您的批评指正是我写作的最大动力。

一.2  故障分析及解决过程

一.2.1  故障环境介绍

一.2.2  故障发生现象及故障分析解决

早上刚来上班，同事就发了一个SQL过来，说是有锁，然后我就查了查系统里的锁，结果一个锁都没得。好吧，还是得干点事的，先看看SQL语句：

MERGE INTO TLHR.TLHRBOKBAL S

USING (SELECT A.BOOKACCOUNT AS BOOKACCOUNT,

              (A.CURRBALANCE + NVL(B.BAL, 0.00)) AS BANKAMT

         FROM TLHR.TLHRBOKBAL_TMP A,

              (SELECT T1.BOOKACCOUNT AS BOOKACCOUNT,

                      SUM(DECODE(T1.DCFLAG, ‘D‘, -T1.AMT, ‘C‘, T1.AMT, 0)) AS BAL

                 FROM TLHR.TLHRBOKBALJN T1

                WHERE T1.BOOKACCOUNT LIKE ‘13500000%‘

                  AND T1.TRANDATE = ‘20150901‘

                  AND (T1.REASON IN (‘1‘, ‘2‘) OR

                      (T1.REASON = ‘0‘ AND T1.ONLINEFLAG = ‘1‘))

                GROUP BY T1.BOOKACCOUNT) B

        WHERE A.BOOKACCOUNT = B.BOOKACCOUNT(+)

          AND A.BOOKACCOUNT LIKE ‘13500000%‘) T

ON (S.BOOKACCOUNT = T.BOOKACCOUNT)

WHEN MATCHED THEN

  UPDATE

     SET S.LASTBALANCE = T.BANKAMT,

         S.CURRBALANCE = T.BANKAMT,

         S.DEBITAMT    = 0.00,

         S.CREDITAMT   = 0.00;

看起来是一个MERGE语句，按照小麦苗以前的经验，这一类的SQL最好是修改为MERGE的非关联形式比较好，我们先看看执行计划有没有问题：

先找到SQL_ID为53qv858pwwwwb：

SELECT a.ELAPSED_TIME,a.EXECUTIONS,a.* FROM v$sql a WHERE a.SQL_TEXT LIKE ‘%MERGE INTO TLHRBOKBAL S%‘ AND A.SQL_TEXT LIKE ‘%13500000%‘  ;

查询历史执行计划：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(SQL_ID => ‘53qv858pwwwwb‘ )) ;

结果发现很多的死锁，拿到相关的文件，看到如下一段：

果然，产生死锁的SQL还是上边分析优化的SQL,其中会话信息为：（332，47221），我们去DBA_HIST_ACTIVE_SESS_HISTORY视图里查询：

SELECT D.SQL_ID, D.CURRENT_OBJ#, D.EVENT, COUNT(1)

  FROM DBA_HIST_ACTIVE_SESS_HISTORY D

 WHERE D.SAMPLE_TIME BETWEEN

       TO_DATE(‘2016-09-01 18:25:00‘, ‘YYYY-MM-DD HH24:MI:SS‘) AND

       TO_DATE(‘2016-09-01 18:45:00‘, ‘YYYY-MM-DD HH24:MI:SS‘)

   AND D.BLOCKING_SESSION_STATUS = ‘VALID‘

   AND D.SESSION_ID = 332

   AND D.SESSION_SERIAL# = 47221

 GROUP BY D.SQL_ID, D.CURRENT_OBJ#, D.EVENT;

可以看到该会话的等待事件是enq: TX - allocate ITL entry。可以猜测是由于ITL事务槽引起的问题。

SELECT DISTINCT D.BLOCKING_SESSION, D.BLOCKING_SESSION_SERIAL#, D.SQL_ID

  FROM DBA_HIST_ACTIVE_SESS_HISTORY D

 WHERE D.SAMPLE_TIME BETWEEN

       TO_DATE(‘2016-09-01 18:25:00‘, ‘YYYY-MM-DD HH24:MI:SS‘) AND

       TO_DATE(‘2016-09-01 18:45:00‘, ‘YYYY-MM-DD HH24:MI:SS‘)

   AND D.EVENT = ‘enq: TX - allocate ITL entry‘

   AND D.BLOCKING_SESSION_STATUS = ‘VALID‘

   AND D.SESSION_ID = 332

   AND D.SESSION_SERIAL# = 47221;

可以看出会话（332，47221）共阻塞了3个会话，由于有死锁，那么我们看看上边查询出来的3个会话阻塞了哪些会话：

SELECT DISTINCT D.INSTANCE_NUMBER,

                D.SESSION_ID,

                D.SESSION_SERIAL#,

                D.BLOCKING_INST_ID,

                D.BLOCKING_SESSION,

                D.BLOCKING_SESSION_SERIAL#,

                D.SQL_ID

  FROM DBA_HIST_ACTIVE_SESS_HISTORY D

 WHERE D.SAMPLE_TIME BETWEEN

       TO_DATE(‘2016-09-01 18:25:00‘, ‘YYYY-MM-DD HH24:MI:SS‘) AND

       TO_DATE(‘2016-09-01 18:45:00‘, ‘YYYY-MM-DD HH24:MI:SS‘)

   AND D.EVENT = ‘enq: TX - allocate ITL entry‘

   AND D.BLOCKING_SESSION_STATUS = ‘VALID‘

   AND ((D.SESSION_ID = 332 AND D.SESSION_SERIAL# = 47221) OR

       (D.SESSION_ID = 2602 AND D.SESSION_SERIAL# = 4343) OR

       (D.SESSION_ID = 2995 AND D.SESSION_SERIAL# = 46891) OR

       (D.SESSION_ID = 1894 AND D.SESSION_SERIAL# = 30761));

可以看到，1894和2602相互阻塞（绿色表示），332和2602相互阻塞（红色表示），2995和332相互阻塞（粉色表示），这么多的相互阻塞就产生了死锁，这里由于SQL_ID不同，而且产生的等待事件是enq: TX - allocate ITL entry，所以推测出生成的是ITL死锁。

解决这类问题就是增大ini_trans和PCT_FREE的值。

SELECT * FROM DBA_TABLES D WHERE D.TABLE_NAME = ‘TLHRBOKBAL‘;

可以看到，ini_trans和PCT_FREE的值都是默认的，太小了，根据MOS（Troubleshooting waits for ‘enq: TX - allocate ITL entry‘ (Doc ID 1472175.1)  地址：http://blog.itpub.net/26736162/viewspace-2124531/）我们需要修改该参数，SQL如下：

ALTER TABLE TLHR.TLHRBOKBAL PCTFREE 20  INITRANS 16 ;

ALTER TABLE TLHR.TLHRBOKBAL MOVE NOLOGGING PARALLEL 12;

ALTER TABLE TLHR.TLHRBOKBAL LOGGING NOPARALLEL;

ALTER INDEX TLHR.PK_TLHRBOKBAL  REBUILD PCTFREE 20 INITRANS 16 NOLOGGING PARALLEL 12;

ALTER INDEX TLHR.PK_TLHRBOKBAL LOGGING NOPARALLEL;

由于表里有3000W的数据量，开了并行，本来我预估的是5分钟，结果move表的时候10秒都不到还是比较快的。

调整之后的值：

SELECT * FROM DBA_TABLES D WHERE D.TABLE_NAME = ‘TLHRBOKBAL‘;

SELECT * FROM dba_indexes d WHERE d.index_name=‘PK_TLHRBOKBAL‘;

修改已经生效，接下来就看开发那边是否还报死锁的错误，这个等待需要明天看了。

终于等到第2天了，看来没有报错了：

一.3  这里我们模拟一个ITL死锁

有人的地方就有江湖，有资源阻塞的地方就可能有死锁。所谓死锁： 是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。其最常见的死锁的类型分为：行级锁（row-level locks）和块级锁（block-level locks），这里的行级锁其实就是指的ITL死锁。有关死锁的问题，有许多需要介绍的，这篇blog主要是故障处理，所以这里我们模拟一个ITL死锁产生的过程即可，后边我会系统的发一次有关死锁的内容，还有ITL的内容，希望大家持续关注小麦苗的微信公众号(xiaomaimiaolhr)。

实验部分：

实验的设计过程来源于网络！

我们首先创建一张表T_ITL_LHR，这里指定PCTFREE为0，INITRANS为1，就是为了观察到ITL的真实等待情况，然后我们给这些块内插入数据，把块填满，让它不能有空间分配。

我们检查数据填充的情况：

可以发现，这2000条数据分布在3个块内部，其中有2个块(94953和94954)填满了，一个块（94955）是半满的。因为有2个ITL槽位，我们需要拿2个满的数据块，4个进程来模拟ITL死锁：

会话1：

会话2：

会话3：

会话4：

这个时候系统不存在阻塞，

SELECT NVL(A.SQL_ID, A.PREV_SQL_ID) SQL_ID,

       A.BLOCKING_SESSION,

       A.SID,

       A.SERIAL#,

       A.LOGON_TIME,

       A.EVENT

  FROM GV$SESSION A

 WHERE A.SID IN (19, 79,78,139)

 ORDER BY A.LOGON_TIME;

以上4个进程把2个不同块的4个ITL槽位给消耗光了，现在的情况，就是让他们互相锁住，达成死锁条件，回到会话1，更新块94954，注意，以上4个操作，包括以下的操作，更新的根本不是同一行数据，主要是为了防止出现的是行锁等待。

会话1：

UPDATE T_ITL_LHR SET A=A

WHERE DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)=94954

AND DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)=3;

会话1出现了等待。

会话3：

UPDATE T_ITL_LHR SET A=A

WHERE DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)=94953

AND DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)=3;

会话3发现出现了等待。

我们查询阻塞的具体情况：

SELECT NVL(A.SQL_ID, A.PREV_SQL_ID) SQL_ID,

       A.BLOCKING_SESSION,

       A.SID,

       A.SERIAL#,

       A.LOGON_TIME,

       A.EVENT

  FROM GV$SESSION A

 WHERE A.SID IN (19, 79,78,139)

 ORDER BY A.LOGON_TIME;

可以看到，会话1被会话4阻塞了，会话3被会话2阻塞了。

注意，如果是9i，在这里就报死锁了，但是在10g里面，这个时候，死锁是不会发生的，因为这里的会话1还可以等待会话4释放资源，会话3还可以等待会话2释放资源，只要会话2与会话4释放了资源，整个环境又活了，那么我们需要把这两个进程也塞住。

会话2，注意，我们也不是更新的同一行数据：

UPDATE T_ITL_LHR SET A=A

WHERE DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)=94954

AND DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)=4;

会话2出现了等待，具体阻塞情况：

我做了几次实验，会话2执行完SQL后，会话3到这里就报出了死锁，但有的时候并没有产生死锁，应该跟系统的阻塞顺序有关，若没有产生死锁，我们可以继续会话4的操作。

SYS@lhrdb21> UPDATE T_ITL_LHR SET A=A

  2   WHERE DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)=94953

  3  AND DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)=3;

UPDATE T_ITL_LHR SET A=A

       *

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

会话4，注意，我们也不是更新的同一行数据：

UPDATE T_ITL_LHR SET A=A

WHERE DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)=94953 

AND DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)=4;

会话4发现出现了等待。

虽然，以上的每个更新语句，更新的都不是同一个数据行，但是，的确，所有的进程都被阻塞住了，那么，死锁的条件也达到了，等待一会（这个时间有个隐含参数来控制的：_lm_dd_interval），我们可以看到，会话2出现提示，死锁：

SYS@lhrdb21> UPDATE T_ITL_LHR SET A=A

  2   WHERE DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)=94954

  3  AND DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)=4;

UPDATE T_ITL_LHR SET A=A

       *

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

报出死锁之后的阻塞情况：

我们可以在会话2上继续执行步骤三中的SQL，依然会产生死锁。生成死锁后，在告警日志中有下边的语句：

Fri Sep 09 17:56:55 2016

Global Enqueue Services Deadlock detected. More info in file

/oracle/app/oracle/diag/rdbms/lhrdb2/lhrdb21/trace/lhrdb21_lmd0_17039368.trc.

其中的内容有非常经典的一段Global Wait-For-Graph(WFG)：

*** 2016-09-09 17:48:22.216

Submitting asynchronized dump request [1c]. summary=[ges process stack dump (kjdglblkrdm1)].

Global blockers dump end:-----------------------------------

Global Wait-For-Graph(WFG) at ddTS[0.395] :

BLOCKED 0x700010063d59b90 3 wq 2 cvtops x1001 TX 0x7000b.0xa67(ext 0x2,0x0)[1002-0029-00008387] inst 1

BLOCKER 0x700010063c6d268 3 wq 1 cvtops x28 TX 0x7000b.0xa67(ext 0x2,0x0)[1002-002D-00003742] inst 1

BLOCKED 0x700010063d5adc8 3 wq 2 cvtops x1 TX 0x30021.0x848(ext 0x2,0x0)[1002-002D-00003742] inst 1

BLOCKER 0x700010063d5a4b8 3 wq 1 cvtops x28 TX 0x30021.0x848(ext 0x2,0x0)[1002-0029-00008387] inst 1

至于每个参数到底是什么意思，目前还没有去研究，等待大神可以无偿解释一下。至于如何解决ITL产生的死锁，无非就是增大表和索引的initrans和PCT_FREE的值，可以参考本BLOG中的ITL死锁问题解决。

该实验过程可能有点复杂，小麦苗画了个图来说明整个实验过程：

一.4  与文章有关的相关连接

[转]:深入研究ITL阻塞与ITL死锁 http://blog.itpub.net/26736162/viewspace-2124539/

  About Me

..........................................................................................................................................................................................................                        

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 本文在itpub（http://blog.itpub.net/26736162）、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号（xiaomaimiaolhr）上有同步更新，推荐pdf文件阅读

● QQ群：230161599 微信群：私聊

● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2124771/ 博客园地址：http://www.cnblogs.com/lhrbest/articles/5858306.html

● 本文pdf版：http://yunpan.cn/cdEQedhCs2kFz （提取码：ed9b）

● 小麦苗分享的其它资料：http://blog.itpub.net/26736162/viewspace-1624453/

● 联系我请加QQ好友(642808185)，注明添加缘由

● 于 2016-09-05 09:00~2016-09-09 19:00 在中行完成

● 【版权所有，文章允许转载，但须以链接方式注明源地址，否则追究法律责任】

..........................................................................................................................................................................................................

长按识别二维码或微信客户端扫描下边的二维码来关注小麦苗的微信公众号：xiaomaimiaolhr,学习最实用的数据库技术。

理】队列等待之TX - allocate