深入探讨DB2 9.5中的锁定超时分析新方法

◆第一部分提供与锁定超时发生的日期和时间，以及相应的实例和数据库相关的信息。

◆Lock Information 部分显示导致锁定超时的锁。除了内部锁名以外，还会显示锁的类型（行锁或表锁）和表信息。要确定表名称，需要使用给定的表空间 ID 和表 ID 查询编目视图 SYSCAT.TABLES：

清单7.将表空间 ID/表 ID 映射到表名称

SELECT TABSCHEMA, TABNAME

FROM SYSCAT.TABLES

WHERE TBSPACEID = tbspaceid AND TABLEID = tableid

◆发生锁定超时的应用程序在 Lock Requestor 部分中描述。这部分还包含一些更有趣的条目：用于连接到数据库的身份验证 ID、应用程序名称、请求的锁模式（以及该锁是否由一个锁升级引起）、需要锁的语句的隔离级别，以及 SQL 语句文本本身。

◆最后一部分 Lock Owner (Representative) 列出持有有问题的锁的应用程序。使用另一个应用程序，还可以查看身份验证 ID、应用程序名称和锁模式等信息。在一些情形下，可能不止一个应用程序持有（共享）一个锁，这会阻塞对锁的请求。在这些情形下，锁定超时报告中只会显示一个锁持有者。这就是这部分具有额外的 Representative 的原因。

在本文开始部分，我们提到了使用 db2cos 和 db2pd 进行锁定超时分析的三点不足。第一点是可用性。结合使用 db2cos 和 db2pd 的方法需要执行一些步骤来设置锁定超时监视。新的方法简单得多，只需设置 DB2_CAPTURE_LOCKTIMEOUT=ON。第二点不足是复杂性，因为它需要进行一些尝试来读取 db2pd 输出和关联不同的 db2pd 部分。使用新的方法，DB2 会生成一个报告文件，其中包含所有必要的信息。但是如何解决最后一点不足：锁定超时情形涉及的 SQL 语句的信息不够充分？目前为止，您只知道被现有的锁定阻塞的 SQL 语句，但是一点都不了解导致锁定的语句。对于这一点，新的锁定超时报告功能也提供了改进。现在看看它的工作原理。

收集SQL语句的历史信息

为了获得锁持有者的应用程序执行的 SQL 语句的信息，我们使用 DETAILS HISTORY 选项创建一个死锁事件监视器并激活它。例如，可以通过如下方法创建一个恰当的死锁事件监视器并将其激活：

清单 8. 使用 DETAILS HISTORY 选项创建死锁事件监视器

db2 "CREATE EVENT MONITOR evmondeadlock FOR DEADLOCKS WITH DETAILS HISTORY

WRITE TO FILE 'path'"

db2 "SET EVENT MONITOR evmondeadlock STATE 1"

您可能会问：“为什么需要死锁事件监视器来监视锁定超时？”答案是构建锁定超时报告需要用到死锁事件监视器代码交付的功能。使用 DETAILS HISTORY 选项创建死锁事件监视器时，DB2 跟踪已经在事务中执行的 SQL 语句。如果发生死锁或锁定超时，这个信息可用于提供 SQL 语句的历史信息，这些 SQL 语句可能与死锁或锁定超时的发生有关。

激活了死锁事件监视器之后，再次运行上面描述的锁定超时场景。这次 DB2 编写一个锁定超时报告，如清单 9 所示：

清单9. 包含 SQL 语句历史信息的锁定超时报告

LOCK TIMEOUT REPORT

Date: 03/01/2008

Time: 15:10:13

Instance: DB2

Database: SAMPLE

Database Partition: 0

Lock Information:

Lock Name: 02000600040040010000000052

Lock Type: Row

Lock Specifics: Tablespace ID=2, Table ID=6, Row ID=x0400400100000000

Lock Requestor:

System Auth ID: FECHNER

Application Handle: [0-202]

Application ID: *LOCAL.DB2.080103140934

Application Name: db2bp.exe

Requesting Agent ID: 2356

Coordinator Agent ID: 2356

Coordinator Partition: 0

Lock timeout Value: 10000 milliseconds

Lock mode requested: ..U

