如何在DB2 UDB中正确的监控死锁
前言：这篇文章通过详细的实例阐述了如何在DB2 UDB 中监控死锁的发生。在DB2 UDB中有两种类型的监控器：快照监控器和事件监控器。快照顾名思义就是数据库连续状态下的一个切面，通过快照监控器，你可以很方便地查看当前连接的应用程序，当前等待的锁，当前的死锁，以及正在执行的SQL语句，同时你可以查看缓冲区，表和表空间的用法。如果保存历史数据，并且能够做出比较，对于分析数据库的并发性能有很大的帮助。
但是我们并不能预测什么时候发生死锁，所以如果有一个后台程序能够一直监控数据库的活动，记录下所有的死锁事件，这对于数据库管理员来说是非常重要的。DB2 UDB提供了事件监控器。通过不遗漏地获得一段时间内所有的数据库事件(在本文中只关心其中的死锁事件)，事件监控器提供了一种可以分析历史数据(本文的重点)，预测将来趋势的可能。DB2 UDB同时提供了DB2 Performance Expert (DB2/PE) 或者类似的程序用来生成分析报表，不过这已经超出了本文的范畴。
常用术语
锁是控制应用程序并发的数据库软件机制，锁用来防止以下情况的发生：
1. 丢失以前更新
2. 不可重复读取
3. 访问未提交数据
锁的模式包括共享锁和排他锁，共享锁允许其他程序读取已经被其他共享锁占用的资源，所以也叫读锁，排他锁意味着在释放资源以前其他的应用程序无法访问同一资源，所以也叫写锁。此外，DB2 UDB 还提供了不同的锁级别，不同的应用程序可能会要求访问不同范围的数据，锁级别有利于充分利用系统资源，提高系统性能。若一个应用程序请求一个锁，而该锁被另外一个应用程序所使用且不能共享，DB2 UDB 就会挂起前一个应用程序。锁升级就是当LOCKLIST (LOCKLIST代表锁能够占用的内存空间) 耗尽或者一个应用程序所拥有的锁大于MAXLOCKS*LOCKLIST的时候(MAXLOCKS 代表应用程序所拥有的锁占所空间的百分比)，DB2 UDB 就试图把几个行级别的锁合并为一个表级别的锁，从而释放锁空间。虽然锁升级本身并不耗费多少时间，但是锁住整个表通常会大大地降低并发性能。
当应用程序处于挂起状态超过了一段规定的时间后，DB2 UDB就会自动中止这个应用程序，同时会向SQLCA发送描述性的错误信息。当两个或者更多的应用程序都持有另外一个应用程序所需资源上的锁，没有这些资源，那些应用程序都无法继续完成其工作的时候，就会发生死锁。
在DLCHKTIME超时之后，DB2 UDB会中止发生死锁的某个应用程序(通常为所做工作最少的那个应用程序)，这会释放这个应用程序所持有的所有的锁，并允许别的应用程序继续工作，DB2 UDB 将向被终止的应用程序的SQLCA发送描述性的错误信息。LOCKTIMEOUT 指定一个应用程序被允许的锁等待的时间，这将避免全局的死锁从而导致整个应用崩溃。如果LOCKTIMEOUT 的值为-1，应用程序会等待直到该锁被释放或者发生一个死锁。
事件监控器
事件监控器用来收集当一个数据库事件发生时所关联的应用程序的信息。这里的事件指，连接，死锁，声明和事务。你可以定义你想监控的事件类型的监控器。比如说，一个死锁监控器就是用来监控死锁的发生。
在DB2 UDB 中存在两种和死锁有关的事件类型：
DEADLOCKS
记录简单的应用程序信息。
DEADLOCKS WITH DETAILS
记录所有复杂的信息，包括应用程序、执行语句声明以及死锁的详细信息。但是使用这种事件监控器会因为需要得到大量额外的信息而降低系统的性能。
如何监控死锁
为了详细说明事件监控器在死锁监控中的用途，我引入了一个简单的死锁场景来触发一个死锁，在随后的章节，我会告诉读者如何分析监控结果以及根据结果来避免死锁的发生。
这里我们需要至少三个应用程序来调用DB2 CLI，一个用来监控死锁的发生，另外两个用来产生死锁。我们可以使用DB2 UDB 安装时附带的SAMPLE数据库。
1. 首先建立一个死锁事件监控器
Session Monitor
db2 connect to sampledb2 "create event monitor
dlmon for tables, deadlocks with details write
to file 'C:dlmon'"mkdir C:dlmondb2 "
set event monitor dlmon state 1"
2. 用另外两个应用程序来产生一个死锁
Session A
db2 connect to sampledb2 +c "insert into employee values('000350', 'Truman', 'I', 'Jiang',
'B00', '5892','1999-02-21', 'Engineer', 19, 'M',
'1978-06-17', 60000, 2000, 6000)"
现在应用程序A就拥有了一个EMPLOYEE表的行级别的排他锁
(注: +c 代表不自动提交SQL语句，DB2 中 autocommit 是缺省设置，也可以通过 db2 update command options using c off 关闭该缺省选项。)
Session B
db2 connect to sampledb2 +c "insert into project
values('AD3300', 'Dead Lock Monitor', 'B00', '000350',
7.00, '1982-07-21', '1983-02-03', 'AD3111')"
现在应用程序B就拥有了一个PROJECT表的行级别的排他锁
Session A
db2 +c "select projname from project"
应用程序A需要PROJECT表上所有行的共享锁，但是因为PROJECT表正在被应用程序B以排他锁的形式独占，这时候应用程序1就进入一个锁等待的状态。
Session B
db2 +c "select firstnme from employee"
应用程序B也进入一个锁等待的状态。此时就出现了一个死锁状态。
3. 两个本身处于锁等待并且占有资源的应用程序互相等待另外一方所持有的资源，这时候Session A和Session B就出现了死锁状态，这种状态一直会延续直到死锁检查器(超出DLCHKTIME时间以后)检查出一个死锁并且回滚其中的一个事务。
Session B
SQLN0991N 因为死锁或者超时，当前事务已经被回滚。原因码为 "2". SQLSTATE=40001这时候死锁事件监控器就会记录这个死锁，同时应用程序A可以完成他的工作。
Session A
PROJNAME----------……20 条记录已选择
Session A
db2 connect reset
Session B
db2 connect reset
4. 通过 db2evmon 工具可以获得死锁信息的日志，并且把日志文件导入到本地机器的文件系统当中。在下面一节，我们将详细分析导出的日志文件。
Session Monitordb2 connect resetdb2evmon
-path c:dlmon > c:dlmondllog1.txt
但是我们并不能预测什么时候发生死锁，所以如果有一个后台程序能够一直监控数据库的活动，记录下所有的死锁事件，这对于数据库管理员来说是非常重要的。DB2 UDB提供了事件监控器。通过不遗漏地获得一段时间内所有的数据库事件(在本文中只关心其中的死锁事件)，事件监控器提供了一种可以分析历史数据(本文的重点)，预测将来趋势的可能。DB2 UDB同时提供了DB2 Performance Expert (DB2/PE) 或者类似的程序用来生成分析报表，不过这已经超出了本文的范畴。
常用术语
锁是控制应用程序并发的数据库软件机制，锁用来防止以下情况的发生：
1. 丢失以前更新
2. 不可重复读取
3. 访问未提交数据
锁的模式包括共享锁和排他锁，共享锁允许其他程序读取已经被其他共享锁占用的资源，所以也叫读锁，排他锁意味着在释放资源以前其他的应用程序无法访问同一资源，所以也叫写锁。此外，DB2 UDB 还提供了不同的锁级别，不同的应用程序可能会要求访问不同范围的数据，锁级别有利于充分利用系统资源，提高系统性能。若一个应用程序请求一个锁，而该锁被另外一个应用程序所使用且不能共享，DB2 UDB 就会挂起前一个应用程序。锁升级就是当LOCKLIST (LOCKLIST代表锁能够占用的内存空间) 耗尽或者一个应用程序所拥有的锁大于MAXLOCKS*LOCKLIST的时候(MAXLOCKS 代表应用程序所拥有的锁占所空间的百分比)，DB2 UDB 就试图把几个行级别的锁合并为一个表级别的锁，从而释放锁空间。虽然锁升级本身并不耗费多少时间，但是锁住整个表通常会大大地降低并发性能。
当应用程序处于挂起状态超过了一段规定的时间后，DB2 UDB就会自动中止这个应用程序，同时会向SQLCA发送描述性的错误信息。当两个或者更多的应用程序都持有另外一个应用程序所需资源上的锁，没有这些资源，那些应用程序都无法继续完成其工作的时候，就会发生死锁。
在DLCHKTIME超时之后，DB2 UDB会中止发生死锁的某个应用程序(通常为所做工作最少的那个应用程序)，这会释放这个应用程序所持有的所有的锁，并允许别的应用程序继续工作，DB2 UDB 将向被终止的应用程序的SQLCA发送描述性的错误信息。LOCKTIMEOUT 指定一个应用程序被允许的锁等待的时间，这将避免全局的死锁从而导致整个应用崩溃。如果LOCKTIMEOUT 的值为-1，应用程序会等待直到该锁被释放或者发生一个死锁。
事件监控器
事件监控器用来收集当一个数据库事件发生时所关联的应用程序的信息。这里的事件指，连接，死锁，声明和事务。你可以定义你想监控的事件类型的监控器。比如说，一个死锁监控器就是用来监控死锁的发生。
在DB2 UDB 中存在两种和死锁有关的事件类型：
DEADLOCKS
记录简单的应用程序信息。
DEADLOCKS WITH DETAILS
记录所有复杂的信息，包括应用程序、执行语句声明以及死锁的详细信息。但是使用这种事件监控器会因为需要得到大量额外的信息而降低系统的性能。
如何监控死锁
为了详细说明事件监控器在死锁监控中的用途，我引入了一个简单的死锁场景来触发一个死锁，在随后的章节，我会告诉读者如何分析监控结果以及根据结果来避免死锁的发生。
这里我们需要至少三个应用程序来调用DB2 CLI，一个用来监控死锁的发生，另外两个用来产生死锁。我们可以使用DB2 UDB 安装时附带的SAMPLE数据库。
1. 首先建立一个死锁事件监控器
Session Monitor
db2 connect to sampledb2 "create event monitor
dlmon for tables, deadlocks with details write
to file 'C:dlmon'"mkdir C:dlmondb2 "
set event monitor dlmon state 1"
2. 用另外两个应用程序来产生一个死锁
Session A
db2 connect to sampledb2 +c "insert into employee values('000350', 'Truman', 'I', 'Jiang',
'B00', '5892','1999-02-21', 'Engineer', 19, 'M',
'1978-06-17', 60000, 2000, 6000)"
现在应用程序A就拥有了一个EMPLOYEE表的行级别的排他锁
(注: +c 代表不自动提交SQL语句，DB2 中 autocommit 是缺省设置，也可以通过 db2 update command options using c off 关闭该缺省选项。)
Session B
db2 connect to sampledb2 +c "insert into project
values('AD3300', 'Dead Lock Monitor', 'B00', '000350',
7.00, '1982-07-21', '1983-02-03', 'AD3111')"
现在应用程序B就拥有了一个PROJECT表的行级别的排他锁
Session A
db2 +c "select projname from project"
应用程序A需要PROJECT表上所有行的共享锁，但是因为PROJECT表正在被应用程序B以排他锁的形式独占，这时候应用程序1就进入一个锁等待的状态。
Session B
db2 +c "select firstnme from employee"
应用程序B也进入一个锁等待的状态。此时就出现了一个死锁状态。
3. 两个本身处于锁等待并且占有资源的应用程序互相等待另外一方所持有的资源，这时候Session A和Session B就出现了死锁状态，这种状态一直会延续直到死锁检查器(超出DLCHKTIME时间以后)检查出一个死锁并且回滚其中的一个事务。
Session B
SQLN0991N 因为死锁或者超时，当前事务已经被回滚。原因码为 "2". SQLSTATE=40001这时候死锁事件监控器就会记录这个死锁，同时应用程序A可以完成他的工作。
Session A
PROJNAME----------……20 条记录已选择
Session A
db2 connect reset
Session B
db2 connect reset
4. 通过 db2evmon 工具可以获得死锁信息的日志，并且把日志文件导入到本地机器的文件系统当中。在下面一节，我们将详细分析导出的日志文件。
Session Monitordb2 connect resetdb2evmon
-path c:dlmon > c:dlmondllog1.txt
2同行回答
分析监控结果
本节我们开始详细分析上一节产生的监控结果，从监控导出的日志文件中，我们可以分析出死锁发生的时间，级别，模式以及产生死锁的SQL语句，从而据此来进一步地修正可能由程序并发设计或者数据库设计所导致的缺陷。
---------------------------------
EVENT LOG HEADER Event Monitor name:
DLMON Server Product ID: SQL08022…… Server instance name: DB2
--------------------------------------
--------------------------------------
Database Name: SAMPLE Database Path:
C:\DB2\NODE0000\SQL00001\ ……------------------3)
Deadlock Event ... Deadlock ID: 1 ……4)
Connection Header Event ... Appl Handle: 949 ……5)
Deadlocked Connection ... Deadlock ID:
1 Participant no.:
2 Participant no. holding the lock: 1 Appl Id:
G9B56A72.HE13.01B406083205 Appl Seq number: 0001
Appl Id of connection holding the lock:
G9B56A72.HD13.02CE06083152 …… Deadlock detection
time: 2006-01-06 16:34:27.327582 Table of lock waited on:
EMPLOYEE (A锁发生的表) Schema of lock waited on:
JT Tablespace of lock waited on : USERSPACE1
Type of lock: Row (A锁级别为行锁) Mode of lock:
X - Exclusive (A锁模式为排他锁) Mode application
requested on lock: NS - Share (and Next Key Share)
(在A排他锁上要求B共享锁，发生死锁) ……Text:
select name from employee(产生B共享锁的SQL语句)
List of Locks: (当前所有锁的列表)…… Lock Name
: 0x020005000D0000000000000052 Lock Attributes: 0x00000008
Release Flags : 0x40000000 Lock Count
: 1 Hold Count : 0 Lock Object Name
: 13 Object Type : Row Tablespace Name: USERSPACE1
Table Schema : JT Table Name
: PROJECT Mode : X - Exclusive
(在PROJECT表上有一个排他锁)…… Lock Name:
0x02000300000000000000000054 Lock Attributes：
0x00000000 Release Flags: 0x00000001 Lock Count
: 1 Hold Count : 0
Lock Object Name : 3 Object Type:
Table Tablespace Name: USERSPACE1 Table Schema:
JT Table Name: EMPLOYEE Mode
: IS - Intent Share(在EMPLOYEE表上有一个共享锁)
Locks Held: 6 Locks in List: 6……9) Table Event...
Table schema: JT Table name: EMPLOYEE Record
is the result of a flush: FALSE Table type: User
Data object pages: 1…… Rows read: 35 Rows written:
1 …… Tablespace id: 2 Table event timestamp:
2006-01-06 16:37:28.972501 (记录EMPLOYEE表上发生的事件)
我们可以分析一下dllog1.txt 文件，来准确定位死锁发生的原因，看看5)Deadlocked Connection: 我们可以看出死锁发生的表是EMPLOYEE，同时我们也可以判断出这是一个对于已被排他锁占有的资源申请共享锁所导致的死锁。更加重要的是我们得到了产生死锁的SQL语句，从上面我们可以推断出一定存在别的应用程序在以独占锁的方式占用EMPLOYEE表，这很有可能就是对于EMPLOYEE表的插入或者更新动作造成的。
而这最有可能就是插入或者更新事务时间过长所导致的，导致事务时间过长的原因大体有两种，一是来自于并发程序的设计和编写，二是来自于数据库的设计和数据库参数的调整。
本节我们通过仔细地分析事件监控器的结果来推断出导致死锁发生的原因，从而采取有效的措施去避免死锁的发生。这些措施包括调整数据库参数，或者修改应用程序的代码，或者修改SQL语句甚至是数据库的设计来提高代码和SQL语句执行的效率。
避免死锁的方法
越早地考虑数据库设计中的并发性问题，就越可以提高代码执行的效率，降低程序开发和维护的成本，这里我们提出了一些避免死锁，提高应用程序并发性的方法。
设置隔离级别，根据应用程序的业务逻辑和数据完整性需求来决定合适的隔离级别，包括：RR，RS，CS，UR。该决定需要对应用程序需求和相关的业务规则具有基本理解
尽量避免锁升级，正确调整参数LOCKLIST, MAXLOCKS
SQL0911N返回码68(LOCKTIMEOUT参数)的原因是锁等待超时，而SQL0911返回码2(DLCHKTIME参数)的原因则是因为死锁被强制回滚，避免这两种错误的方法就是合理设计数据库和建立合理的索引
尽快提交事务，不要在事务中加入不必要的执行时间过长的代码，比如大的代码循环和远程调用，或者一些没有用处的SELECT语句
应用程序的框架实现保证一旦发现SQL错误，立刻执行回滚事务，释放锁。
如果多个应用程序访问同一资源，最好以相同的次序访问。这样，即使前一个访问资源的应用程序会延迟其他应用程序的访问，也不会导致死锁的发生
设定外键索引，如果想删除父表中的行，就需要扫描多个子表中的多行数据，这样就需要占用多个子表的锁，我们可以通过在外键上建立索引来减少扫描子表的行数，否则若不建立索引，如果从父表中删除一行的时候，就需要扫描整个子表。
总结
在我门完成这个例子的实际过程中，大家可以看到不同DB2工具(DBC CLI, SQL, DB2EVMON)的使用实例，并且可以学会如何逐步地利用死锁事件监控器来监控死锁的发生，最后我门掌握的内容是如何分析那些从死锁监控器得来的结果，以及采用相应的措施来避免死锁的发生。收起
本节我们开始详细分析上一节产生的监控结果，从监控导出的日志文件中，我们可以分析出死锁发生的时间，级别，模式以及产生死锁的SQL语句，从而据此来进一步地修正可能由程序并发设计或者数据库设计所导致的缺陷。
---------------------------------
EVENT LOG HEADER Event Monitor name:
DLMON Server Product ID: SQL08022…… Server instance name: DB2
--------------------------------------
--------------------------------------
Database Name: SAMPLE Database Path:
C:\DB2\NODE0000\SQL00001\ ……------------------3)
Deadlock Event ... Deadlock ID: 1 ……4)
Connection Header Event ... Appl Handle: 949 ……5)
Deadlocked Connection ... Deadlock ID:
1 Participant no.:
2 Participant no. holding the lock: 1 Appl Id:
G9B56A72.HE13.01B406083205 Appl Seq number: 0001
Appl Id of connection holding the lock:
G9B56A72.HD13.02CE06083152 …… Deadlock detection
time: 2006-01-06 16:34:27.327582 Table of lock waited on:
EMPLOYEE (A锁发生的表) Schema of lock waited on:
JT Tablespace of lock waited on : USERSPACE1
Type of lock: Row (A锁级别为行锁) Mode of lock:
X - Exclusive (A锁模式为排他锁) Mode application
requested on lock: NS - Share (and Next Key Share)
(在A排他锁上要求B共享锁，发生死锁) ……Text:
select name from employee(产生B共享锁的SQL语句)
List of Locks: (当前所有锁的列表)…… Lock Name
: 0x020005000D0000000000000052 Lock Attributes: 0x00000008
Release Flags : 0x40000000 Lock Count
: 1 Hold Count : 0 Lock Object Name
: 13 Object Type : Row Tablespace Name: USERSPACE1
Table Schema : JT Table Name
: PROJECT Mode : X - Exclusive
(在PROJECT表上有一个排他锁)…… Lock Name:
0x02000300000000000000000054 Lock Attributes：
0x00000000 Release Flags: 0x00000001 Lock Count
: 1 Hold Count : 0
Lock Object Name : 3 Object Type:
Table Tablespace Name: USERSPACE1 Table Schema:
JT Table Name: EMPLOYEE Mode
: IS - Intent Share(在EMPLOYEE表上有一个共享锁)
Locks Held: 6 Locks in List: 6……9) Table Event...
Table schema: JT Table name: EMPLOYEE Record
is the result of a flush: FALSE Table type: User
Data object pages: 1…… Rows read: 35 Rows written:
1 …… Tablespace id: 2 Table event timestamp:
2006-01-06 16:37:28.972501 (记录EMPLOYEE表上发生的事件)
我们可以分析一下dllog1.txt 文件，来准确定位死锁发生的原因，看看5)Deadlocked Connection: 我们可以看出死锁发生的表是EMPLOYEE，同时我们也可以判断出这是一个对于已被排他锁占有的资源申请共享锁所导致的死锁。更加重要的是我们得到了产生死锁的SQL语句，从上面我们可以推断出一定存在别的应用程序在以独占锁的方式占用EMPLOYEE表，这很有可能就是对于EMPLOYEE表的插入或者更新动作造成的。
而这最有可能就是插入或者更新事务时间过长所导致的，导致事务时间过长的原因大体有两种，一是来自于并发程序的设计和编写，二是来自于数据库的设计和数据库参数的调整。
本节我们通过仔细地分析事件监控器的结果来推断出导致死锁发生的原因，从而采取有效的措施去避免死锁的发生。这些措施包括调整数据库参数，或者修改应用程序的代码，或者修改SQL语句甚至是数据库的设计来提高代码和SQL语句执行的效率。
避免死锁的方法
越早地考虑数据库设计中的并发性问题，就越可以提高代码执行的效率，降低程序开发和维护的成本，这里我们提出了一些避免死锁，提高应用程序并发性的方法。
设置隔离级别，根据应用程序的业务逻辑和数据完整性需求来决定合适的隔离级别，包括：RR，RS，CS，UR。该决定需要对应用程序需求和相关的业务规则具有基本理解
尽量避免锁升级，正确调整参数LOCKLIST, MAXLOCKS
SQL0911N返回码68(LOCKTIMEOUT参数)的原因是锁等待超时，而SQL0911返回码2(DLCHKTIME参数)的原因则是因为死锁被强制回滚，避免这两种错误的方法就是合理设计数据库和建立合理的索引
尽快提交事务，不要在事务中加入不必要的执行时间过长的代码，比如大的代码循环和远程调用，或者一些没有用处的SELECT语句
应用程序的框架实现保证一旦发现SQL错误，立刻执行回滚事务，释放锁。
如果多个应用程序访问同一资源，最好以相同的次序访问。这样，即使前一个访问资源的应用程序会延迟其他应用程序的访问，也不会导致死锁的发生
设定外键索引，如果想删除父表中的行，就需要扫描多个子表中的多行数据，这样就需要占用多个子表的锁，我们可以通过在外键上建立索引来减少扫描子表的行数，否则若不建立索引，如果从父表中删除一行的时候，就需要扫描整个子表。
总结
在我门完成这个例子的实际过程中，大家可以看到不同DB2工具(DBC CLI, SQL, DB2EVMON)的使用实例，并且可以学会如何逐步地利用死锁事件监控器来监控死锁的发生，最后我门掌握的内容是如何分析那些从死锁监控器得来的结果，以及采用相应的措施来避免死锁的发生。收起
浏览964