DB2 for Linux, UNIX, and Windows 的锁事件,第 1 部分: 分析 DB2 for Linux, UNIX, and Windows 中的锁等待情形
使用 db2pd 工具确定并发问题的原因
db2pd
是用于监视各种 DB2 数据库活动以及故障排除的实用程序。它是从 DB2 V8.2 开始随 DB2 引擎发布的一个独立的实用程序,其外观和功能类似于 Informixonstat
实用程序。db2pd
是从命令行以一种可选的交互模式执行的。该实用程序运行得非常快,因为它不需要获取任何锁,并且在引擎资源以外运行(这意味着它甚至能在一个挂起的引擎上工作)。通过快照监视还可以收集db2pd
提供的很多监视器数据,但是db2pd
和快照监视的输出格式却有很大不同。这使 DBA 可以选择更符合用户需求的监视替代方法。本文关注用于锁监视的db2pd
选项。有一篇由 Sam Poon 撰写的 developerWorks 文章(参见参考资料 小节)对 db2pd
的监视功能作了更广泛的介绍。
下面的图展示了用于锁监视的 db2pd
选项:
图 1. 用于锁监视的
db2pd
选项
![db2pd 锁选项](https://i-blog.csdnimg.cn/blog_migrate/af8c94be3856e88fbded64c2c31d5281.png)
TranHdl
:用于指定事务句柄,以便只监视由特定事务持有的锁。showlocks
:这个子选项将锁名称扩展成有意义的解释。对于一个行锁,该选项显示以下信息:表空间 ID、表 ID、分区 ID、页和槽。通过使用编目视图SYSCAT.TABLES
上的一个查询,很容易将表空间 ID 和表 ID 映射到相应的表名:
清单 1. 将表空间 ID、表 ID 映射到表模式、表名
SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE TBSPACEID = tbspaceid AND TABLEID = tableid
wait
:如果指定wait
子选项,则db2pd
只显示事务当前正在等待的锁,以及对等待情形负责的锁。这个子选项大大简化了锁等待分析,因为它将输出限制为参与锁等待情形的锁。db2pd database
和file
选项不是特定于锁监视的,但是适用于(几乎)所有db2pd
调用。database
选项将db2pd
返回的监视器数据限制为某个数据库的监视器数据。而file
选项则允许定义一个文件,以便将db2pd
输出写到该文件。
接下来,我们开始使用前面介绍的 db2pd
选项来分析一个示例锁等待情形。为此,我们创建 DB2 SAMPLE
数据库:
清单 2. 创建
SAMPLE
数据库
db2sampl |
用户 A 执行事务 A,以根据每个经理的薪水为他们提供 10% 的奖金:
清单 3. 事务 A 执行的更新操作
UPDATE EMPLOYEE SET BONUS = SALARY * 0.1 WHERE JOB = 'MANAGER' |
当事务 A 仍然在运行(因为用户 A 还没有使用 COMMIT
或 ROLLBACK
终止该事务)时,用户 B 执行事务 B,以将每个雇员的薪水提高 2%:
清单 4. 事务 B 执行的更新操作
UPDATE EMPLOYEE SET SALARY = SALARY * 0.02 |
由于事务 B 没有完成,用户 B 请求 DBA 确定问题的原因。于是,DBA 调用 db2pd
,看是否存在锁等待情形:
清单 5. 检查锁等待情形
db2pd -db sample -locks wait showlocks Database Partition 0 -- Database SAMPLE -- Active -- Up 3 days 08:33:05 Locks: Address TranHdl Lockname Type Mode Sts Owner Dur 0x050A0240 6 02000600050040010000000052 Row ..X W 2 1 0x050A0DB0 2 02000600050040010000000052 Row ..X G 2 1 HoldCount Att ReleaseFlg 0 0x00 0x40000000 TbspaceID 2 TableID 6 PartitionID 0 Page 320 Slot 5 0 0x00 0x40000000 TbspaceID 2 TableID 6 PartitionID 0 Page 320 Slot 5 |
db2pd
报告 ID 为 2 的表空间中一个 ID 为 6 的表上有一个行锁存在锁等待情形。通过检查 SYSCAT.TABLES
,DBA 断定表EMPLOYEE
上的确存在锁等待。
清单 6. 确定锁等待情形所涉及的表
SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE TBSPACEID = 2 AND TABLEID = 6 TABSCHEMA TABNAME -------------------------------------------------------------------------------- FECHNER EMPLOYEE 1 record(s) selected. |
对于事务 2(列 TranHdl
),db2pd -locks
输出的 status 列(Sts
)显示一个 “G”。G 代表 “granted”,意即事务句柄为 2 的事务拥有行锁。此外,列Mode
表明,事务 2 持有的是一个 X 锁。等待的事务(列Sts
中显示 “W”(“wait”)的事务)是句柄为 6 的事务。该事务正在与事务 2 请求同一个行上的 X 锁。通过查看Owner
列(显示事务 2 是锁的所有者)和比较Lockname
(对于 db2pd -locks
中的两个条目是相同的),可以看到这一点。
接下来,DBA 将事务句柄映射到应用程序。这可以使用另一个 db2pd
选项 -transactions
来完成:
清单 7. 将事务句柄映射到应用程序
db2pd -db sample -transactions Database Partition 0 -- Database SAMPLE -- Active -- Up 3 days 08:34:47 Transactions: Address AppHandl [nod-index] TranHdl Locks State Tflag Tflag2 0x05141880 30 [000-00030] 2 9 WRITE 0x00000000 0x00000 0x05144880 34 [000-00034] 6 5 WRITE 0x00000000 0x00000 |
这个 db2pd
调用的输出表明,事务 2(列 TranHdl
)是由应用程序 30(列 AppHandl
)执行的,而事务 6 是由应用程序 34 执行的。这两个事务都正在对数据库执行写更改(列State
= WRITE)。所以 DBA 现在知道,应用程序 30 正持有应用程序 34 所等待的锁。
要获得关于锁等待情形涉及的应用程序的更多信息,可使用 -agents
选项调用 db2pd
。该选项打印代表应用程序运行的代理的信息。注意,-agents
是一个实例级选项,这意味着不需要指定一个数据库(实际上,当指定一个数据库时,db2pd
打印出一条警告,并忽略 database 选项)。
清单 8. 获得关于应用程序和相应代理的信息
db2pd -agents Database Partition 0 -- Active -- Up 3 days 08:35:42 Agents: Current agents: 2 Idle agents: 0 Active coord agents: 2 Active agents total: 2 Pooled coord agents: 0 Pooled agents total: 0 Address AppHandl [nod-index] AgentTid Priority Type State 0x04449BC0 34 [000-00034] 3392 0 Coord Inst-Active 0x04449240 30 [000-00030] 2576 0 Coord Inst-Active ClientPid Userid ClientNm Rowsread Rowswrtn LkTmOt DBName 3916 USER_B db2bp.ex 43 43 NotSet SAMPLE 2524 USER_A db2bp.ex 153 14 NotSet SAMPLE |
在 db2pd -agents
输出中,DBA 可以看到使用应用程序 30 和 34 的用户的 ID(列 Userid
):应用程序 30 是由 USER_A 执行的,而应用程序 34 是由 USER_B 执行的。只有当每个用户都有一个单独的数据库授权 ID 时,才可能出现那样的应用程序与用户 ID 之间的映射。通常,这对于在应用服务器上运行的应用程序是不可能的,因为这些应用程序使用连接池,连接不是个人化的。
关于每个应用程序的更多信息则由 db2pd
选项 -applications
提供:
清单 9. 获得关于应用程序的更多信息
db2pd -db sample -applications Database Partition 0 -- Database SAMPLE -- Active -- Up 3 days 08:36:14 Applications: Address AppHandl [nod-index] NumAgents CoorTid Status 0x04AF8080 34 [000-00024] 1 3940 Lock-wait 0x03841960 30 [000-00020] 1 2548 UOW-Waiting C-AnchID C-StmtUID L-AnchID L-StmtUID Appid 195 1 0 0 *LOCAL.DB2.061122195637 0 0 60 1 *LOCAL.DB2.061122195609 |
Status
列确认了 DBA 已经知道的一些东西:应用程序 34 处在锁等待状态。但是这并不新鲜,于是 DBA 将注意力集中在列C-AnchID/C-StmtUID
和L-AnchID/L-StmtUID
上。“C” 代表当前(current),“L” 代表最近(last)的锚 ID/语句 UID。这些 ID 可用于标识应用程序最近执行的 SQL 语句和应用程序当前执行的语句。为此,可以用-dynamic
选项调用db2pd
。该选项显示数据库动态语句缓存的内容:
清单 10. 检查动态语句缓存的内容
db2pd -db sample -dynamic Database Partition 0 -- Database SAMPLE -- Active -- Up 3 days 08:37:39 Dynamic Cache: Current Memory Used 187188 Total Heap Size 1271398 Cache Overflow Flag 0 Number of References 2 Number of Statement Inserts 3 Number of Statement Deletes 0 Number of Variation Inserts 2 Number of Statements 3 Dynamic SQL Statements: Address AnchID StmtUID NumEnv NumVar NumRef NumExe 0x056CEBD0 60 1 1 1 1 1 0x056CE850 180 1 0 0 0 0 0x056CFEA0 195 1 1 1 1 1 Text UPDATE EMPLOYEE SET BONUS = SALARY * 0.1 WHERE JOB = 'MANAGER' SET CURRENT LOCALE LC_CTYPE = 'de_DE' UPDATE EMPLOYEE SET SALARY = SALARY * 0.02 Dynamic SQL Environments: Address AnchID StmtUID EnvID Iso QOpt Blk 0x056CECD0 60 1 1 CS 5 B 0x056D30A0 195 1 1 CS 5 B Dynamic SQL Variations: Address AnchID StmtUID EnvID VarID NumRef Typ 0x056CEEB0 60 1 1 1 1 4 0x056D3220 195 1 1 1 1 4 Lockname 010000000100000001003C0056 01000000010000000100C30056 |
-applications
输出与 -dynamic
输出之间的映射很简单:
应用程序 34(处于锁等待状态)当前正在执行当前锚 ID 195 和当前语句 ID 1 所标识的 SQL 语句。在 db2pd -dynamic
输出的Dynamic SQL Statements
部分中,那些 ID 可以映射到以下 SQL 语句:
清单 11. 应用程序 34 执行的 SQL 语句
UPDATE EMPLOYEE SET SALARY = SALARY * 0.02 |
持有锁的应用程序 30 最近执行的 SQL 语句是最近锚 ID 60 和最近语句 ID 1 所标识的 SQL 语句。那些 ID 可以映射到以下 SQL 语句:
清单 12. 应用程序 30 执行的 SQL 语句
UPDATE EMPLOYEE SET BONUS = SALARY * 0.1 WHERE JOB = 'MANAGER' |
注意,db2pd -dynamic
输出包含另一个通常难以发现的有趣信息:Dynamic SQL Environments
部分的列Iso
中显示了被执行的动态 SQL 语句的隔离级别(UR = Uncommitted Read,CS = Cursor Stability,RS = Read Stability,RR = Repeatable Read)。
我们来总结一下 DBA 就用户 B 的应用程序被挂起的原因有什么发现:
- 挂起是由表
EMPLOYEE
上一个独占式的行锁导致的。 - 持有锁的事务属于用户 A 执行的一个应用程序。而用户 B 的事务正在等待那个锁。
- 两条有冲突的语句都是表
EMPLOYEE
上的UPDATE
语句。
有了这些信息,DBA 可以开始采取一些必要的步骤来解决锁等待状况,例如建议用户 A 终止事务,或者强制关闭用户 A 的应用程序。此外,可以采取措施避免将来出现那样的状况,例如配置 DB2 控制器(governor),使之自动终止运行时间过长的事务。
在这个示例场景中,db2pd
被连续执行数次,每次使用一个单独的选项。现实中不会出现这样的情况。相反,db2pd
只被调用一次,调用时同时使用前面介绍的所有选项:
清单 13. 分析锁等待情形所需的带有所有选项的单个
db2pd
调用
db2pd -db sample -locks wait showlocks -transactions -agents -applications -dynamic -file db2pd.out -repeat 15 40 |
产生的输出由针对每个选项的输出组成,各部分输出之间的顺序与各选项在 db2pd
调用中的顺序一致。而且,请注意 db2pd
调用最后的 2 个附加选项:
-file
表明db2pd
输出应该被写到一个文件。在示例调用中,输出被写到文件db2pd.out
中。-repeat
表明db2pd
应该每隔 15 秒执行一次,共执行 40 次(即每隔 15 秒执行一次,共执行 10 分钟)。每次执行的输出被附加到-file
选项指定的文件后面。
-file
和 -repeat
选项对于在一段时间内监视数据库活动比较有用。对于锁等待分析,这两个选项可以帮助捕捉只存在一小段时间的锁等待情形。例如,如果数据库参数LOCKWAIT
被设置为 20 秒,一个等待锁的事务在过了 20 秒的等待时间后被回滚。为了捕捉那样的锁等待情形,db2pd
的时间间隔必须设置为比 20 秒更短的时间间隔,例如例子中的 15 秒。
有时候,锁等待情形会导致锁超时,而锁超时又会导致事务被回滚。锁等待导致锁超时所需的时间段由数据库配置参数 LOCKTIMEOUT
指定。锁超时分析最大的问题是,不知道下一次的锁超时何时发生。为了捕捉死锁,可以创建一个死锁事件监视器。每当出现死锁时,这个死锁事件监视器便写一个条目。但是,对于锁超时就没有类似的事件监视器。所以到 DB2 9® 为止,捕捉锁超时的惟一方法还是连续的db2pd
或快照监视(对于db2pd
,和前面解释的一样,-file
和 -repeat
选项可用于连续的锁监视)。
DB2 9 包含了一种新的机制,用于在数据库出现故障或发生事件时收集监视器数据:db2cos
脚本。为了捕捉锁超时事件,可以配置数据库,使之每当出现锁超时时启动db2cos
脚本。在db2cos
脚本中,和前面讨论的一样,可以以相同的选项调用 db2pd
。我们来看一个示例场景,该场景演示了如何用db2cos
脚本捕捉锁超时。
对于这个场景,假设 DBA 将数据库锁超时值设为 10 秒:
清单 14. 更新锁超时设置
UPDATE DB CFG FOR SAMPLE USING LOCKTIMEOUT 10 |
为了每当出现锁超时时启动 db2cos
脚本,DBA 调用 db2pdcfg
实用程序,如下所示:
清单 15. 使用
db2pdcfg
配置 db2cos
脚本的调用
db2pdcfg -catch locktimeout count=1 |
-catch
选项指定应该自动导致调用 db2cos
脚本的故障或事件。对于锁超时事件,可以指定字符串 locktimeout
。或者,可以指定与锁超时相应的 SQL 错误码和原因码:
清单 16. 用于捕捉锁超时的另一种
db2pdcfg
调用
db2pdcfg -catch 911,68 count=1 |
除了一些字符串值和 SQL 代码之外,db2pdcfg
还接受内部 DB2 错误码。所以,用这种方式可以捕捉很多数据库故障和事件。锁超时事件只是使用db2pdcfg
和db2cos
的一个例子。
如果 count
子选项的值为 1,则表明当出现锁超时事件时应该执行 db2cos
脚本。
db2pdcfg
通过以下输出确认错误捕捉的设置:
清单 17.
db2pdcfg
对错误捕捉设置的确认
Error Catch #1 Sqlcode: 0 ReasonCode: 0 ZRC: -2146435004 ECF: 0 Component ID: 0 LockName: Not Set LockType: Not Set Current Count: 0 Max Count: 1 Bitmap: 0x4A1 Action: Error code catch flag enabled Action: Execute sqllib/db2cos callout script Action: Produce stack trace in db2diag.log |
db2diag.log
报告中也包括错误捕捉设置。可以使用 db2diag
实用程序(用于检查 db2diag.log
内容的一个有用的实用程序)过滤 db2diag.log
文件,而不必在一个文本编辑器中打开它:
清单 18. 在
db2diag.log
中确认错误捕捉设置
db2diag -g funcname:=pdErrorCatch 2006-12-18-13.37.25.177000+060 I727480H285 LEVEL: Event PID : 4648 TID : 3948 PROC : db2syscs.exe INSTANCE: DB2 NODE : 000 FUNCTION: DB2 UDB, RAS/PD component, pdErrorCatch, probe:30 START : Error catch set for ZRC -2146435004 |
ZRC -2146435004 是用于锁超时的 DB2 内部错误码。可以通过下面的 db2diag
调用查看这些错误码:
清单 19. 使用
db2diag
查看 DB2 内部错误码的含义
db2diag -rc -2146435004 |
通过使用 db2pdcfg
,数据库引擎现在被配置为每当出现锁超时时调用 db2cos
脚本。db2cos
脚本收集判别锁超时原因所需的所有监视器信息。为此,DBA 必须修改db2cos
脚本,以便用已知的选项调用db2pd
。可以在下面的子目录中找到 db2cos
脚本:
- Windows:
DB2 install directory\BIN\db2cos.bat
,例如C:\Program Files\IBM\SQLLIB\BIN\db2cos.bat
- UNIX/Linux:
Instance owner home/sqllib/bin/db2cos
在 Microsoft Windows® 上,默认的 db2cos.bat
脚本看上去如下所示:
清单 20. Windows 上默认
db2cos.bat
的内容
setlocal :iterargs if %0. == . goto iterdone if /i %0. == INSTANCE. set INSTANCE=%1 if /i %0. == DATABASE. set DATABASE=%1 if /i %0. == TIMESTAMP. set TIMESTAMP=%1 if /i %0. == APPID. set APPID=%1 if /i %0. == PID. set PID=%1 if /i %0. == TID. set TID=%1 if /i %0. == DBPART. set DBPART=%1 if /i %0. == PROBE. set PROBE=%1 if /i %0. == FUNCTION. set FUNCTION=%1 if /i %0. == REASON. set REASON=%1 if /i %0. == DESCRIPTION. set DESCRIPTION=%1 if /i %0. == DiAGPATH. set DIAGPATH=%1 shift goto iterargs :iterdone if %DATABASE%. == . goto no_database db2pd -db %DATABASE% -inst >> %DIAGPATH%\db2cos%PID%%TID%.%DBPART% goto exit :no_database db2pd -inst >> %DIAGPATH%\db2cos%PID%%TID%.%DBPART% :exit |
对于数据库级的事件或故障,默认的 db2cos
脚本用 -db
和 -inst
选项调用db2pd
。DBA 用一个db2pd
调用替换相应的行,该调用收集锁超时分析所需的监视器数据:
清单 21. 更改
db2cos
脚本,以收集用于锁超时分析的数据
if %DATABASE%. == . goto no_database db2pd -db %DATABASE% -locks wait -transactions -agents -applications -dynamic >> %DIAGPATH%\db2cos%PID%%TID%.%DBPART% goto exit |
现在,db2cos
脚本已准备好,DBA 可以坐等下一次锁超时事件的发生。
假设像之前描述的那样,用户 A 与 B 之间发生相同的锁情形。但是,这一次设置了 LOCKTIMEOUT
,因此过了 10 秒(LOCKTIMEOUT
= 10)之后用户 B 的事务被回滚。用户 B 通知 DBA 回滚自己的事务,并且收到 SQL 错误消息 -911 和原因码 68(SQL code -911 / reason code 68 = locktimeout)。于是,DBA 检查通过自动调用db2cos
脚本收集到的监视器数据。
首先,DBA 用锁超时内部错误码调用 db2diag
,以确定锁超时发生的确切时间:
清单 22. 在
db2diag.log
中检查锁超时事件的时间点
db2diag -g data:=-2146435004 2006-12-18-14.27.24.656000+060 I6857H409 LEVEL: Event PID : 2968 TID : 2932 PROC : db2syscs.exe INSTANCE: DB2 NODE : 000 DB : SAMPLE APPHDL : 0-21 APPID: *LOCAL.DB2.061226132544 AUTHID : FECHNER FUNCTION: DB2 UDB, lock manager, sqlplnfd, probe:999 DATA #1 : <preformatted> Caught rc -2146435004. Dumping stack trace. |
db2diag.log
条目显示,在 2006-12-18-14.27.24.656000 时发生了一次锁超时。由于 db2cos
脚本将它的输出写到 %DIAGPATH%
中的 db2cos%PID%%TID%.%DBPART%
文件中,DBA 有望在实例的诊断路径中找到一个db2cos29682932.0
文件:
%DIAGPATH%
= instance's diagnostic path = on Windows by defaultC:\Program Files\IBM\SQLLIB\DB2
%PID%
= process ID = 2968(如db2diag.log
条目中所示)%TID%
= thread ID = 2932(也显示在db2diag.log
条目中)%DBPART%
= database partition = 0(在一个非分区数据库环境中)
那个文件的内容很像本文第一部分中逐步考察的那个 db2pd
监视器输出,DBA 可以通过它来识别锁超时的原因。
捕捉到锁超时后,DBA 可以通过 -catch clear
选项调用 db2pdcfg
来禁用 db2cos
脚本:
清单 23. 再次使用
db2pdcfg
清除错误捕捉设置
db2pdcfg -catch clear All error catch flag settings cleared. |
本文演示了如何使用 db2pd
实用程序进行锁等待监视。本文借助一个示例场景展示了 DBA 如何通过检查不同 db2pd
选项的输出来识别并发问题的原因。从 DB2 9 开始,可以将db2pd
与新的db2cos
脚本一起使用,以便在出现锁超时事件时捕捉它们。您学习了如何配置出现锁超时事件时db2cos
脚本的自动调用。本文还介绍了db2diag
实用程序 —— 这是用于检查 db2diag.log
内容的一个有用的工具。
总结就是:Locks TranHdl -->Transactions AppHandl --> Applications AnchID --> Dynamic SQL Statements TEXT