Application Status: (SQLM_UOWEXEC)

Current Operation: (SQLM_EXECUTE_IMMEDIATE)

Lock Escalation: No

Context of Lock Request:

Identification: UOW ID (1); Activity ID (1)

Activity Information:

Package Schema: (NULLID )

Package Name: (SQLC2G13NULLID )

Package Version: ()

Section Entry Number: 203

SQL Type: Dynamic

Statement Type: DML, Insert/Update/Delete

Effective Isolation: Cursor Stability

Statement Unicode Flag: No

Statement: UPDATE EMPLOYEE SET BONUS = SALARY * 0.1

WHERE JOB = 'MANAGER'

Lock Owner (Representative):

System Auth ID: FECHNER

Application Handle: [0-188]

Application ID: *LOCAL.DB2.080103140511

Application Name: db2bp.exe

Requesting Agent ID: 5488

Coordinator Agent ID: 5488

Coordinator Partition: 0

Lock mode held: ..X

List of Active SQL Statements: Not available

List of Inactive SQL Statements from current UOW:

Entry: #1

Identification: UOW ID (6); Activity ID (2)

Package Schema: (NULLID )

Package Name: (SQLC2G13)

Package Version: ()

Section Entry Number: 201

SQL Type: Dynamic

Statement Type: DML, Select (blockable)

Effective Isolation: Cursor Stability

Statement Unicode Flag: No

Statement: SELECT LASTNAME, FIRSTNME, SALARY FROM EMPLOYEE

ORDER BY LASTNAME ASC

Entry: #2

Identification: UOW ID (6); Activity ID (1)

Package Schema: (NULLID )

Package Name: (SQLC2G13)

Package Version: ()

Section Entry Number: 203

SQL Type: Dynamic

Statement Type: DML, Insert/Update/Delete

Effective Isolation: Cursor Stability

Statement Unicode Flag: No

Statement: UPDATE EMPLOYEE SET SALARY = SALARY * 1.02

这个锁定超时报告的开始部分与前面看到的相同。但是，这次的 Lock Owner 部分包含额外的、有价值的信息。在标题 List of Inactive SQL Statements from current UOW 下边，可以看到在发生锁定超时之前锁持有者的事务执行的所有 SQL 语句。从这组 SQL 语句中，可以找到导致问题锁定的语句。在这个场景中，使用 UPDATE 语句增加每个员工的工资。

注意，这个功能是对结合使用 db2cos 和 db2pd 方法的一个重大改进。使用 db2cos 与 db2pd 相结合的方法，只能看到锁持有者的应用程序执行的最后一条语句 — 在这个场景中是对 EMPLOYEE 表的查询。但是由于查询并没有导致出现问题的独占锁，您仍然不知道是哪条语句导致了锁定。使用新方法 — DB2_CAPTURE_LOCKTIMEOUT 和死锁事件监视器 — 您拥有在锁拥有者的事务中执行的所有 SQL 语句的历史信息，这就可以将 UPDATE 确定为相关的语句。

使用锁定超时报告的提示

带有语句历史功能的死锁事件监视器适用于所有应用程序，会增加 DB2 数据库管理程序对监视器堆的大量使用。所以应该谨慎使用。您应该始终首先设置 DB2_CAPTURE_LOCKTIMEOUT=ON，然后只在必要的时候使用 DETAILS HISTORY 选项激活死锁事件监视器。

使用锁定超时报告时，您可能会注意到，DIAGPATH 中的锁定超时报告文件的数量在持续增加。DB2 不会删除这些报告文件，所以 DBA 需要删除它们或者将它们移动到不同的位置，以便在 DIAGPATH 的文件系统上始终有足够的空间。

即使拥有了锁定超时报告功能，也不是总能够轻松确定出导致锁定超时的原因。例如，如果锁定超时由静态 SQL 或 DB2 内部锁定引起时，就没有那么容易确定原因。DB2 9.5 文档的 Lock timeout reporting 一章提供了这些局限性的一个简短列表（参见下面的 参考资料）。但是，DB2 9.5 中的锁定超时报告绝对是一个许多 DBA 期待已久的功能，而且将大大简化对锁定超时的分析。