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）  启用死锁监控

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 | grep “DIA”来获取诊断目录位置）

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排他锁

Ø  等待表锁的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。