常见DB2锁等待解决流程

以下内容摘录自《DB2性能调整和优化》一书锁一章，如果转载务必指明出处！

大家可以到卓越网上购买，链接地址如下：

http://www.amazon.cn/search/search.asp?searchKind=author&searchWord=%E7%89%9B%E6%96%B0%E5%BA%84&searchType=1&sortType=market

 

锁等待是实际生产中最常碰到的性能问题，下面我们总结了锁等待问题的解决流程和步骤。现在的情况是一个应用系统中出现很多锁等待导致系统出现性能问题。我们可以执行如下步骤来解决锁等待问题：

1、首先把数据库监控开关打开：

db2 update dbm cfg using DFT_MON_LOCK on DFT_MON_STMT on db2  update monitor switches using lock ON sort ON bufferpool ON uow ON table ON statement ON

2、利用DB2表函数编写一个监控SQL脚本。

select AGENT_ID ,substr(STMT_TEXT,1,100) as statement,STMT_ELAPSED_TIME_MS  from table(SNAPSHOT_STATEMENT('SAMPLE',-1)) as B where AGENT_ID in (select AGENT_ID_HOLDING_LK from table(SNAPSHOT_LOCKWAIT('SAMPLE',-1)) as A  order by  LOCK_WAIT_START_TIME ASC FETCH FIRST 20 ROWS ONLY ) order by STMT_ELAPSED_TIME_MS DESC-----这个脚本非常实用，希望大家熟悉掌握

此语句，会按照执行时间的长短的先后顺序排列出所有造成lockwait的sql语句。

3、我们可以把这个脚本写到一个shell脚本中，每隔三秒钟（不一定是3秒，也可以1秒）运行一次，把监控结果管道累加输出到一个文件。

#!/usr/bin/ksh # dbname=$1 #create a log file filename=find.locksql.$(date+'%m%d%H%M%S') touch $filename #connect to database echo now,connecting to database: $dbname db2 "connect to $dbname" db2 "update dbm cfg using DFT_MON_LOCK on DFT_MON_STMT on" db2  "update monitor switches using lock ON sort ON bufferpool ON uow ON table ON statement ON" echo now,finding the SQLs which made lockwait 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" > $filename echo The SQLs have saved to the file $filename

4、 一旦定位引起锁等待的SQL语句后，如果该SQL语句写的效率很低下，可以考虑对该SQL语句作出调整；如果该SQL语句上没有创建最合理的索引，尝试使用db2advis工具为引起锁等待的SQL语句创建最合理的索引。尝试调优引起锁等待的SQL语句。

5、 如果创建索引和调优SQL语句仍然不能解决问题，考虑能否根据业务逻辑选择UR隔离级别。但是这种方式只能解决读的问题。

6、 在业务逻辑允许的情况下，考虑设置DB2_EVALUNCOMMITTED、DB2_SKIPDELETED和DB2_SKIPINSERTED来提高并发。

7、 最后考虑能否对引起锁等待的SQL语句关联的表做数据归档，业务分离等手段。

传统的锁定方法会导致应用程序互相阻塞。当一个应用程序必须等待另一个应用程序释放其锁定时，阻塞就会发生。用于处理这种阻塞的影响的策略通常会提供一种机制以指定最大可接受阻塞持续时间。这就是应用程序在不能获取锁定的情况下在返回之前等待的时间。  

  以前，只能在数据库级别通过更改 locktimeout 数据库配置参数的值来指定锁定等待超时时间。锁定等待策略通过新的 SET CURRENT LOCK TIMEOUT 可以在语句级别指定（DB2 V9.5以后），此语句更改 CURRENT LOCK TIMEOUT 专用寄存器的值。CURRENT LOCK TIMEOUT 专用寄存器指定在返回指示不能获取锁定的错误之前等待锁定的秒数。