通过新云监控分析DB2锁等待、死锁和锁超时
简介
DB2的锁等待、死锁和锁超时会造成事务运行堵塞或回滚，严重影响数据库的性能。监控这类事件以及分析他们产生的原因是DB2 DBA必要的技能之一。在传统运维中，大多数情况DB2 DBA会通过db2pd、db2snapshot、db2top等方式分析当前正在发生的锁等待事件，但往往对死锁和锁超时的分析感到十分棘手，这是因为无法预知下一次的死锁和锁超时何时发生。在DB2数据库中为了捕捉死锁和锁超时，需要创建一个锁事件监视器。每当死锁和锁超时发生时，这个锁事件监视器便写一个条目，DBA可以在事件发生之后通过调用工具去分析捕获的数据。这些繁琐的操作给初级DBA或开发人员造成了不小障碍。本文介绍如何通过新云监控工具轻松监控DB2锁等待、死锁和锁超时的功能，并演示了如何使用它来分析锁等待、死锁和锁超时事件。
监控锁等待事件
在实际业务中，锁等待是影响并发性能的最重要因素，锁等待是针对正在发生的事务，这就需要DB2 DBA快速发现并定位到问题产生原因。
下面我们通过模拟DB2锁等待场景，来对比传统分析锁等待方法和通过新云监控分析锁等待的。
模拟锁等待场景
打开两个CLP窗口，分别连到TEST库中，然后分步执行以下SQL：
第一步CLP1中执行：
db2 +c "update TEST.CUSTOMER_INFO set CUS_IDCARD='999999999032661463' where CUS_IDCARD='999999999032661463'"
第二步CLP2中执行：
db2 +c "update TEST.CUSTOMER_INFO set CUS_IDCARD='1000000000765492011' where CUS_IDCARD='1000000000765492011'"
第三步CLP2中执行：
db2 +c "update TEST.CUSTOMER_INFO set CUS_IDCARD='999999999032661463' where CUS_IDCARD='999999999032661463'"
（注：此时第三步执行会出现锁等待现象）
使用传统方式定位锁等待事件
本节场景采用db2pd工具来分析锁等待事件，步骤如下：
清单1：定位锁等待事务关系
db2pd -d test -locks wait showlocks
Locks:
TranHdlType Mode Sts Owner Dur HoldCount rrIID TableNm SchemaNm
5 RowLock ..U W 8 0 0 0 CUSTOMER_INFO TEST
8 RowLock ..X G 8 2 0 0 CUSTOMER_INFO TEST
由清单1可以看出，事务句柄8持有X锁（Mode X代表Exclusive Lock，U代表Update Lock），事务句柄5处在等待状态中（Sts W代表wait，G代表granted），涉及到的表是TEST.CUSTOMER_INFO（注：清单中去除一些和本文不相关的信息）
清单2：定位应用程序句柄
db2pd -d test -transactions
Transactions:
AppHandl TranHdl Locks State Tflag Tflag2 Firstlsn
12153 5 5 READ 0x00000000 0x00000000 0
12152 8 4 READ 0x00000000 0x00000000 0
由清单2可以看出，事务句柄5、8分别对应的应用句柄12153、12512（注：清单中去除一些和本文不相关的信息）
清单3：定位应用程序句柄当前执行语句的锚点id和语句uid
db2pd -d test -application
Applications:
AppHandl NumAgents CoorEDUID Status C-AnchID C-StmtUID L-AnchID L-StmtUID Appid
12153 1 1116 Lock-wait 924 4 742 8 *LOCAL.db2v105.151224161151
12152 1 1125 UOW-Waiting 0 0 924 4 *LOCAL.db2v105.151224161147
由清单3以看出，应用句柄12153处在锁等待状态中，当前的锚点id（C-AnchID）是924，当前语句Uid（C-StmtUID）是4，这些 ID 可用于标识应用程序最近执行的 SQL 语句和应用程序当前执行的语句。（注：清单中去除一些和本文不相关的信息）
清单4：定位SQL语句
db2pd -db test -dynamic
Address AnchID StmtUID NumEnv NumVar NumRef NumExe Text
0x00007F3B8B1B8C20 924 4 1 1 3 3 update TEST.CUSTOMER_INFO set CUS_IDCARD='999999999032661463' where CUS_IDCARD='999999999032661463'
由清单4可以看出，锚点标识是924，语句Uid是4，的sql语句是update TEST.CUSTOMER_INFO set CUS_IDCARD='999999999032661463' where CUS_IDCARD='999999999032661463' 。（注：清单中去除一些和本文不相关的信息）
至此，通过以上4步操作，我们最终定位到锁等待相关应用程序和语句的信息。可以看到，整个操作过程相当繁琐，不仅需要记住大量的命令，还需要知道如何从这些命令的输出文本中摘取关键的信息。对于初级DBA或应用开发测试人员来说，不容易掌握。下面我们将介绍如何在新云监控工具中轻松监控DB2数据库锁等待。
使用新云监控定位锁等待事件
当有锁等待出现时，新云监控-锁等待页面（图1）会直接将锁等待信息展现出来，可以非常清晰的看出锁等待的逻辑关系：
应用句柄（12153）发出更新语句（update TEST.CUSTOMER_INFO set CUS_IDCARD='999999999032661463' where CUS_IDCARD='999999999032661463' ），应用句柄（12152）持有表（TEST.CUSTOMER_INFO）行级的排他锁，导致应用句柄（12153）发出的请求处在锁等待状态。
图1：锁等待信息
对于每个应用句柄，新云监控都提供超链接，点击后可展开该应用的详细信息，包括应用程序ID，连接时间，客户机地址等，方便用户轻松的将参与锁等待的应用句柄和实际应用程序关联起来
请求者应用句柄12153的详细信息（图2）
图2：应用句柄12153详细信息
持有者应用句柄12152的详细信息（图3）
图3：应用句柄12152详细信息
监控死锁和锁超时事件
模拟死锁场景
打开两个CLP窗口，分别连到TEST库中，然后分步执行以下SQL：
第一步CLP1中执行：
db2 +c "update TEST.CUSTOMER_INFO set CUS_IDCARD='999999999032661463' where CUS_IDCARD='999999999032661463'"
第二步CLP2中执行：
db2 +c "update TEST.CUSTOMER_INFO set CUS_IDCARD='1000000000765492011' where CUS_IDCARD='1000000000765492011'"
第三步CLP2中执行：
db2 +c "update TEST.CUSTOMER_INFO set CUS_IDCARD='999999999032661463' where CUS_IDCARD='999999999032661463'"
第四步CLP1中执行：
db2 +c "update TEST.CUSTOMER_INFO set CUS_IDCARD='1000000000765492011' where CUS_IDCARD='1000000000765492011'"
此时会发现在CLP1事务报错SQL0911N，reason code “2”，事务因为死锁而回滚。
db2 +c "update TEST.CUSTOMER_INFO set CUS_IDCARD='1000000000765492011' where CUS_IDCARD='1000000000765492011'"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0911N The current transaction has been rolled back because of a deadlock
or timeout. Reason code "2". SQLSTATE=40001
注：The reason codes are as follows:
8 The transaction was rolled back due to a deadlock.
使用传统方式分析死锁事件
在DB2数据库中，死锁和锁超时事件无法预测。对于已经发生的死锁和锁超时事件，为了捕捉它们的具体信息，需要创建一个锁事件监视器。注意开启锁事件监控器会带来一部分系统开销，建议在针对有需要分析死锁和锁超时事件的系统上开启。
DB2默认存在DB2DETAILDEADLOCK 事件监视器，并在实例启动时开启DB2DETAILDEADLOCK 事件监视器，当发生死锁时，需要通过db2 flush event monitor db2detaildeadlock命令将监控器内存的信息到磁盘中，通过db2evmon -db test -evm DB2DETAILDEADLOCK查看分析监控器内容，获取相关应用链接、参与者信息、回滚的应用等信息来进一步分析死锁产生的原因。
从DB2V9.7开始DB2DETAILDEADLOCK 事件监视器已不推荐使用，DB2V9.7开始建议使用LOCKING锁事件监控器来捕捉死锁和锁超时事件，分一下几步：
1、创建LOCKING类型锁事件监控器
2、激活锁事件监控
3、编译db2evmonfmt 工具
4、调用 db2evmonfmt 工具来格式化为死锁事件收集的数据
5、通过输出的内容进行分析
具体内容可以详见IBM development文章：” DB2 for Linux, UNIX, and Windows 的锁事件，第 3 部分: 使用 DB2 9.7 中的锁事件监控器来解决并发性问题”
http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1004lockeventmonitor/index.html
通过上述文章可以看到，虽然DB2提供了锁事件监视器，但是其使用过程十分繁琐，整个分析过程比前面提到的监控锁等待更加复杂。
使用新云监控分析死锁事件
新云监控在工具中集成了DB2死锁和锁超时监控功能，将传统的手动使用DB2锁事件监视器分析死锁和锁超时的步骤自动化，用户只需要在新云监控中开启死锁和锁超时开关，即可轻松监控和分析死锁和锁等待事件。通过新云监控可以清晰看到历史发生的死锁和锁超时次数。
新云监控默认记录了数据库中死锁和锁超时发生的次数，如图4所示：
图4：锁统计
如果还要进一步监控和分析死锁和锁超时的详细信息，则先需要通过新云监控创建锁事件监控器
创建锁事件监控器
通过新云监控工具添加LOCKING类型锁事件监控器，如图5所示。
图5：添加新监控器
添加的锁事件监控器会默认开启，如图6所示。
图6：创建后默认监控器名称NDTM_LOCKMON，监控器开关自动开启
发生死锁事件后，在死锁和锁超时的监控页面会记录已经发生死锁事件的详细信息（图7）
图7：监控器记录死锁事件
通过（图7）可以轻松看出死锁事件参与双方是应用程序名称（*LOCAL.db2v105.151222193822）和应用程序名称（*LOCAL.db2v105.151222194637），并且最终应用程序(*LOCAL.db2v105.151222193822)被DB2死锁探测器选中为受害者进行回滚。
点击死锁事件ID上的超链接，可以列出该事件对应死锁的详细信息（图8）
图8：死锁详细信息
我们将图8中的死锁详细信息里的关键信息摘出到清单5、6、7、8中，包括死锁参与者的关系、锁、应用程序和 SQL 语句等有关信息并标注出重点关注信息。
清单5：Deadlock Graph信息
Deadlock Graph
--------------
Total number of deadlock participants : 2
Participant that was rolled back : 2
Type of deadlock : local
Participant Participant Deadlock Member Application Handle
Requesting Lock Holding Lock
--------------- --------------- --------------- ------------------
1 2 0 01881
2 1 0 01588
清单5记录参与者总数和具体回滚的参与者ID，并解释参与者持有锁的关系。重点关注参与者总数（Total number of deadlock participants）、回滚的参与者ID（Participant that was rolled back）。
清单6：Participant No 1 requesting lock信息
Participant No 1 requesting lock
----------------------------------
Lock Name : 0x05000400050000000000000052
Lock wait start time : 2015-12-22-15.20.18.567864
Lock wait end time : 2015-12-22-15.20.25.861565
Lock Type : ROW
Lock Specifics : ROWID=5,DATA_PARTITION_ID=0,PAGEID=0
Lock Attributes : 00000000
Lock mode requested : Update
Lock mode held : Exclusive
Lock Count : 0
Lock Hold Count : 0
Lock rrIID : 0
Lock Status : Waiting
Lock release flags : 00000000
Tablespace TID : 5
Tablespace Name : MID_DATA
Table FID : 4
Table Schema : TEST
Table Name : CUSTOMER_INFO
清单6记录参与者请求锁相关信息，重点关注参与者（Participant No 1）请求锁等待的开始时间（Lock wait start time）和结束时间（Lock wait end time）、锁类型（Lock Type）、锁状态（Lock Status）和表名（Table Name）模式名（Table Schema）。
清单7：Attributes信息
Attributes Requester Requester
--------------------- ------------------------------ ------------------------------
Participant No 2 1
Application Handle 01588 01881
Application ID *LOCAL.db2v105.151222193822 *LOCAL.db2v105.151222194637
Application Name db2bp db2bp
Authentication ID DB2V105 DB2V105
Requesting AgentID 8829 8830
Coordinating AgentID 8829 8830
Agent Status UOW Executing UOW Executing
Application Action No action No action
Lock timeout value 0 0
Lock wait value 0 0
Workload ID 1 1
Workload Name SYSDEFAULTUSERWORKLOAD SYSDEFAULTUSERWORKLOAD
Service subclass ID 13 13
Service superclass SYSDEFAULTUSERCLASS SYSDEFAULTUSERCLASS
Service subclass SYSDEFAULTSUBCLASS SYSDEFAULTSUBCLASS
Current Request Execute Immediate Execute Immediate
TEntry state 1 1
TEntry flags1 00000000 00000000
TEntry flags2 00000200 00000200
Lock escalation no no
清单7记录应用程序相关信息，重点关注参与者ID信息（Participant No）、应用ID信息（Application ID）、代理状态（Agent Status）。
清单8：Current Activities of Participant No 2信息
Current Activities of Participant No 2
----------------------------------------
Activity ID : 2
Uow ID : 16
Package Name : SQLC2K26
Package Schema : NULLID
Package Version :
Package Token : AAAAAfAd
Package Sectno : 203
Reopt value : none
Incremental Bind : no
Eff isolation : CS
Eff degree : 0
Actual degree : 1
Eff locktimeout : -1
Stmt first use : 2015-12-22-15.20.11.968756
Stmt last use : 2015-12-22-15.20.11.968756
Stmt unicode : no
Stmt query ID : 0
Stmt nesting level : 0
Stmt invocation ID : 0
Stmt source ID : 0
Stmt pkgcache ID : 3186865733680
Stmt type : Dynamic
Stmt operation : DML, Insert/Update/Delete
Stmt no : 1
Stmt text : update TEST.CUSTOMER_INFO set CUS_IDCARD='1000000000765492011' where CUS_IDCARD='1000000000765492011'
Past Activities of Participant No 2
-------------------------------------
Activities not available
清单8记录回滚参与者（Participant No 2）的信息，重点关注回滚的SQL语句（Stmt text）信息。
模拟锁超时场景
打开两个CLP窗口，分别连到TEST库中，然后分步执行以下SQL：
第一步CLP1、CLP2中设置当前回话锁等待超时时间为10秒：
db2 "set current lock timeout 10"
第二步CLP1中执行：
db2 +c "update TEST.CUSTOMER_INFO set CUS_IDCARD='999999999032661463' where CUS_IDCARD='999999999032661463'"
第三步CLP2中执行：
db2 +c "update TEST.CUSTOMER_INFO set CUS_IDCARD='1000000000765492011' where CUS_IDCARD='1000000000765492011'"
第四步CLP1中执行：
db2 +c "update TEST.CUSTOMER_INFO set CUS_IDCARD='999999999032661463' where CUS_IDCARD='999999999032661463'"
此时会发现在CLP1事务报错SQL0911N，reason code “68”，事务因为锁超时而回滚。
db2 +c "update TEST.CUSTOMER_INFO set CUS_IDCARD='999999999032661463' where CUS_IDCARD='999999999032661463'"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0911N The current transaction has been rolled back because of a deadlock
or timeout. Reason code "68". SQLSTATE=40001
注：The reason codes are as follows:
68 The transaction was rolled back due to a lock timeout.
使用传统方式分析锁超时事件
传统方式分析锁超时需要分析db2diag.log日志中锁超时事件的时间点，proceesID、thread ID等信息结合db2snapshot或db2pd来分析，DB2V9.7以后建议通过创建锁事件监控器来分析锁超时，分一下几步：
6、创建LOCKING类型锁事件监控器
7、激活锁事件监控
8、编译db2evmonfmt 工具
9、调用 db2evmonfmt 工具来格式化为锁超时事件收集的数据
10、通过输出的内容进行分析
具体内容可以详见IBM development文章：” DB2 for Linux, UNIX, and Windows 的锁事件，第 3 部分: 使用 DB2 9.7 中的锁事件监控器来解决并发性问题”
http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1004lockeventmonitor/index.html
通过上述文章可以看到，虽然DB2提供了事件监视器，但是其使用过程十分繁琐，整个分析过程比前面提到的监控锁等待更加复杂。
使用新云监控分析锁超时事件
当锁超时事件发生后，通过监控页面（图9）可以看到锁超时相关信息，点击事件ID可以弹出锁超时详细信息（图10）。
图9监控器发现有锁超时事件
图10锁超时详细信息
在锁超时的详细信息中，可以轻松看到请求者和拥有者的关系、及锁、应用程序和 SQL 语句有关信息，最终确定锁超时事件的原因：应用程序名称（*LOCAL.db2v105.151222194637）请求在（TEST..CUSTOMER_INFO）表上更新数据，执行的语句是（update TEST.CUSTOMER_INFO set CUS_IDCARD='999999999032661463' where CUS_IDCARD='999999999032661463'），但应用程序名称(*LOCAL.db2v105.151222193822) 已经拥有表（TEST..CUSTOMER_INFO）上要修改数据的行锁，最终应用程序名称（*LOCAL.db2v105.151222194637）等待10秒后回滚，以下清单9、10、11标记出锁超时详细信息中重点关注的内容。
清单9：Participant No 1 requesting lock信息
Participant No 1 requesting lock
----------------------------------
Lock Name : 0x05000400050000000000000052
Lock wait start time : 2015-12-22-18.24.57.019662
Lock wait end time : 2015-12-22-18.25.07.024162
Lock Type : ROW
Lock Specifics : ROWID=5,DATA_PARTITION_ID=0,PAGEID=0
Lock Attributes : 00000000
Lock mode requested : Update
Lock mode held : Exclusive
Lock Count : 0
Lock Hold Count : 0
Lock rrIID : 0
Lock Status : Waiting
Lock release flags : 00000000
Tablespace TID : 5
Tablespace Name : MID_DATA
Table FID : 4
Table Schema : TEST
Table Name : CUSTOMER_INFO
清单9记录参与者请求锁的相关信息，重点关注参与者（Participant No 1）请求锁等待的开始时间（Lock wait start time）和结束时间（Lock wait end time）、锁类型（Lock Type）、锁状态（Lock Status）和表名（Table Name）模式名（Table Schema）。
清单10：Attributes信息
Attributes Requester Owner
--------------------- ------------------------------ ------------------------------
Participant No 1 2
Application Handle 01881 01588
Application ID *LOCAL.db2v105.151222194637 *LOCAL.db2v105.151222193822
Application Name db2bp db2bp
Authentication ID DB2V105 DB2V105
Requesting AgentID 8830 8829
Coordinating AgentID 8830 8829
Agent Status UOW Executing UOW Waiting
Application Action No action No action
Lock timeout value 10 0
Lock wait value 0 0
Workload ID 1 1
Workload Name SYSDEFAULTUSERWORKLOAD SYSDEFAULTUSERWORKLOAD
Service subclass ID 13 13
Service superclass SYSDEFAULTUSERCLASS SYSDEFAULTUSERCLASS
Service subclass SYSDEFAULTSUBCLASS SYSDEFAULTSUBCLASS
Current Request Execute Immediate Execute Immediate
TEntry state 1 1
TEntry flags1 00000000 00000000
TEntry flags2 00000200 00000200
Lock escalation no no
清单10记录拥有者和请求者信息，可以直观看出应用程序、代理状态和超时时间等信息，重点关注参与者ID信息（Participant No）、应用ID信息（Application ID）、代理状态（Agent Status）。
清单11：Current Activities of Participant No 1信息
Current Activities of Participant No 1
----------------------------------------
Activity ID : 2
Uow ID : 20
Package Name : SQLC2K26
Package Schema : NULLID
Package Version :
Package Token : AAAAAfAd
Package Sectno : 203
Reopt value : none
Incremental Bind : no
Eff isolation : CS
Eff degree : 0
Actual degree : 1
Eff locktimeout : 10
Stmt first use : 2015-12-22-18.24.57.019501
Stmt last use : 2015-12-22-18.24.57.019501
Stmt unicode : no
Stmt query ID : 0
Stmt nesting level : 0
Stmt invocation ID : 0
Stmt source ID : 0
Stmt pkgcache ID : 3968549781551
Stmt type : Dynamic
Stmt operation : DML, Insert/Update/Delete
Stmt no : 1
Stmt text : update TEST.CUSTOMER_INFO set CUS_IDCARD='999999999032661463' where CUS_IDCARD='999999999032661463'
清单11记录当前活动的参与者信息，重点关注参与者（Participant No 1）的sql语句（Stmt text）。
总结
本文以DB2锁等待、死锁和锁超时为例，为大家介绍了新云监控针对这三种锁事件的监控功能，同时和传统分析方式进行对比，从对比结果来看，DBA通过新云监控会及时分析出引起这三种锁事件的应用程序和相关的SQL语句，比传统分析方式节约了大量的时间花销，提高了运维DB2数据库整体效率。
关于新云监控其他功能体验，欢迎大家下载试用新云监控：http://www.newdt.cn/list-31.html
访问新云监控在线演示：http://newdtcn.kmdns.net:8090/db2mon/login.html
了解新数科技更多资讯请关注：http://www.newdt.cn
附件：
通过新云监控分析DB2锁等待、死锁和锁超时.docx (177.42 KB)
