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

回顾DB2 9.1中的锁定超时分析

使用db2pd工具和db2cos脚本进行锁定超时分析的方法包含以下几步：

1.使用一个特殊的DB2脚本 — 名为 db2cos — 在每次调用db2cos脚本时执行一个db2pd调用。db2pd 调用收集与锁定、事务、应用程序、语句缓存相关的信息，并将信息存储在一个文本文件中以供分析。

2.要在锁定超时发生时自动执行 db2cos 脚本（以及包含的 db2pd 命令，需要使用 db2pdcfg 命令注册锁定超时事件。

3.在锁定超时事件中，DBA 可以检查通过自动调用 db2cos 脚本生成的 db2pd 输出。这使 DBA 能够确定发生锁定争用的原因，从而设法在以后避免发生这类情形。

尽管其中介绍的方法提供了大量信息，使得锁定超时分析比以前的 DB2 9 for Linux, Unix, and Windows 版本更加简单，但它仍然存在一些不足：

◆使用此方法需要手动改写 db2cos 脚本并通过调用 db2pdcfg 来注册锁定超时事件。两个步骤都不复杂，但是对于新手来说可能有些困难。

◆解释 db2pd 输出以识别锁定超时情形中涉及的应用程序和 SQL 语句，这个任务并不容易，而且需要一些尝试。

◆如果锁定超时是由包含多个 SQL 命令的事务引起的，那么 db2pd 收集的信息可能还不足以确定导致锁定的 SQL 语句。

DB2 9.5 中全新的锁定超时报告功能

在 DB2 9.5 的锁定超时报告功能中，引入了一个新特性，使得锁定超时分析变得非常简单。要激活锁定超时报告，只需将 DB2 注册变量 DB2_CAPTURE_LOCKTIMEOUT 设置为 ON，并重新启动您的 DB2 实例：

清单 1. 激活 DB2 9.5 中的锁定超时报告

db2set DB2_CAPTURE_LOCKTIMEOUT=ON

db2stop

db2start

是的，就是这么简单。当 DB2_CAPTURE_LOCKTIMEOUT 设置为 ON 时，DB2 为每个锁定超时事件自动创建一个报告文件。报告文件保存在 DIAGPATH 数据库管理员配置（DBM CFG）参数指向的目录中，包含的信息有：锁定超时的日期和时间、存在问题的锁定、锁定请求程序和锁定拥有者。

那么 DB2 9.5 中就不使用 db2cos 脚本了吗？事实并非如此。将 db2cos 脚本和 db2pd 工具结合具有非常广泛的用途。这意味着，这些工具组合仍然可用于捕捉与 SQL 代码和 DB2 内部返回代码相关的任何 DB2 事件信息，而不仅仅是锁定超时信息。

现在看看新的 DB2 9.5 注册变量 DB2_CAPTURE_LOCKTIMEOUT 并查看一个使用 DB2 SAMPLE 数据库的锁定超时示例场景。如果 SAMPLE 数据库不存在，可以调用下面的命令创建一个：

清单2. 创建 SAMPLE 数据库

db2sampl

只有当数据库配置（DB CFG）参数 LOCKTIMEOUT 未被设置为 -1 时，锁定超时才会发生。-1 意味着一个应用程序可能在无限期地等待一个必需的锁定，在一些情形中，这并不是期望发生的行为，但是 -1 是 LOCKTIMEOUT 的默认设置。对于此示例场景，假设 LOCKTIMEOUT 被更改为 10 秒（LOCKTIMEOUT 的值以秒为单位）：

清单3.更改LOCKTIMEOUT的值

db2 "UPDATE DB CFG FOR SAMPLE USING LOCKTIMEOUT 10"

为了促使一个锁定超时错误发生，我们首先建立一个数据库连接，并执行一些 SQL 语句来模拟表 EMPLOYEE 上的真实事务：

清单4. 第一个事务将每个员工的工资提高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"

到目前为止，这个事务包含一个 UPDATE 命令，该命令将每个员工的工资提高 2%。然后，使用 SELECT 语句查询新的工资。注意，通过为 DB2 命令行处理程序（CLP）调用指定 +c 选项，我们禁用了自动提交功能。UPDATE 语句会导致 EMPLOYEE 表中的每行上发生一个独占（X）锁。这些独占锁会一直持续下去，直到使用 COMMIT 或 ROLLBACK 语句结束事务。

不需要结束事务，在一个单独的 shell 中建立第二个数据库连接并开始另一个事务：

清单5. 第二个事务为每位经理提供 10%（取决于他们的工资)的奖金

db2 "CONNECT TO SAMPLE"

db2 +c "UPDATE EMPLOYEE SET BONUS = SALARY * 0.1 WHERE JOB = 'MANAGER'"

这个事务的用途是，根据每位经理的当前工资，为他们提供 10% 的奖金。由于 EMPLOYEE 表中的所有行都被第一个事务独占锁定，第二个应用程序进入了锁等待模式。10 秒钟之后（还记得 LOCKTIMEOUT 的设置吧），就会发生预期的锁定超时。

目前为止还没有出现什么新内容。但是由于 DB2 注册变量 DB2_CAPTURE_LOCKTIMEOUT 被设置为 ON，锁定超时报告处于激活状态，DB2 会在 DIAGPATH 目录中自动生成锁定超时报告。注意，DB2 9.5 for Windows 中默认的 DIAGPATH 发生了改变。如果 DIAGPATH 参数未设置，DIAGPATH 会指向目录 DB2INSTPROF\DB2INSTANCE，其中 DB2INSTPROF 是实例目录的位置，而 DB2INSTANCE 是 DB2 实例的名称。要确定存储在 DB2INSTPROF 中的路径，可以通过执行 db2set -all 命令显示 DB2 注册内容。如果在默认实例中创建 SAMPLE 数据库，那么 DB2INSTANCE 的值为 DB2。报告文件的名称为 db2locktimeout.dbpartition.agentid.timestamp，对于单分区的数据库，其中的 dbpartition 始终为 0。

DB2 生成的锁定超时报告如下所示：

清单6. 锁定超时报告

LOCK TIMEOUT REPORT

Date: 03/01/2008

Time: 07:34:31

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-38]

Application ID: *LOCAL.DB2.080103063343

Application Name: db2bp.exe

Requesting Agent ID: 5232

Coordinator Agent ID: 5232

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-33]

Application ID: *LOCAL.DB2.080103063332

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: Not available

锁定超时报告包括 4 部分：

◆第一部分提供与锁定超时发生的日期和时间，以及相应的实例和数据库相关的信息。 ◆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 期待已久的功能，而且将大大简化对锁定超时的分析。