DB2锁等待和死锁问题
数据库中之所以会存在死锁或者锁等待,是因为某一事务执行时间过长,导致锁没有及时释放,那么我们的解决办法就是,事务过程尽量要短,并且事务中的sql执行要快,这样才不会有过多的锁等待。还有一个原因,就是一些执行糟糕的sql,比如走了全表扫描,那么它会占据表中大量的锁,导致锁住了其他行,其他用户只能等待。
解决锁等待,要注意以下几点
优化查询 Sql,采用db2advis建立合适的索引,使得其能够走索引查询,由于索引的范围和排序,可以直接跳过许其他行,定位到符合我们需要的行。
采用合适的隔离级别。由于DB2 默认是CS的隔离级别,它的原理是,游标每到一行就会锁住改行,对于一般应用来说是足够了,但是如果遇到全表扫描,那么CS模式会锁住表中大量的行,直到查询完毕。所以可以根据业务需求,将其改为UR模式,它不会对表加任何行锁。或者在JDBC中设置隔离级别(Isolation Levels)
合理设置锁超时参数,它主要是用来避免事务长时间被占用,导致锁和连接无法释放,影响系统的并发。可以设置 DB参数
更新操作一定要走索引,否则很容易产生死锁。(针对边更新边查的操作)
避免出现锁升级现象,当锁等待达到一定程度时(行锁的个数超过loctList *percent of lock list),就会出现行锁升级为表锁,即锁升级。因为一旦出现锁升级,那么锁住的就不再是行,而是表,那么其他事务要想访问该表中的任意行,必须等待事务将锁释放。
修改Lock timeout (-1代表不检测锁超时),一般来说,该参数默认为10s足矣。
当系统存在严重的锁等待时,可以通过以下sql,定位到锁等待Sql
db2 "select AGENT_ID ,substr(STMT_TEXT,1,100) as statement,STMT_ELAPSED_TIME_MS from table(SNAPSHOT_STATEMENT('dbname',-1)) as B where AGENT_ID in (select AGENT_ID_HOLDING_LK from table(SNAPSHOT_LOCKWAIT(‘dbname’,-1)) as A order by LOCK_WAIT_START_TIME ASC FETCH FIRST 20 ROWS ONLY )
order by STMT_ELAPSED_TIME_MS DESC"
死锁比锁超时更加可怕,因为它将随机回滚一个事务,而这个不受应用程序控制,不可控的错误十分可怕,所以一旦出现死锁,必须解决掉。
如何观察DB2是否存在死锁呢,有以下两种方式:
1) 开启lock快照监控
db2 update monitor switches using LOCK on
执行如下命令:
db2 get snapshot for database on dbname | grep -i "LOCK"
可以看到其中有DeadLocks detected
2) 采用db2top 工具(db2 V9.1后才有)
Db2top –d dbname
然后键盘输入‘d’,如下:
可以看到其中有个 DeadLocks 计数。
如何定位死锁也有很多方式,如:
1) 创建死锁监控器(需要针对文件分析,复杂度较高)
2) 采用db2pd(最稳定,一般可以定位到)
3) 采用db2top 监控组件(最快,但不一定能抓到)
附件中,讲述方法2),该方法我在差旅测试过程中发现,并且总是可以精确定位到。
附上:如何精确定位死锁
——谁占有锁,谁等待锁,供开发参考
有很多死锁定位方式。但是能精确定位的还是比较少。发现通过dbpd来捕捉是最好的,也是最精确的。
方法总结如下:
1) 启用死锁监控
参考:
- The list of useful commands for db2pdcfg
- How to use db2pdcfg to catch lock timeout events and invoke the db2cos script for automatic data collection?
db2pdcfg -catch deadlock
当死锁触发时,会自动执行db2cos脚本(在%db2dump%/bin 目录下)。
这个脚本里调用了db2pd来将当前信息捕捉下来
其中主要捕捉的信息包含如下:
Db2pd共捕捉了locks(锁)、transaction(事务)、agents(代理进程)、application(应用)、dynamic(动态sql,这个最重要,是用于定位到sql)
2) 坐等死锁
当死锁触发时,将自动生成 db2cos.process_id_application_id.txt,该文件在db2的诊断目录下
(可以通过 db2 get dbm cfg 来获取诊断目录位置)
会话1
会话2
3) 分析db2cos文件
a) 首先看-locks showlocks 模块,截图如下:
找到其中包含【Sts】为W*,并且根据【ReleaseFlg】定位到相同slot的,类似,总共会有两处,一处状态为 W*(是死锁)、一处是 G(代表锁拥有)。Slot相同,就代表他们是在同一个数据对象上等待。寻找后的数据,如下截图:
从上图,可以看出如下信息:
- 在tabled=514和tabspacesId=4上等待(可以在syscat.tables 视图上根据信息定位到表名称
select TABNAME , TABSCHEMA from syscat.tables where TABLEID=514 and TBSPACEID=4) - 占据表锁的transactionHandle Id 为 28,是X排他锁llllll
- 等待表锁的transactionHandle Id为55,是NS(下一键共享锁)
有了这两个Id接着往下走。我们已经知道了谁在等待、谁在占有。
b) 查看-transaction,获取transaction id对应的agent id
可以看到Apphandle对应值应有了,下面就需要根据这个Apphandle去找sql执行的信息,已经离目标不远了哈。
c) 查看-dynamic 信息
观察如下列【C-AnchID】、【C-StmtUID】、【L-AnchID】、【L-StmtUID】
其中C-AnchID是代表当前正在执行的sql槽号,L-AnchID是代表上次执行的sql槽号。OK,我们就需要通过这个槽号来找到对应的Sql。定位到如下模块:
然后根据【C-AnchID】、【C-StmtUID】、【L-AnchID】、【L-StmtUID】列值找到对应sql(AnchID->C- AnchID,StmtUID-> C-StmtUID),如下:
自此,我们发现了导致死锁的Sql:
哪个sql 在等待锁?
状态为W*:
SELECTCOUNT ( * )
FROM BIZ.WF_TASK A
INNERJOIN
BIZ.REI_FORM B
ON A.RECEIPT_NO = B.REI_FORM_ID
WHERE A.TASK_STATUS = ?
AND A.HANDLE_ID = ?
AND A.RECEIPT_TYPE = ?
AND B.APPROVE_STATUS = ?
AND B.REI_FORM_NO = ?
哪个Sql在占有锁?
状态为G:
UPDATE BIZ.REI_FORM
SET APPROVE_STATUS = ?
WHERE REI_FORM_ID = ?
定位到Sql之后,我们就可以按照第三节中叙述的方法,该建索引就建立索引,sql写的不规范就调整sql。