转自：http://www.anyiwa.com/?p=1158
DB2 V9.7 锁事件监控
DB2 V9.7 锁事件监控
在大型 DB2 环境中，存在多个应用程序相互竞争数据资源的情况，为保证数据完整性，DB2 采用了锁机制来保护用户的修改不丢失。随着应用程序数量的增多、复杂性的提高，诊断和排除应用程序锁定争用情况的工作可能相当复杂而且耗费 DBA 很多时间。在 DB2 V9.7中新增了锁定事件监视器和其他工具通过收集锁定数据来帮助 DBA 简化锁引起应用程序性能问题。
简介
在大型 DB2 环境中，存在多个应用程序相互竞争数据资源的情况，为保证数据完整性，DB2 采用了锁机制来保护用户的修改不丢失。随着应用程序数量的增多、复杂性的提高，诊断和排除应用程序锁定争用情况的工作可能相当复杂而且耗费 DBA 很多时间。在 V9.7 中，DB2 新增了锁定事件监视器和其他工具通过收集锁定数据来帮助 DBA 简化锁引起应用程序性能问题。
在 V9 以前版本中，如果希望诊断锁问题，我们可以采用表函数 SNAP_GET_LOCKWAIT 查看当前锁等待情况，或者使用 GET SNAPSHOT FOR LOCKS 命令获取相关信息。在 V9.1 版本中，DB2 增加了管理视图 SYSIBMADM. SNAPLOCKWAIT 用于返回锁等待信息。无论是表函数 SNAP_GET_LOCKWAIT 或者管理视图 SYSIBMADM. SNAPLOCKWAIT，我们必须在锁定发生的瞬间执行查询才能获得当前的锁定信息。而应用程序的锁发生具有随机性，对于已经超时回滚或者死锁回滚的应用，则上面的方法不能看到锁定模式、锁定对象等信息，也看不到哪些应用程序因为执行哪些 SQL 语句引起锁问题。
DB2V9.7 提供了锁定事件监视器用于在发生锁定事件时自动捕获关于事件的描述性信息。锁定事件监视器捕获的信息可以标识引起的锁定争用所涉及的关键应用程序，同时还可以捕获这些应用程序因为执行何种 SQL 语句而引起锁。与 DB2 以前版本中的死锁事件监视器不同，锁定事件监视器同时捕获关于锁定请求者（接收到死锁或锁定超时错误或者等待锁定时的耗用时间超出指定时间长度的应用程序）和当前锁定所有者的信息。
DB2V9.7 提供的锁定事件监视器可以捕获其锁定数据的活动的类型包括：
SQL 语句，例如：DML 、DDL 、CALL
LOAD 命令
REORG 命令
BACKUP DATABASE 命令
实用程序请求
在使用了 DB2V9.7 锁定事件监视器后，IBM 不推荐继续使用早期的死锁事件监视器（CREATE EVENT MONITOR FOR DEADLOCKS 语句和 DB2DETAILDEADLOCK），同时不推荐使用锁定超时报告功能（DB2_CAPTURE_LOCKTIMEOUT 注册表变量）。不过在新创建的数据中，默认还是创建了 DB2DETAILDEADLOCK 事件，因此如果我们希望使用锁定事件监视器，最好执行下面语句予以删除。
清单 1. 删除默认死锁事件监视
SET EVENT MONITOR DB2DETAILDEADLOCK state 0
DROP EVENT MONITOR DB2DETAILDEADLOCK
非格式化事件表
DB2 V9.7 对锁、事务事件监控引入了一种新的写入目标即非格式化事件表。使用非格式化事件表的事件监控器具有比较好的性能。在事件捕获过程中，信息以二进制形式写入到非格式化事件表中。一个事件可能会在非格式化事件表中插入多行记录，每一行具有不同的类型。
我们在创建事件时可以指定非格式化事件表的名称、表所在的表空间，默认情况下非格式化表名与事件名称相同。非格式化表具有以下列：
appl_id
appl_name
client_acctng
client_applname
client_userid
client_wrkstnname
event_correlation_id
event_data
event_id
event_type
member
partitioning_key
record_seq_num
record_type
service_subclass_name
service_superclass_name
workload_name
其中为 Blob 类型的 event_data 列最为关键，其他列是用于帮助用户识别感兴趣事件的辅助信息。在完成捕获后，我们可以使用 Java 程序 db2evmonfmt、表函数 EVMON_FORMAT_UE_TO_XML 或存储过程 EVMON_FORMAT_UE_TO_TABLES 从非格式化事件表中抽取数据用以分析。本文将主要使用存储过程 VMON_FORMAT_UE_TO_TABLES 进行数据分析。
与锁事件监控相关的配置参数
为配合锁事件监控，DB2 V9.7 增加了数据库配置参数 MON_LOCKWAIT、MON_LOCKTIMEOUT、MON_DEADLOCK、MON_LW_THRESH。
MON_LOCKWAIT 用于控制锁事件监控在捕获锁等待时搜集数据的详细程度，可以设置成以下值：
NONE，不会在任何分区中收集工作负载的锁定超时数据。
WITHOUT_HIST，发生锁定事件时，会将关于锁定事件的数据发送到任何活动的锁定事件监视器。不会将过去的活动历史记录以及输入值发送到事件监视器。
WITH_HIST，对于所有此类锁定事件，都可以收集当前工作单元中的过去活动历史记录。活动历史缓冲区将在达到最大大小限制后回绕。这意味着，要保留的过去活动数的缺省限制是 250。如果过去活动的数目高于此限制，那么将只报告最新的活动。
HIST_AND_VALUES，对于那些包含输入数据值的活动，会将那些输入数据值发送到任何活动的锁定事件监视器。这些数据值不包括 LOB 数据、更改开始 LONG VARCHAR 数据、LONG VARGRAPHIC 数据、更改结束结构化类型数据或 XML 数据。
MON_LW_THRESH 控制在生成 mon_lockwait 的事件之前等待锁定时花费的时间，以微秒为单位。
MON_LOCKTIMEOUT 和 MON_DEADLOCK 取值范围与 MON_LOCKWAIT 相同，含义类似。
为收集详细的锁锁定信息，我们将数据库的这些参数都调整为 HIST_AND_VALUES。
清单 2. 修改锁事件监控配置参数
db2 update db cfg using MON_LOCKTIMEOUT HIST_AND_VALUES
MON_DEADLOCK HIST_AND_VALUES MON_LOCKWAIT HIST_AND_VALUES
MON_LW_THRESH 5000000
DB20000I UPDATE DATABASE CONFIGURATION 命令成功完成。
db2 get db cfg
——————————- 省略 ————————-
锁定超时事件数 (MON_LOCKTIMEOUT) = HIST_AND_VALUES
死锁事件数 (MON_DEADLOCK) = HIST_AND_VALUES
锁定等待事件数 (MON_LOCKWAIT) = HIST_AND_VALUES
锁定等待事件阈值 (MON_LW_THRESH) = 5000000
锁等待事件
当两个应用程序竞争资源时，第一个应用程序在资源上加锁后，第二个应用程序企图加锁时发现希望增加的锁与资源现有的锁不兼容，就出现锁等待，第二个应用程序挂起。为捕获锁等待事件，MON_LOCKWAIT 不能设置为 NONE，当锁等待时间超过 MON_LW_THRESH 指定的值时则事件监视器捕获该事件。
清单 3. 创建锁等待监视器
db2 create event monitor lockwaitevm for locking
write to UNFORMATTED EVENT TABLE
db2 set event monitor lockwaitevm state 1
db2 list tables for all|find /i “lockwaitevm”
LOCKWAITEVM DB2ADMIN T 2009-11-18-14.47.43.281002
清单 3 代码中，我们首先创建了一个锁事件监视器，并把捕获的数据写入到非格式化事件表中。我们发现在系统除成功创建一个事件外，DB2 还在系统中创建了一张同名的表用于保存捕获数据。
我们另外打开两个命令窗口，用于模拟两个应用，用户名称依次为 app1、app2。我们把测试表 stock 所有权限授予给 public 组，以允许 app1、app2 用户修改数据。
清单 4. 创建测试表
db2 “import from stock.exp of ixf replace_create into db2admin.stock”
db2 “grant all on db2admin.stock to public”
db2 “create index itemno on db2admin.stock(itemno)”
db2 “runstats on table db2admin.stock and indexes all”
清单 5. 应用程序 1 连接
db2 connect to test user app1 using app1
数据库连接信息
数据库服务器 = DB2/NT 9.7.0
SQL 授权标识 = APP1
本地数据库别名 = TEST
清单 6. 应用程序 2 连接
db2 connect to test user app2 using app2
数据库连接信息
数据库服务器 = DB2/NT 9.7.0
SQL 授权标识 = APP2
本地数据库别名 = TEST
清单 7. 查看连接信息
db2 list applications
授权标识程序 应用程序名 应用程序句柄 应用程序标识 数据库名称 代理序号
——– ———– ———- ——————-
APP2 db2bp.exe 14 *LOCAL.DB2.091118070502 TEST 1
APP1 db2bp.exe 7 *LOCAL.DB2.091118070426 TEST 1
我们看到 app1 对应的应用程序句柄为 7，app2 对应的应用程序句柄为 14，这两个句柄将在后面用来识别应用。
我们在应用程序 1 中修改 stock 表的数据：
清单 8. 应用 1 修改 stock 表数据
db2 +c “update db2admin.stock set qty=qty+1 where itemno=300″
清单 9. 应用 2 修改 stock 表数据
db2 +c “update db2admin.stock set qty=qty+1 where itemno=300″
我们的应用 1 和应用 2 同时修改 stock 表的相同行，由于应用 1 先发出 SQL，返回成功后并未发出 commit 或 rollback。因此应用 1 在“300”行上持有 X 锁，当应用 2 在同一行上也希望持有 X 锁时，应用 2 必须等待。
当等待时间超过 MON_LW_THRESH 参数限定的 5 秒钟后，锁等待事件发生。我们使用 EVMON_FORMAT_UE_TO_TABLES 格式化捕获的数据。存储过程 EVMON_FORMAT_UE_TO_TABLES 将首先调用表函数 EVMON_FORMAT_UE_TO_XML 将非格式化事件表数据转换为 XML，然后再将 XML 数据转换成关系表形式。
清单 10. 获取锁等待事件数据
db2 set event monitor lockwaitevm state 0
db2 “call EVMON_FORMAT_UE_TO_TABLES ( ‘LOCKING’, NULL, NULL, N
ULL, NULL, NULL, ‘RECREATE_FORCE’, -1,
’SELECT * FROM lockwaitevm ORDER BY event_timestamp’)”
返回状态 = 0
db2 list tables for all |find /i “lock”
表 / 视图 模式 类型 创建时间
LOCKWAITEVM DB2ADMIN T 2009-11-18-14.47.43.281002
LOCK_ACTIVITY_VALUES DB2ADMIN T 2009-11-18-15.35.24.375003
LOCK_EVENT DB2ADMIN T 2009-11-18-15.35.24.078004
LOCK_PARTICIPANTS DB2ADMIN T 2009-11-18-15.35.24.296003
LOCK_PARTICIPANT_ACTIVITIES DB2ADMIN T 2009-11-18-15.35.24.343003
我们看到经过格式化后，系统中多了几张表，名称为 LOCK_ACTIVITY_VALUES、LOCK_EVENT、LOCK_PARTICIPANTS、LOCK_PARTICIPANT_ACTIVITIES。表 LOCK_EVENT 对应发生的锁定事件，每个事件对应一条记录。LOCK_PARTICIPANTS 标识事件的参与者，每个参与的应用程序一行。LOCK_PARTICIPANT_ACTIVITIES 中包含了参与事件的应用程序曾经和当前正在执行的语句。这些表中都有一个 XMLID 列来标识一个事件，格式为 <event_header>_<event_id>_<event_type>_<event_timestamp>_<partition> 组成。对所锁等待事件，event_header 为 db2LockEvent，event_type 为 LOCKWAIT。
我们使用下面脚本查询锁等待事件参与者。
清单 11. 查询锁事件参与者
select substr(XMLID,1,64) as xml_id,
EVENT_ID,
substr(EVENT_TYPE,1,32) evn_type ,
MEMBER,
DL_CONNS,
ROLLED_BACK_PARTICIPANT_NO
from LOCK_EVENT;
select substr(XMLID,1,64) as xml_id,
PARTICIPANT_NO,
PARTICIPANT_TYPE,
PARTICIPANT_NO_HOLDING_LK,
APPLICATION_HANDLE,
LOCK_WAIT_VAL,
LOCK_NAME,
LOCK_OBJECT_TYPE,
LOCK_ATTRIBUTES,
LOCK_CURRENT_MODE,
LOCK_MODE_REQUESTED,
LOCK_MODE, LOCK_COUNT,
LOCK_HOLD_COUNT,
LOCK_RRIID,
LOCK_STATUS,
LOCK_RELEASE_FLAGS,
TABLE_FILE_ID,
TABLE_NAME,
TABLE_SCHEMA,
TABLESPACE_NAME,
THRESHOLD_ID,
THRESHOLD_NAME
from LOCK_PARTICIPANTS;
通过查询，我们发现表 LOCK_EVENT 中有一条记录，事件类型为 LOCKWAIT，发生时间为 2009-11-18-15.29.51.094867。表 LOCK_PARTICIPANTS 中有两条记录，一条记录的 PARTICIPANT_TYPE 为 Requester，应用程序句柄为 14 即 APP2，LOCK_OBJECT_TYPE 为 2 表示为行锁，LOCK_MODE_REQUESTED 为 5 表示请求的锁类型为排他锁（X 锁），请求锁定的表为 STOCK，而行上目前加的锁 LOCK_MODE 为 5 表示排他锁（X 锁）。表 LOCK_PARTICIPANTS 另一条记录 PARTICIPANT_TYPE 为 Owner，应用程序句柄为 7 即 APP1。这意味着 APP1 为锁的拥有者，在行上持有了 X 锁导致应用程序 2 锁等待。如果我们希望进一步了解参与者因为执行了何种语句导致锁等待，我们需要进一步查询表 LOCK_PARTICIPANT_ACTIVITIES。
清单 12. 查询锁事件参与者的活动
SELECT
PARTICIPANT_NO,
ACTIVITY_TYPE,
substr(STMT_TEXT,1,256) sql_text
FROM LOCK_PARTICIPANT_ACTIVITIES;
对清单 12 的查询结果，我们重点关注 ACTIVITY_TYPE 和 SQL。活动类型取值 current 表示应用正在执行某个 SQL，取值 past 表示该 SQL 已经执行完成。上述查询结果在显示 PARTICIPANT_NO 为 1（与清单 11 查询语句关联，即为应用程序 2）正在执行 Update 语句，PARTICIPANT_NO 为 2（与清单 11 查询语句关联，即为应用程序 1）则已经完成 Update 语句执行。
清单 13. 引起锁等待的 SQL
PARTICIPANT_NO ACTIVITY_TYPE SQL_TEXT
1 current update db2admin.stock set qty=qty+1 where itemno=300
2 past update db2admin.stock set qty=qty+1 where itemno=300
锁超时事件
为避免应用程序处于无限等待状态，我们可以为应用程序设定锁超时。这可以通过修改数据库配置参数 LOCKTIMEOUT 为所有应用程序设置超时时间，也可以通过为特定会话设置 CURRENT LOCK TIMEOUT 特殊寄存器达到这个目的。本文在两个应用程序的命令行中设置 CURRENT LOCK TIMEOUT 特殊寄存器为 30 秒给 APP1 和 APP2 限定超时时间，然后重新执行 Update 语句。为避免与锁等待的事件信息混淆，我们删除事件、删除非格式化表、删除格式化结果表
清单 14. 为 APP1 和 APP2 会话设置锁超时
db2 “set current lock timeout 30″
清单 15. 创建锁超时事件
db2 ” drop event monitor LOCKWAITEVM”
db2 ” drop table LOCKWAITEVM”
db2 ” drop table LOCK_ACTIVITY_VALUES”
db2 ” drop table LOCK_EVENT”
db2 ” drop table LOCK_PARTICIPANTS”
db2 ” drop table LOCK_PARTICIPANT_ACTIVITIES ”
db2 create event monitor locktimeout for locking
WRITE TO UNFORMATTED EVENT TABLE
db2 set event monitor locktimeout state 1
清单 16. 应用 1 修改 stock 表数据
db2 +c “update db2admin.stock set qty=qty+1 where itemno=259″
清单 17. 应用 2 修改 stock 表数据
db2 +c “update db2admin.stock set qty=qty+1 where itemno=259″
DB21034E 该命令被当作 SQL 语句来处理，因为它是无效的“命令行处理器”命令。
在 SQL 处理期间，它返回：SQL0911N 因为死锁或超时，所以当前事务已回滚。
原因码为 “68″。 SQLSTATE=40001
在经过 30 秒钟的等待后，应用 2 返回 SQL0911N 错，原因码为 68 表示发生了锁超时。我们使用下面程序格式化事件数据。
清单 18. 格式化锁超时事件数据
db2 set event monitor locktimeout state 0
db2 “call EVMON_FORMAT_UE_TO_TABLES
( ‘LOCKING’, NULL, NULL, NULL, NULL, NULL, ‘RECREATE_FORCE’, -1,
‘SELECT * FROM locktimeout ORDER BY event_timestamp’)”
我们查询 LOCK_EVENT 发现存在两个事件，一个事件为 LOCKWAIT 一个事件为 LOCKTIMEOUT。这表明在锁超时事件发生之前，由于等待时间超过了 5 秒（参数 MON_LW_THRESH 设置的值），先发生了锁等待事件，然后才发生锁超时。查询表 LOCK_PARTICIPANTS 显示相关锁事件的参与方，查询表 LOCK_PARTICIPANT_ACTIVITIES 显示参与者执行 SQL 的历史。
清单 19. 锁超时事件
db2 “select substr(XMLID,1,64) xml_id from LOCK_EVENT”
XML_ID
—————————————————————-
db2LockEvent_2_LOCKWAIT_2009-11-18-17.34.09.292636_0
db2LockEvent_3_LOCKTIMEOUT_2009-11-18-17.34.34.292606_0
db2 “select PARTICIPANT_NO as p_no, PARTICIPANT_TYPE as p_type,
PARTICIPANT_NO_HOLDING_LK as p_no_holdling_lk, APPLICATION_HANDLE as
appl_hanle,substr(TABLE_NAME,1,10) as t_name from LOCK_PARTICIPANTS”
P_NO P_TYPE P_NO_HOLDLING_LK APPL_HANLE T_name
1 Requester 2 14 STOCK
2 Owner – 7 -
死锁事件
两个应用程序有冲突的锁需求，不能完成应用事务的情况称为死锁。出现死锁时，一个应用拥有资源 A 的锁，去申请资源 B 的锁，另一个应用拥有资源 B 的锁，去申请资源 A 的锁。双方都处于锁等待状态，并且也不释放自己拥有的锁，导致无限循环等待。死锁一般都是由于应用使用资源时顺序不当导致的，可以通过建立资源访问规则避免死锁。
DB2 在 V9.7 版本以前，默认为数据库建立了一个死锁监视器，并启动一个死锁检查进程定期启动检查是否有死锁存在，默认检查间隔为 10 秒（DLCHKTIME 参数控制）。DB2 V9.7 建议不再使用死锁监视器，建议采用锁监视。
清单 20. 创建死锁事件监视
db2 ” drop event monitor locktimeout ”
db2 ” drop table LOCKTIMEOUT”
db2 ” drop table LOCK_ACTIVITY_VALUES”
db2 ” drop table LOCK_EVENT”
db2 ” drop table LOCK_PARTICIPANTS”
db2 ” drop table LOCK_PARTICIPANT_ACTIVITIES ”
db2 create event monitor lockdead for locking
WRITE TO UNFORMATTED EVENT TABLE
db2 set event monitor lockdead state 1
我们依次在 APP1 和 APP2 连接下执行下面 SQL：
清单 21. APP1 更新 ITEMNO 为 300 的行
db2 +c “update db2admin.stock set qty=qty+1 where itemno=300 ”
清单 22. APP2 更新 ITEMNO 为 259 的行
db2 +c “update db2admin.stock set qty=qty+1 where itemno=259 ”
清单 23. APP1 更新 ITEMNO 为 259 的行
db2 +c “update db2admin.stock set qty=qty+1 where itemno=259 ”
清单 24. APP2 更新 ITEMNO 为 300 的行
db2 +c “update db2admin.stock set qty=qty+1 where itemno=300 ”
DB21034E 该命令被当作 SQL 语句来处理，因为它是无效的“命令行处理器”命令。在
SQL 处理期间，它返回：
SQL0911N 因为死锁或超时，所以当前事务已回滚。原因码为 “2″。 SQLSTATE=40001
在经过 10 秒钟后，APP2 返回 SQL0911 错误，原因码为 2 表示是死锁。
我们查询 LOCK_EVENT 发现存在三个事件，先发生两个 LOCKWAIT 事件，然后发生 DEADLOCK 事件。这表明在死锁事件发生之前，两个应用由于等待时间超过了 5 秒（参数 MON_LW_THRESH 设置的值），分别发生了锁等待事件，然后才发生死锁。死锁等待事件中被回滚的参与方为 2，通过 LOCK_PARTICIPANTS 表对应到 APP2。查询表 LOCK_PARTICIPANTS 显示第一个锁等待事件请求者为 APP1，拥有者为 APP2，请求 ITEMO 为 259 的行锁。第二个锁等待事件请求者为 APP2，拥有者为 APP1，请求 ITEMO 为 300 的行锁。死锁事件两个参与方都是请求者，DB2 检查出这两个应用相互持有对方的锁。
清单 25. 锁超时事件
db2 “select substr(XMLID,1,64) xml_id ,
ROLLED_BACK_PARTICIPANT_NO as r_b_p_no from LOCK_EVENT”
XML_ID R_B_P_NO
——————— ——
db2LockEvent_2_LOCKWAIT_2009-11-19-11.56.50.079310_0 -
db2LockEvent_3_LOCKWAIT_2009-11-19-11.56.55.906457_0 -
db2LockEvent_4_DEADLOCK_2009-11-19-11.57.00.599826_0 2
db2 “select PARTICIPANT_NO as p_no, PARTICIPANT_TYPE as p_type
, PARTICIPANT_NO_HOLDING_LK as p_no_holdling_lk,
APPLICATION_HANDLE as appl_hanl e,substr(TABLE_NAME,1,10) as t_name
from LOCK_PARTICIPANTS”
P_NO P_TYPE P_NO_HOLDLING_LK APPL_HANLE T_NAME
——- —— ——– ———– ——
1 Requester 2 7
STOCK
2 Owner – 14
-
1 Requester 2 14 STOCK
2 Owner – 7 -
2 Requester 1 14 STOCK
1 Requester 2 7 STOCK
我们可以通过以下查询获得参与死锁历史上执行过的导致死锁的 SQL 语句：
清单 26. 引起死锁的 SQL
db2 “select PARTICIPANT_NO as p_no, ACTIVITY_TYPE as a_type,
substr(STMT_TEXT,1,256) sql_text FROM LOCK_PARTICIPANT_ACTIVITIES
where xmlid like ‘%DEADLOCK%’ ”
P_NO A_TYPE SQL_TEXT
2 current update db2admin.stock set qty=qty+1 where itemno=300
2 past update db2admin.stock set qty=qty+1 where itemno=259
1 current update db2admin.stock set qty=qty+1 where itemno=259
1 past update db2admin.stock set qty=qty+1 where itemno=300
总结
DB2 V9.7 提供的锁定事件监控集中了锁等待事件、锁超时事件、死锁事件监控，方便 DBA 及时发现引起死锁的应用程序和相关 SQL 语句。本文中将这三种分开描述是为简化理解，在实际使用过程中创建一个监视器即可。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝｜｜
DB2 for Linux, UNIX, and Windows 的锁事件，第 3 部分: 使用 DB2 9.7 中的锁事件监控器来解决并发性问题
http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1004lockeventmonitor/简介
本文介绍了新的锁事件监控器，并演示了如何使用它来分析并发性问题。本系列其他文章（查看 参考资料）分别解释了 DB2 8.2/9.1 和 DB2 9.5 的可用选项。这些选项已经作了进一步加强。DB2 9.7 提供了死锁、锁超时、锁等待分析的集成监控方案：新的锁事件监控器。这个锁事件监控器取代了旧的机制和工具。这些机制和工具从 DB2 9.7 开始不再使用，在以后的 DB2 发行版中不再出现。本文介绍了新的锁事件监控器，并演示了如何使用它来分析并发性问题。
创建示例数据库
本文描述了锁事件监控器以及两个并发问题的例子。第一个例子中，创建了 DB2 示例数据库，并设置 DB CFG 的 LOCKTIMEOUT 参数值为 10 秒，如清单 1 所示。
清单 1. 创建 DB2 示例数据库，并设置 LOCKTIMEOUT 值
db2sampl db2 "UPDATE DB CFG FOR SAMPLE USING LOCKTIMEOUT 10"
锁事件监控器收集的信息写进非格式化事件表。之所以这么称，是因为数据以二进制形式存放在表中。非格式化事件表应放在单独表空间中。这可防止表空间由于监控数据过多而意外填满表格或索引数据。有了 DB2 自动存储，很容易为非格式化事件表创建额外存储空间，这是因为表空间不需要任何显式表空间容器定义。清单 2 连接到 SAMPLE 数据库并创建新的 TBSPACE_LOCKING 表空间。
清单 2. 为事件监控数据创建表空间
db2 "CONNECT TO SAMPLE" db2 "CREATE TABLESPACE TBSPACE_LOCKING"
下一步，创建锁事件监控器。选择锁事件监控器本身和其对应的非格式化事件表的名字。例中，锁事件监控器叫做 EVMON_LOCKING。非格式化事件表叫做 TAB_LOCKING，模式为 EMDATA。指定 IN子句，表格放在自己的 TBSPACE_LOCKING 表空间中。
清单 3. 创建锁事件监控器
db2 "CREATE EVENT MONITOR EVMON_LOCKING FOR LOCKING WRITE TO UNFORMATTED EVENT TABLE
(TABLE EMDATA.TAB_LOCKING IN TBSPACE_LOCKING)"
默认情况下，锁事件监控器是随 AUTOSTART 选项生成的，因此当数据库激活时自动开始。为了一开始就开启锁事件监控器而无需重新激活数据库，通过设置其状态值，即 active 为 1 来显式开启，如清单 4 所示。
清单 4. 激活锁事件监控器
db2 "SET EVENT MONITOR EVMON_LOCKING STATE 1"
定义生成锁事件的范围
尽管锁事件监控器已激活，但还没开始收集每个会话的并发性问题相关信息。相反您应当指定锁事件监控器所要监控的会话。在两级指定会话：
-
数据库级
- 从 DB2 9.7 开始，DB CFG 使用三个新的参数：MON_DEADLOCK、MON_TIMEOUT 和 MON_LOCKWAIT。当激活其中任何一个参数后，锁事件监控器开始观察所有的数据库会话，它独立于应用程序名、认证 ID、以及其他参数，监控对应锁事件发生情况：死锁、锁超时或锁等待。 工作负载级
- 或者，可以配置锁事件监控器观察属于某个工作负载的会话。此时，可以通过设置特定的工作负载选项来激活锁事件集合。
一般来说，死锁或锁超时事件相关的数据集合应当在数据库级激活，因为这些事件通常是应用程序异常。因此，提前限制某种工作负载通常不可能，或根本不需要。另一方面，锁等待事件总会在一定程度上出现。因此将锁等待事件限制到某一工作负载就会有意义。这里给出一般指导原则，就是在为特定的并发性问题决定最佳监控策略前检查这两个选项。
收集某一工作负载的锁超时事件数据
本例中，使用锁事件监控器只收集某一工作负载的锁超时信息。简单起见，不要创建用户定义工作负载。相反使用 DB2 WLM 默认工作负载 SYSDEFAULTUSERWORKLOAD，它包含不属于用户定义工作负载的所有会话。对于 SYSDEFAULTUSERWORKLOAD，锁事件监控器捕获所有锁超时事件。由于已经创建并激活锁事件监控器 EVMON_LOCKING，只要启动所需工作负载的锁事件数据集合，如清单 5 所示。
清单 5. 启动默认用户工作负载的锁超时集合
db2 "ALTER WORKLOAD SYSDEFAULTUSERWORKLOAD COLLECT LOCK TIMEOUT DATA WITH HISTORY"
如选中 WITH HISTORY 选项，锁事件监控器收集：
- 锁超时事件的出错 SQL 语句
- 在锁超时事件中涉及的事务内部执行的所有其他 SQL 语句
现在，通过试图使用两个不同会话更改 EMPLOYEE 表来故意引起锁超时。第一个会话中，执行让每个员工提高 2% 薪水的事务，然后查询所有员工姓名和新的薪水。由于该事务未执行（自动执行已通过 DB2 CLP 选项 +c 被停用），它对 EMPLOYEE 表中的每行都持有 X-lock（独占锁），如清单 6 所示。
清单 6. 第一个事务 - 让所有员工薪水提高 2%
db2 "CONNECT TO SAMPLE" db2 +c "UPDATE EMPLOYEE SET SALARY = SALARY * 1.02" db2 +c "SELECT LASTNAME, FIRSTNME, SALARY FROM EMPLOYEE ORDER BY LASTNAME ASC"
在另一个会话中，执行另一个事务，根据现有薪水给每个经理 10% 的奖金，如清单 7 所示。
清单 7. 第二个事务：给所有经理 10% 的奖金
db2 "CONNECT TO SAMPLE" db2 +c "UPDATE EMPLOYEE SET BONUS = SALARY * 0.1 WHERE JOB = 'MANAGER'"
由于第一个事务锁住所有经理和其他员工行，锁超时 10 秒后发生（记住 LOCKTIMEOUT 参数设置），第二个事务的 UPDATE 语句失败。
清单 8. DB2 返回的锁超时错误
SQL0911N The current transaction has been rolled back because of a deadlock or timeout
Reason code "68". SQLSTATE=40001
锁超时事件的信息被写入非格式化表 EMDATA.TAB_LOCKING 中：执行 CREATE EVENT MONITOR 语句时所指定的。为了读取并格式化锁监控器收集的信息，使用名为 db2evmonfmt 的工具，它是用 Java 编写的。在第一次使用前，必须先编译。可以在 DB2 安装目录的 samples 子目录找到该工具的源代码（包括相应 XML 表单的样式表）。在编译 Java 源代码之前，将两个文件（源代码 + 样式表）复制到当前工作目录，如清单 9 所示。
清单 9. 复制 db2evmonfmt 工具的源文件
copy "C:\Program Files\IBM\SQLLIB\samples\java\jdbc\db2evmonfmt.java" . copy "C:\Program Files\IBM\SQLLIB\samples\java\jdbc\DB2EvmonLocking.xsl" .
需要 JDK (Java Development Kit) 来编译 db2evmonfmt 工具。由于 JDK 是随每个 DB2 服务器产品安装的，可以使用 DB2 安装程序的 Java 编译器（javac）。为了让操作系统找到 Java 编译器，javac 二进制文件必须在调用前包含在 PATH 变量中，如清单 10 所示。
清单 10. 编译 db2evmonfmt 工具
set PATH=C:\Program Files\IBM\SQLLIB\java\jdk\bin;%PATH% javac db2evmonfmt.java
为了读取和格式化锁超时事件的信息，根据选项调用 db2evmonfmt 工具，如清单 11 所示。默认情况下，工具输出写到标准输出，所以将其输出重新指向文本文件。
清单 11. 调用 db2evmonfmt 工具来格式化为锁超时事件收集的数据
java db2evmonfmt -d sample -ue emdata.tab_locking -ftext -u username -p password
>locktimeout.txt
其中的代码选项如下：
-
-d
- 数据库名 -ue
- 非格式化事件表的完全许可路径（包括模式） -ftext
-
纯文本格式（或者，通过指定
-fxml选项设置 XML 格式）的锁事件信息输出
-u
- 数据库访问的用户 ID -p
- 数据库访问的密码
对于锁超时事件，db2evmonfmt 生成如清单 12 所示的输出。
清单 12. 锁超时事件的 db2evmonfmt 输出
------------------------------------------------------- Event ID : 1 Event Type : LOCKTIMEOUT Event Timestamp : 2010-02-16-18.27.38.102302 Partition of detection : 0 ------------------------------------------------------- Participant No 1 requesting lock ---------------------------------- Lock Name : 0x02000600040000000000000052 Lock wait start time : 2010-02-16-18.27.28.086228 Lock wait end time : 2010-02-16-18.27.38.102302 Lock Type : ROW Lock Specifics : ROWID=4,DATA_PARTITION_ID=0,PAGEID=0 Lock Attributes : 00000000 Lock mode requested : Update Lock mode held : Exclusive Lock Count : 1 Lock Hold Count : 0 Lock rrIID : 0 Lock Status : Waiting Lock release flags : 40000000 Tablespace TID : 2 Tablespace Name : USERSPACE1 Table FID : 6 Table Schema : FECHNER Table Name : EMPLOYEE Attributes Requester Owner --------------------- ------------------------------ ------------------------------ Participant No 1 2 Application Handle 0342 0333 Application ID *LOCAL.DB2.100216172719 *LOCAL.DB2.100216172702 Application Name db2bp.exe db2bp.exe Authentication ID FECHNER FECHNER Requesting AgentID 2172 6320 Coordinating AgentID 2172 6320 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 subclass SYSDEFAULTSUBCLASS SYSDEFAULTSUBCLASS Current Request Execute Immediate Close Cursor TEntry state 1 2 TEntry flags1 00000000 00000000 TEntry flags2 00000200 00000200 Lock escalation no no Client userid Client wrkstnname Client applname Client acctng Current Activities of Participant No 1 ---------------------------------------- Activity ID : 1 Uow ID : 1 Package Name : SQLC2H20 Package Schema : NULLID Package Version : Package Token : AAAAAZBZ Package Sectno : 203 Reopt value : none Incremental Bind : no Eff isolation : CS Eff degree : 0 Eff locktimeout : 10 Stmt unicode : no Stmt query ID : 0 Stmt nesting level : 0 Stmt invocation ID : 0 Stmt source ID : 0 Stmt pkgcache ID : 3783866187777 Stmt type : Dynamic Stmt operation : DML, Insert/Update/Delete Stmt text : UPDATE EMPLOYEE SET BONUS = SALARY * 0.1 WHERE JOB = 'MANAGER' Past Activities of Participant No 1 ------------------------------------- Activities not available Current Activities of Participant No 2 ---------------------------------------- Activities not available Past Activities of Participant No 2 ------------------------------------- Past Activities wrapped: no Activity ID : 2 Uow ID : 2 Package Name : SQLC2H20 Package Schema : NULLID Package Version : Package Token : AAAAAZBZ Package Sectno : 201 Reopt value : none Incremental Bind : no Eff isolation : CS Eff degree : 0 Eff locktimeout : 10 Stmt unicode : no Stmt query ID : 0 Stmt nesting level : 0 Stmt invocation ID : 0 Stmt source ID : 0 Stmt pkgcache ID : 721554505729 Stmt type : Dynamic Stmt operation : DML, Select (blockable) Stmt text : SELECT LASTNAME, FIRSTNME, SALARY FROM EMPLOYEE ORDER BY LASTNAME ASC Activity ID : 1 Uow ID : 2 Package Name : SQLC2H20 Package Schema : NULLID Package Version : Package Token : AAAAAZBZ Package Sectno : 203 Reopt value : none Incremental Bind : no Eff isolation : CS Eff degree : 0 Eff locktimeout : 10 Stmt unicode : no Stmt query ID : 0 Stmt nesting level : 0 Stmt invocation ID : 0 Stmt source ID : 0 Stmt pkgcache ID : 3835405795329 Stmt type : Dynamic Stmt operation : DML, Insert/Update/Delete Stmt text : UPDATE EMPLOYEE SET SALARY = SALARY * 1.02
输出包含所有用来确定锁超时事件原因的信息，包括：
- 参与锁超时事件的应用程序通用信息，如应用程序名、认证 ID 等等。
- 等待应用程序请求的锁信息，如行或表锁、共享或独占锁、表名等等。
- 阻塞的 SQL 语句信息。
- 在参与事务中的锁超时事件前执行的所有 SQL 语句信息，如语句文本、使用的隔离级、执行顺序等等。
事件监控器不止收集参与事务中最后执行的 SQL 语句。这有助于锁分析，因为这些语句可能不是并发问题的原因。例如，不是 SELECT 语句造成产生问题的锁，但 UPDATE 语句恰好在同一事务中在 SELECT 语句前执行。只有在指定 ALTER WORKLOAD...COLLECT LOCK TIMEOUT DATA 语句的 WITH HISTORY 选项后才收集之前 SQL 语句的信息。
确定引起锁超时事件的原因后，停止收集默认工作负载的锁超时信息，如清单 13 所示。
清单 13. 停止默认用户工作负载的锁超时数据收集
db2 "ALTER WORKLOAD SYSDEFAULTUSERWORKLOAD COLLECT LOCK TIMEOUT DATA NONE"
为了准备下一个示例场景，将 DB CFG 参数 LOCKTIMEOUT 重新设置为 -1，这意味着锁超时不再发生，因为一个事务可以无限等待另一个事务释放所需的锁。由于 LOCKTIMEOUT 参数不可在线调整，需要停用并重新激活数据库，以使更改起作用，如清单 14 所示。
清单 14. 重置数据库 LOCKTIMEOUT 设置
db2 "TERMINATE" db2 "DEACTIVATE DB SAMPLE" db2 "UPDATE DB CFG FOR SAMPLE USING LOCKTIMEOUT -1" db2 "ACTIVATE DB SAMPLE"
收集整个数据库的死锁事件数据
可以设置锁事件监控器来收集某个工作负载或整个数据库的锁事件信息。前一个例子演示了如何收集单个工作负载的锁超时事件信息。本例中，将看一看如何配置锁事件监控器以让其收集整个数据库的死锁事件。无论收集的锁事件数据是何种级别（工作负载或数据库），只有当锁事件监控器激活时才收集锁事件数据。
通过 DB CFG 参数 MON_DEADLOCK 来激活收集整个数据库的死锁事件信息（锁超时和锁等待分别对应的是 MON_TIMEOUT 和 MON_LOCKWAIT）。默认情况下，MON_DEADLOCK 值设置为 WITHOUT_HIST，这意味着锁事件监控器已经收集死锁事件的信息，但不包括历史数据。如果想要收集历史数据，将参数值更新为 HISTORY。与 LOCKTIMEOUT 参数相比，MON_DEADLOCK 可以在线配置，这意味着没必要停用或激活数据库。尽管如此，要在线改变 DB CFG 参数，对数据库的连接是强制性的，而且 UPDATE DB CFG 命令必须包含IMMEDIATE 选项，如清单 15 所示。
清单 15. 在数据库级配置死锁信息收集
db2 "CONNECT TO SAMPLE" db2 "UPDATE DB CFG USING MON_DEADLOCK HISTORY IMMEDIATE"
下一步，在最终将导致死锁的各会话中执行一系列 SQL 语句。在会话中，试着同时更新 DEPARTMENT 表中的位置数据和部门名称。在第一个会话中，执行清单 16 中的 UPDATE 语句，它对部门号为 B01 的部门行设置独占锁。
清单 16. 第一个事务：更新某个部门的位置数据
db2 "CONNECT TO SAMPLE" db2 +c "UPDATE DEPARTMENT SET LOCATION = 'New York' WHERE DEPTNO = 'B01'"
在第二个会话中，更新部门号为 E11 的行，然后试着更新部门号为 B01 的行，如清单 17 所示。
清单 17. 第二个事务：更新某个部门的名称
db2 "CONNECT TO SAMPLE" db2 +c "UPDATE DEPARTMENT SET DEPTNAME = 'OPERATIONS 1' WHERE DEPTNO = 'E11'" db2 +c "UPDATE DEPARTMENT SET DEPTNAME = 'PLANNING 1' WHERE DEPTNO = 'B01'"
第二个会话中的 UPDATE 语句被第一个会话中的事务所持有的独占锁阻塞，因此它必须等待。现在在第一个会话中执行一个试图改变被第二个会话事务阻塞的行（E11 部门的行）的 UPDATE 语句。数据库识别出死锁事件。
清单 18. 第一个事务：更新另一个部门的位置信息
db2 +c "UPDATE DEPARTMENT SET LOCATION = 'Los Angeles' WHERE DEPTNO = 'E11'"
数据库管理器自动回滚其中一个事务，第一个会话中的或第二个会话中的。出错的事务的 UPDATE 语句返回相应错误信息，如清单 19 所示。
清单 19. DB2 返回的死锁错误
SQL0911N The current transaction has been rolled back because of a deadlock or timeout.
Reason code "2". SQLSTATE=40001
为了格式化锁事件监控器收集的死锁事件数据，并将其写到普通文本文件中，再次调用 db2evmonfmt 工具。由于第一个例子已编译过源代码，db2evmonfmt Java 类文件已存在。这一次，为限制工具输出死锁事件，将附加选项 -type 的值设置为 deadlock，如清单 20 所示。
清单 20. 调用 db2evmonfmt 工具来格式化为死锁事件收集的数据
java db2evmonfmt -d sample -ue emdata.tab_locking -type deadlock -ftext
-u username -p password >deadlock.txt
db2evmonfmt 的输出格式几乎与第一个例子中所描述的格式一致。根据应用程序、锁和 SQL 语句有关信息，可以轻松确定死锁事件的原因，如清单 21 所示。
清单 21. 死锁事件的 db2evmonfmt 输出
------------------------------------------------------- Event ID : 1 Event Type : DEADLOCK Event Timestamp : 2010-02-21-19.25.32.723805 Partition of detection : 0 ------------------------------------------------------- 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 044 2 1 0 024 Participant No 1 requesting lock ---------------------------------- Lock Name : 0x020005000B0000000000000052 Lock wait start time : 2010-02-21-19.25.26.208252 Lock wait end time : 2010-02-21-19.25.32.723805 Lock Type : ROW Lock Specifics : ROWID=11,DATA_PARTITION_ID=0,PAGEID=0 Lock Attributes : 00000000 Lock mode requested : Exclusive Lock mode held : Exclusive Lock Count : 1 Lock Hold Count : 0 Lock rrIID : 0 Lock Status : Waiting Lock release flags : 40000000 Tablespace TID : 2 Tablespace Name : USERSPACE1 Table FID : 5 Table Schema : FECHNER Table Name : DEPARTMENT Participant No 2 requesting lock ---------------------------------- Lock Name : 0x02000500050000000000000052 Lock wait start time : 2010-02-21-19.25.13.381517 Lock wait end time : 2010-02-21-19.25.32.723805 Lock Type : ROW Lock Specifics : ROWID=5,DATA_PARTITION_ID=0,PAGEID=0 Lock Attributes : 00000000 Lock mode requested : Exclusive Lock mode held : Exclusive Lock Count : 1 Lock Hold Count : 0 Lock rrIID : 0 Lock Status : Waiting Lock release flags : 40000000 Tablespace TID : 2 Tablespace Name : USERSPACE1 Table FID : 5 Table Schema : FECHNER Table Name : DEPARTMENT Attributes Requester Requester --------------------- ------------------------------ ------------------------------ Participant No 1 2 Application Handle 024 044 Application ID *LOCAL.DB2.100221182012 *LOCAL.DB2.100221182513 Application Name db2bp.exe db2bp.exe Authentication ID FECHNER FECHNER Requesting AgentID 4148 1016 Coordinating AgentID 4148 1016 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 subclass SYSDEFAULTSUBCLASS SYSDEFAULTSUBCLASS Current Request Execute Immediate Execute Immediate TEntry state 2 2 TEntry flags1 00000000 00000000 TEntry flags2 00000200 00000200 Lock escalation no no Client userid Client wrkstnname Client applname Client acctng Current Activities of Participant No 1 ---------------------------------------- Activity ID : 2 Uow ID : 4 Package Name : SQLC2H20 Package Schema : NULLID Package Version : Package Token : AAAAAZBZ Package Sectno : 203 Reopt value : none Incremental Bind : no Eff isolation : CS Eff degree : 0 Eff locktimeout : -1 Stmt unicode : no Stmt query ID : 0 Stmt nesting level : 0 Stmt invocation ID : 0 Stmt source ID : 0 Stmt pkgcache ID : 631360192513 Stmt type : Dynamic Stmt operation : DML, Insert/Update/Delete Stmt text : UPDATE DEPARTMENT SET LOCATION = 'Los Angeles' WHERE DEPTNO = 'E11' Past Activities of Participant No 1 ------------------------------------- Past Activities wrapped: no Activity ID : 1 Uow ID : 4 Package Name : SQLC2H20 Package Schema : NULLID Package Version : Package Token : AAAAAZBZ Package Sectno : 203 Reopt value : none Incremental Bind : no Eff isolation : CS Eff degree : 0 Eff locktimeout : -1 Stmt unicode : no Stmt query ID : 0 Stmt nesting level : 0 Stmt invocation ID : 0 Stmt source ID : 0 Stmt pkgcache ID : 2791728742402 Stmt type : Dynamic Stmt operation : DML, Insert/Update/Delete Stmt text : UPDATE DEPARTMENT SET LOCATION = 'New York' WHERE DEPTNO = 'B01' Current Activities of Participant No 2 ---------------------------------------- Activity ID : 2 Uow ID : 1 Package Name : SQLC2H20 Package Schema : NULLID Package Version : Package Token : AAAAAZBZ Package Sectno : 203 Reopt value : none Incremental Bind : no Eff isolation : CS Eff degree : 0 Eff locktimeout : -1 Stmt unicode : no Stmt query ID : 0 Stmt nesting level : 0 Stmt invocation ID : 0 Stmt source ID : 0 Stmt pkgcache ID : 4380866641921 Stmt type : Dynamic Stmt operation : DML, Insert/Update/Delete Stmt text : UPDATE DEPARTMENT SET DEPTNAME = 'PLANNING 1' WHERE DEPTNO = 'B01' Past Activities of Participant No 2 ------------------------------------- Past Activities wrapped: no Activity ID : 1 Uow ID : 1 Package Name : SQLC2H20 Package Schema : NULLID Package Version : Package Token : AAAAAZBZ Package Sectno : 203 Reopt value : none Incremental Bind : no Eff isolation : CS Eff degree : 0 Eff locktimeout : -1 Stmt unicode : no Stmt query ID : 0 Stmt nesting level : 0 Stmt invocation ID : 0 Stmt source ID : 0 Stmt pkgcache ID : 3173980831746 Stmt type : Dynamic Stmt operation : DML, Insert/Update/Delete Stmt text : UPDATE DEPARTMENT SET DEPTNAME = 'OPERATIONS 1' WHERE DEPTNO = 'E11'
删除锁事件监控器
不再需要锁事件监控器时，可以将其停用并删除。如果需要锁事件监控器来分析可能的并发性问题，就只需将其停用。删除锁事件监控器不会隐式删除对应的非格式化事件表。如果不需要保留所收集的锁事件数据用作将来的分析，必须使用 DROP TABLE 语句手动删除非格式化事件表，如清单 22 所示。
清单 22. 删除锁事件监控器
db2 "SET EVENT MONITOR EVMON_LOCKING STATE 0" db2 "DROP EVENT MONITOR EVMON_LOCKING" db2 "DROP TABLE EMDATA.TAB_LOCKING"
结束语
本文介绍了如何使用 DB2 LUW 9.7 中引入的新的锁事件监控器。本文通过实例演示了如何使用锁事件监控器来收集所有种类的并发性问题：死锁、锁超时及锁等待。使用新的格式化工具 db2evmonfmt，收集的锁事件信息可以通过这种方式格式化，让确认某种锁问题的原因变得更简单。DB2 LUW 9.7 提供了一种集成方式来分析所有种类的并发性问题，它也取代了以前 DB2 LUW 发行版中使用的锁分析的方法。
5916
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
