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

侯君 2017-06-19 19:07:35
作者介绍

侯君证通股份有限公司DBA,主要负责DB2、MySQL、Couchbase运维,以及自动化运维平台开发,Python爱好者。

 

前言

 

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

 

我们对触发命令进行了改造,以便抓取更多信息:

 

 

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

 

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

 

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 报告的内容:

 


 

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

 

 

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隔离级别。

 

 

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

 

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

 

首先看Locks:段的信息:

 

 

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

 

 

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

 

 

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

 

同样可以在Application:段中查看更详细信息:

 


 

 

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

 

 

 

总结

 

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

 

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

 

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

 

2、在报告中看到了Lock RequestorLOCK 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等基本操作

 

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

 

参考资料

 

最新评论
访客 2024年04月08日

如果字段的最大可能长度超过255字节,那么长度值可能…

访客 2024年03月04日

只能说作者太用心了,优秀

访客 2024年02月23日

感谢详解

访客 2024年02月20日

一般干个7-8年(即30岁左右),能做到年入40w-50w;有…

访客 2023年08月20日

230721

活动预告