DB2数据库锁超时问题的分析与排查

时间:2024-04-10 17:00:40

前言

DB2的锁管理机制一直为DB2应用开发人员和DBA所诟病。对其锁机制不理解的直接后果就是导致锁超时和死锁的发生。所以监控并分析锁超时和死锁,应是每个DB2 DBA的居家必备生存技能。本文记录了公司生产环境数据库发生锁超时后的分析、处理过程,如有表述不当之处,欢迎指正。

数据库环境

DB2 pureScale V10.5 FP5。

问题背景

我司生产环境某数据库每天下午15:40~15:50会报锁超时。经查明,问题发生时间正好是每日集中跑批时间。

DB2锁超时设置

为了能抓到锁超时的Requestor和Owner,需要对数据库进行适当配置。

设置TIMEOUT注册变量

db2set DB2_CAPUTRE_LOCKTIMEOUT=ON

一旦发生锁超时,会在~/sqllib/db2dump/DIAG000{0,1}/路径下面生成 db2locktimeout.1.5493.2017-05-25-15-49-01 文件。

设置db2cos

仅有db2locktimeout*文件中的信息不足以定位Lock Requestor的具体语句。因此,我们在生产环境还启用了db2cos脚本,捕获更详细的应用信息。该脚本位于 ~/sqllib/bin/db2cos中。

我们对触发命令进行了改造,以便抓取更多信息:
DB2数据库锁超时问题的分析与排查
“#”号注释的命令为原始命令,此处替换成下一行的新内容。

修改完脚本后,执行以下命令使脚本生效:

db2pdcfg -catch 911,68 count=255

一旦发生锁超时,便会触发db2cos脚本,在DIAG目录下面生成捕获的文件,
格式为:$pid.$eduid.$dbpart.db2pd.${database}.txt
例如:202484.5493.001.db2pd.PTSMGMDB.txt

分析锁超时报告

首先分析db2locktimeout.1.5493.2017-05-25-15-49-01 报告的内容:
DB2数据库锁超时问题的分析与排查
DB2数据库锁超时问题的分析与排查
报告里描述了Lock Requestor和Lock Owner详细信息,包括锁超时发生时间,锁类型(Table lock)和加锁的资源ID(obj={4;136})。通过表和表空间ID,我们可以执行以下语句定位被锁的表名:
DB2数据库锁超时问题的分析与排查
Lock Requestor应用程序名称为db2bp,它是CLP后端进程的名称。但是在db2locktimeout文件中,我们看不到Requestor的语句,这个可以通过查看db2cos脚本抓到的202484.5493.001.db2pd.PTSMGMDB.txt文件得到。

再来看Lock Owner,显示占用表的应用是package,可以知道package的名字PTSDAYOFFPUB和版本号V1.1.0_P25,并且当时执行了两条SQL语句11和12,可以通过SYSCAT.STATEMENTS查找执行语句内容,但是因为此查询可能会导致尝试更新SYSCAT.STATEMENTS目录视图的其他应用程序发生竞争,所以查询时最好使用UR隔离级别。
DB2数据库锁超时问题的分析与排查
可以发现只有编号位11的查询table是T_BASE_QWDZLSB ,因此可以确定该语句导致了对表加锁,其隔离级别为CS,会产生一个IS锁。

再来看下202484.5493.001.db2pd.PTSMGMDB.txt,定位Lock Requestor语句,原始文件中内容很多,我们对其进行了剪裁,提取出关键部分。

首先看Locks:段的信息:
DB2数据库锁超时问题的分析与排查

TranHdl 287的Sts为G,表明占有锁;TranHdl 272的Sts为W*,表明正在等待。接着根据Locks being waited on:段映射TranHdl和AppHandl,知道272的AppHandl为111774 。
DB2数据库锁超时问题的分析与排查

而111774 当前执行语句信息为C-AnchID和C-StmtUID为781和1,根据这两个值定位到Dynamic SQL Statements:段
DB2数据库锁超时问题的分析与排查

可以发现执行语句为LOCK TABLE t_base_qwdzlsb IN EXCLUSIVE MODE 。

同样可以在Application:段中查看更详细信息:
DB2数据库锁超时问题的分析与排查
DB2数据库锁超时问题的分析与排查

至此,可以总结导致锁超时的root cause:

PTSDAYOFFPUB执行了SELECT * from T_BASE_QWDZLSB语句,对表加了IS锁;而后在CLP中又执行了LOCK TABLE t_base_qwdzlsb IN EXCLUSIVE MODE语句(之后发现由IMPORT操作引起),请求一个X表锁。而IS和X锁是不兼容的,X锁会一直等待IS锁释放,超过系统设置的30秒超时时间,从而发生锁超时。
DB2数据库锁超时问题的分析与排查
DB2数据库锁超时问题的分析与排查

总结

1、定位造成锁超时的语句后,我们让项目组修改了package中的查询游标,加上WITH UR,但是锁超时依旧在报?这是为什么呢?

因为虽然显式地指定了SQL 语句的隔离级别为UR,但是,由于DB2中的游标是模糊游标 (没有显式地声明游标是只读的还是可更新的),因而系统会默认地将这个模糊游标当成可更新游标处理,在 UR 下,可更新游标的工作方式就像隔离级别为CS一样。要避免这种升级,可以采用以下办法:

修改应用程序中的游标,使游标变成非模糊游标。将 SELECT 语句更改为SELECT * from table_name FOR READ ONLY WITH UR子句。

2、在报告中看到了Lock Requestor的LOCK TABLE语句,未在脚本或程序里发现,是什么引起的?

经过分析,发现该语句是由IMPORT Utility引起。

IMPORT支持两种表锁定方式:脱机或ALLOW NO ACCESS方式;以及联机或ALLOW WRITE ACCESS方式。ALLOW NO ACCESS方式会阻止并行应用程序访问表数据,ALLOW WRITE ACCESS方式允许并行应用程序同时对导入目标表进行读写访问。如果未显示指定任何方式,那么IMPORT默认为ALLOW NO ACCESS,同时,默认情况下会使用RS绑定数据库,因此可以改造IMPORT语句:


  1. IMPORT FROM xx.ixf of ixf ALLOW WRITE ACCESS COMMITCOUNT 5000 INSERT INTO table_name

如上所述分析锁超时的步骤还是很繁琐的,而且需要一定的技巧和概念基础,需要:

  • 知道如何抓取锁超时
  • 了解不同隔离级别的区别
  • 了解DB2锁机制
  • 掌握IMPORT,CURSOR等基本操作

但只要多分析,多查资料,就可以将整个分析过程进行流程化,后期就能和自动化运维工作结合起来。