hackergodness
作者hackergodness2017-06-19 13:44
数据库开发工程师, 爱奇艺

记一次生产DB2数据库锁超时问题的分析与排查

字数 2959阅读 4780评论 3赞 6

前言

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中。

我们对触发命令进行了改造,以便抓取更多信息:
微信图片_20170619133147.jpg

微信图片_20170619133147.jpg

“#”号注释的命令为原始命令,此处替换成下一行的新内容。

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

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 报告的内容:
微信图片_20170619133253.png

微信图片_20170619133253.png

QQ截图20170619133527.png
QQ截图20170619133527.png

报告里描述了Lock Requestor和Lock Owner详细信息,包括锁超时发生时间,锁类型(Table lock)和加锁的资源ID(obj={4;136})。通过表和表空间ID,我们可以执行以下语句定位被锁的表名:
QQ截图20170619133616.png
QQ截图20170619133616.png

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隔离级别。
QQ截图20170619133647.png

QQ截图20170619133647.png

可以发现只有编号位11的查询table是T_BASE_QWDZLSB ,因此可以确定该语句导致了对表加锁,其隔离级别为CS,会产生一个IS锁。

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

首先看Locks:段的信息:
微信图片_20170619133710.jpg

微信图片_20170619133710.jpg

TranHdl 287的Sts为G,表明占有锁;TranHdl 272的Sts为W*,表明正在等待。接着根据Locks being waited on:段映射TranHdl和AppHandl,知道272的AppHandl为111774 。
微信图片_20170619133739.jpg

微信图片_20170619133739.jpg

而111774 当前执行语句信息为C-AnchID和C-StmtUID为781和1,根据这两个值定位到Dynamic SQL Statements:段
微信图片_20170619133805.png

微信图片_20170619133805.png

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

同样可以在Application:段中查看更详细信息:
微信图片_20170619133829.jpg

微信图片_20170619133829.jpg

微信图片_20170619133840.png
微信图片_20170619133840.png

至此,可以总结导致锁超时的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秒超时时间,从而发生锁超时。
QQ截图20170619133934.png

QQ截图20170619133934.png

QQ截图20170619133953.png
QQ截图20170619133953.png

总结

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语句:

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

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

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

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

如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!

6

添加新评论3 条评论

hackergodnesshackergodness数据库开发工程师, 爱奇艺
2017-06-20 15:47
然后db2cos据说可能在某版本下有bug,待求证,我也去gg下。

hackergodness@hackergodness 现有的bug基本都在V9.5,而且已经被fix,如果使用V9.7+应该可以放心~

2017-06-20 16:35
hackergodnesshackergodness数据库开发工程师, 爱奇艺
2017-06-20 15:46
感谢孙大师的提示,再补充下,db2pdcfg设置时不应将count值设置太大,否则容易引起性能问题。诚然,如果发生死锁、锁超时频率太高,必然有一定的资源开销。
jxnxsdengyujxnxsdengyu系统工程师, 江西农信
2017-06-19 14:48
很清晰很详细。
Ctrl+Enter 发表

作者其他文章

相关文章

相关问题

相关资料

X社区推广