DB2数据库发生死锁了怎么办？（转）

相关图：http://tech.it168.com/db/2007-12-18/200712181037451.shtml

【IT168 技术文档】在新的数据库应用系统上线初期，由于测试不完善或不熟悉DB2的机制，常会出现锁等待死锁等现象存在于我们的应用系统中，如何捕获锁等待或死锁信息并解决锁问题，是保证平稳上线必须面对的问题。目前应用系统最常使用的DB2数据库版本有多个，有 8.1，8.2，9.1还有新推出的9.5，对于不同版本的DB2数据库提供的解决办法不尽相同，下面对于上述问题的解决作了一个简单说明，希望对大家有用。

首先在上线前有几件跟锁相关的事情需要开发设计人员一定要做好

1． 相关参数的更改

注册表变量参数：

    DB2_EVALUNCOMMITTED=on 当启用此变量时，在可能的情况下，它将进行表或索引访问扫描以延迟或避免行锁定，直到知道数据记录满足谓词求值为止。

    DB2_SKIPDELETED=on 当启用此变量时，在可能的情况下，它允许使用无条件地跳过已删除的键或跳过已删除的行。

    DB2_SKIPINSERTED=on 当启用此变量时，在可能的情况下，它允许跳过未落实的已插入行，就好像从未插入这些行一样。

数据库参数：

    LOCKLIST 锁定列表的内存量，当此参数设置为 AUTOMATIC 时，就启用了自调整功能。这允许内存调整器根据工作负载需求变化动态地调整此参数控制的内存区大小。如果不是自动，需要设置相对大一些；DB2默认是行锁，每个行锁大约占64或128个字节（64位数据库），计算锁定列表内存的大小公式是： ( 每个应用程序的平均锁定数目的估计值 * 每个锁定所需的字节数(128或64) * maxappls(或者max_coordagents) /4096 ) * 120%，这里只是建议公式，实际情况还要视操作系统实际的内存量来定。

    MAXLOCKS 升级前锁定列表的最大百分比，默认是22%(windows)或10%(unix)，可以根据要求自行改动，计算公式是 2 * 100 / maxappls

    DLCHKTIME 死锁检测时间间隔，默认是10000毫秒(10秒)，可以根据要求自行改动，也可不动。增大此参数以降低检查死锁的频率，因此增加应用程序必须等待消除死锁的时间。 减小此参数会增大检查死锁的频率，从而减少应用程序必须等待死锁解决的时间，但是会增加数据库管理器检查死锁所花的时间。

    LOCKTIMEOUT 锁等待时间，默认是 -1，也就是永远等待，请改成固定的值，在事务处理（OLTP）环境中，可以使用 30 秒的初始启动值。在一个只查询的环境中，您可以从一个较高的值开始。

    LOGFILSIZ 日志文件大小，此参数定义每个主日志文件和辅助日志文件的大小。如果数据库要运行大量更新、删除或插入事务，而这将导致日志文件很快变满，则应增大日志文件，了解您的并发应用程序的日志记录需求，来确定一个不会分配过量而浪费空间的日志文件大小。

    LOGPRIMARY 主日志文件数，了解您的并发应用程序的日志记录需求，适当增大日志文件数。

注意： 在更新参数时，需要注意有些参数在更改后需要重新启动数据库DB2实例才可以生效；有些参数需要断开当前所有的应用程序，重新链接才能生效；有些参数可以立即生效，使用者请参考相关文档，注意参数生效特性。

2． 应用程序设计

－程序尽量短小精悍

－程序尽量晚地访问关键资源

－程序尽可能快地提交工作

－程序尽可能快地关闭游标

－建立合适索引

3． 性能测试

要根据将来实际的并发用户数和数据量进行测试

上线之后维护时我们要做的几件事情

1. 做好定期维护

通过使用如下命令进行维护：
－reorg表和索引定期重组
－runstats表和索引的统计信息定期更新
－rebind 程序包定期重新编译

2. 日常观察db2diag.log文件

查看下面锁升级信息 escalation

2006-02-13-11.05.08.060000-480 E613164H452 LEVEL: Warning PID : 2112 TID : 3132 PROC : db2syscs.exe INSTANCE: DB2 NODE : 000 DB : SAMPLE APPHDL : 0-170 APPID: *LOCAL.DB2.060213185727 FUNCTION: DB2 UDB, data management, sqldEscalateLocks, probe:3 MESSAGE : ADM5502W The escalation of "35" locks on table "TEDWAS .

STAFF" to lock intent "X" was successful.


查看下面死锁或锁超时信息DeadLock or Lock timeout

2006-11-08-16.29.11.398155+480 E36235682A521 LEVEL: Error PID : 12979 TID : 1 PROC : db2agent (TESTDB) 0 INSTANCE: db2inst1 NODE : 000 DB : TESTDB APPHDL : 0-288 APPID: 198.132.3.100.57177.061108070923 AUTHID : TESTDB FUNCTION: DB2 UDB, data management, sqldEscalateLocks, probe:4 MESSAGE : ADM5503E The escalation of "2" locks on table“TESTDB.TEST12" to lock intent "X" has failed.

The SQLCODE is "-911". 2006-11-08-16.24.39.672914+480 E36100838A502 LEVEL: Error PID : 20866 TID : 1 PROC : db2agent (TESTDB) 0 INSTANCE: db2inst1 NODE : 000 DB : TESTDB APPHDL : 0-1394 APPID: 198.132.3.110.58426.061108075556 AUTHID : TESTDB FUNCTION: DB2 UDB, data management, sqldEscalateLocks, probe:4 MESSAGE : ADM5503E The escalation of "1" locks on table “TESTDB.

TEST11" to lock intent "X" has failed. The SQLCODE is "-952".


    我们可以看到红字标识出的锁升级(escalation)，锁等待锁超时（The SQLCODE is "-911"），程序由于锁的原因而终止（The SQLCODE is "-952".）

3. 观察命令list applications的输出

    查看应用程序的状态是否有锁定等待(Lock-wait)状态出现。
  
    执行命令 list applications for db sample show detail 得到如下结果

DB2ADMIN db2bp.exe 1129 *LOCAL.DB2.071128162517 00001 1 0 348

锁定等待 2006-11-29 00:25:52.417899 TEST SAMPLE C:\DB2\NODE0000\SQL00001\ DB2ADMIN db2taskd 1127 *LOCAL.DB2.071128162445 00001 1 0 628

连接已完成 2006-11-29 00:24:43.909356 TEST SAMPLE C:\DB2\NODE0000\SQL00001\ 。。。。。。。。 DB2ADMIN db2bp.exe 1126 *LOCAL.DB2.071128162443 00001 1 0 976 UOW

正在等待 2006-11-29 00:25:00.559420 TEST SAMPLE C:\DB2\NODE0000\ SQL00001\ 。。。。。。。。

    这里我们可以看到应用程序（1129）正在等待其他应用程序锁的释放，而应用程序（1126）正在执行程序，其中1129和1126分别是应用程序的ID。

4. 观察快照信息（snapshot）的输出

    在得到快照信息之前需要将锁定信息快照开关打开，命令如下

update dbm cfg using dft_mon_lock on(实例级别)
update monitor switches using lock on(会话级别，推荐使用)

    之后可以用如下命令取出快照信息

get snapshot for locks on sample

    我们可以得到类似信息：

数据库锁定快照
数据库名称           = SAMPLE
数据库路径            = C:\DB2\NODE0000\SQL00001\
输入数据库别名            = SAMPLE
挂起的锁定            = 8
当前已连接的应用程序            = 2
当前正等待锁定的代理程序数            = 1
快照时间戳记            = 2007-11-29 17:54:13.992157
应用程序句柄            = 54
应用程序标识            = *LOCAL.DB2.071129094306
序号            = 00001
应用程序名            = db2bp.exe CONNECT
授权标识            = DB2ADMIN
应用程序状态            = 锁定等待
状态更改时间            = 2007-11-29 17:50:16.124739
应用程序代码页            = 1386
挂起的锁定            = 4
总计等待时间（毫秒）            = 237867

锁定列表
锁定名称            = 0x030006000500C0020000000052
锁定属性            = 0x00000008
发行版标志            = 0x40000000
锁定计数            = 1
挂起计数            = 0
锁定对象名            = 46137349
对象类型            = 行
表空间名            = IBMDB2SAMPLEREL
表模式            = DB2ADMIN
表名            = TEST1
方式            = X

。。。。。。。。。。。。。。

锁定名称            = 0x03000600000000000000000054
锁定属性            = 0x00000000
发行版标志            = 0x40000000
锁定计数            = 1
挂起计数            = 0
锁定对象名            = 6
对象类型            = 表
表空间名            =  IBMDB2SAMPLEREL
表模式            = DB2ADMIN
表名            =  TEST1 方式 = IX 。。。。。。。。。。。。。

    从上面信息可以看到应用程序（54）正处于锁定等待状态，而这个程序所要求的锁，在快照信息里有详细描述（由红字标识出的），同时我们还可以看到整个数据库其他程序的锁定信息，要想得到某个应用程序的锁定信息，可用如下命令：

get snapshot for locks for application agentid 54

    其中54就是应用程序的句柄

5. 注意无效程序（Invalid pakage）的监控

    如果系统里有存储过程或用户自定义函数或嵌入C的程序，这些程序包含静态SQL，在编译时会生成执行代码片段，存储在数据库的系统表里，在程序执行时直接调用这些代码执行。

    在系统运行一段时间后，如果发生了表结构变了，索引删除了，统计信息发生变化了等这些程序所依赖的对象发生变化，那这些执行代码片段可能会失效或不可用。我们需要对这些程序进行监控，可以用下面命令查询无效程序（pakage）：
select pkgname,valid,last_bind_time from syscat.packages where pkgschema = 'name' and valid != 'Y'

    如果状态（valid）为N，说明需要重新绑定，如果状态为X，说明某些其依赖的对象被删掉了，需要重新创建那些被依赖的对象（如：表）

    要想重新绑定，可以执行下面命令：
rebind package pkgname resolve any

    同时DB2还提供一个db2rbind命令，这个命令可以一次绑定所有有效或无效的程序（pakage），命令如下：
db2rbind dbname -l logfile all

6. 监控运行时间长排序次数多读最多运行频率高的SQL

    要想查看这些SQL，可以通过表函数（DB2 V8）或系统管理视图（DB2 V9）来实现。

    在DB2 V9中增加了管理视图，可以如下使用：

查看执行时间最长的 5 个动态 SQL 语句：

select AVERAGE_EXECUTION_TIME_S , SUBSTR(STMT_TEXT,1,200) AS STMT_TEXT from SYSIBMADM.

TOP_DYNAMIC_SQL order by AVERAGE_EXECUTION_TIME_S desc fetch first 5 rows only;

查看执行频率最高的 5 个动态 SQL 语句：

select NUM_EXECUTIONS, AVERAGE_EXECUTION_TIME_S, STMT_SORTS, SORTS_PER_EXECUTION,

SUBSTR(STMT_TEXT,1,200) AS STMT_TEXT from SYSIBMADM.

TOP_DYNAMIC_SQL ORDER BY NUM_EXECUTIONS desc fetch first 5 rows only;

查看排序次数最多的 5 个动态 SQL 语句：

select STMT_SORTS, SORTS_PER_EXECUTION, substr(STMT_TEXT,1,200) as STMT_TEXT from SYSIBMADM.

TOP_DYNAMIC_SQL order by STMT_SORTS desc fetch first 5 rows only;

    在DB2 V8中增加了表函数，可以如下使用：

查看执行时间最长的 5 个动态 SQL 语句：

select TOTAL_EXEC_TIME/NUM_EXECUTIONS, SUBSTR(STMT_TEXT,1,200)

AS STMT_TEXT FROM TABLE( SNAPSHOT_DYN_SQL (CAST(NULL AS VARCHAR(1)), CAST (NULL AS INTEGER)))

as SNAPSHOT_DYN_SQL order by TOTAL_EXEC_TIME/NUM_EXECUTIONS desc fetch first 5 rows only;

查看执行频率最高的 5 个动态 SQL 语句：

select NUM_EXECUTIONS, TOTAL_EXEC_TIME/NUM_EXECUTIONS, STMT_SORTS,

STMT_SORTS/NUM_EXECUTIONS as SORTS_PER_EXECUTION,

SUBSTR(STMT_TEXT,1,200) AS STMT_TEXT from TABLE( SNAPSHOT_DYN_SQL (CAST(NULL AS VARCHAR(1)),

CAST (NULL AS INTEGER))) as SNAPSHOT_DYN_SQL ORDER BY NUM_EXECUTIONS desc fetch first 5 rows only;;

查看排序次数最多的 5 个动态 SQL 语句：

select STMT_SORTS, STMT_SORTS/NUM_EXECUTIONS as SORTS_PER_EXECUTION,

substr(STMT_TEXT,1,200) as STMT_TEXT from TABLE( SNAPSHOT_DYN_SQL (CAST(NULL AS VARCHAR(1)),

CAST (NULL AS INTEGER))) as SNAPSHOT_DYN_SQL order by STMT_SORTS desc fetch first 5 rows only;

    如果发现了运行成本比较高的SQL，就要来优化这些SQL的执行效率，来降低持有锁的锁产生的资源消耗，进一步降低死锁和锁等待的产生。


如果发生死锁或锁等待等现象我们该怎么办

    一旦我们在上述的2，3项发现了死锁或锁等待或锁升级，或者其他途径报告锁的问题，我们将如何解决呢？

首先我们要考虑应用系统的变化
    考察最近是否有新的程序加入或者是否对现系统做了改动，比如表结构变了，程序变了，是否有了大量数据的变动。
如果有，可以重新编译与变动相关的程序（比如存储过程等），查看与变动相关的SQL（比如运行效率低）。

其次我们可以使用DB2提供工具来解决问题
    如果上述方法还无法解决问题，就要采用DB2提供的工具来尝试解决问题了。
1. 查看db2diag.log文件，查找sqlcode是 -911或 -952

2006-11-08-16.29.11.398155+480 E36235682A521 LEVEL: Error PID : 12979 TID : 1 PROC : db2agent (TESTDB) 0 INSTANCE: db2inst1 NODE : 000 DB : TESTDB APPHDL : 0-288 APPID: 198.132.3.100.57177.061108070923 AUTHID : TESTDB FUNCTION: DB2 UDB, data management, sqldEscalateLocks, probe:4 MESSAGE : ADM5503E The escalation of "2" locks on table“TESTDB.TEST12" to lock intent "X" has failed.

The SQLCODE is "-911". 2006-11-08-16.24.39.672914+480 E36100838A502 LEVEL: Error PID : 20866 TID : 1 PROC : db2agent (TESTDB) 0 INSTANCE: db2inst1 NODE : 000 DB : TESTDB APPHDL : 0-1394 APPID: 198.132.3.110.58426.061108075556 AUTHID : TESTDB FUNCTION: DB2 UDB, data management, sqldEscalateLocks, probe:4 MESSAGE : ADM5503E The escalation of "1" locks on table “TESTDB.TEST11"to lock intent "X" has failed.

The SQLCODE is "-952".

    我们可以看到红字标识出的SQLCODE is  "-911" 或 SQLCODE is "-952"的信息里，也分别提供了的表名字（TESTDB.TEST12,TESTDB.TEST11）,从这里我们可以看出，锁的问题与这些表相关，有了这些表的描述，对我们定位问题就前进了一步，下面我们需要定位是哪个程序或哪个SQL影响的，


2. 锁的快照信息（snapshot）
    当使用list applications命令还能看到运行状态还是锁定等待的应用时，可以立即使用get snapshot for locks 命令来查找到底锁定是被哪个程序挂起的
    查看应用程序的状态是否有锁定等待(Lock-wait)状态出现
    执行命令  list applications for db sample show detail 得到如下结果

    。。。。。。。。
应用程序句柄                               = 54
应用程序标识                        = *LOCAL.DB2.071129094306
序号                                = 00001
应用程序名                          = db2bp.exe CONNECT
授权标识                    = DB2ADMIN
应用程序状态                        = 锁定等待
状态更改时间                        = 2007-11-29 17:50:16.124739
应用程序代码页                      = 1386
挂起的锁定                          = 4
总计等待时间（毫秒）                = 237867
  挂起锁定的代理程序标识                   = 45 
保留锁定的应用程序标识                   = *LOCAL.DB2.071129094146 
锁定名称                                = 0x030006000500C0020000000052 
锁定属性                                 = 0x00000000 
发行版标志                               = 0x00000001 
锁定对象类型                             = 行 
锁定方式                                 = 互斥锁定（X） 
请求的锁定方式                           = 下一个键共享（NS） 
挂起锁定的表空间名                       = IBMDB2SAMPLEREL 
挂起锁定的表模式                         = DB2ADMIN 
挂起锁定的表名                           = TEST1 
挂起锁定的表的数据分区标识               = 0 
锁定等待启动时间戳记                     = 2007-11-29 17:50:16.124744 。。。。。。。。

    这里我们可以看到应用程序（54）正在等待应用程序（45）的锁的释放，被锁的的表名是 DB2ADMIN.TEST1。

3. 应用程序和动态SQL的快照信息（snapshot）
    当使用list applications命令已经不能看到运行状态是锁定等待的应用时，就无法立即定位是哪个应用程序锁定引起的，这时我们使用get snapshot for applications和 get snapshot for dynamic sql 命令来查找锁定可能是被哪个SQL或哪个程序引起的，比如得到如下内容:

            应用程序快照

应用程序句柄                               = 45
。。。。。。。。。。。。。。

已用的 UOW 日志空间（以字节计）            = 174
先前的 UOW 完成时间戳记                    = 2007-11-29 17:41:46.288856
上次完成 uow 耗用时间（秒.毫秒）           = 0.000000
UOW 启动时间戳记                           = 2007-11-29 17:41:58.733755
UOW 停止时间戳记                           =
UOW 完成状态                               = 。。。。。。。。。。。。。

    这里我们可以看到红字标识部分的应用程序（45），已经执行很长时间但一直没有结束，说明有可能就是这个程序造成的。

      动态 SQL 快照结果
数据库名称                               = SAMPLE
数据库路径                      = C:\DB2\NODE0000\SQL00001\
执行数                          = 17
编译数                          = 1
最差预编译时间（毫秒）          = 9
最佳预编译时间（毫秒）          = 9
已删除的内部行                  = 0
已插入的内部行                  = 0
已读取的行                      = 0
已更新的内部行                  = 0
已写入的行                      = 0
语句排序                        = 17
语句排序溢出                    = 0
总计排序时间                    = 3
缓冲池数据逻辑读取              = 0
缓冲池数据物理读取              = 0
缓冲池临时数据逻辑读取          = 0
缓冲池临时数据物理读取          = 0
缓冲池索引逻辑读取              = 0
缓冲池索引物理读取              = 0
缓冲池临时索引逻辑读取          = 0
缓冲池临时索引物理读取          = 0
缓冲池 xda 逻辑读取             = 0
缓冲池 xda 物理读取             = 0
缓冲池临时 xda 逻辑读取         = 0
缓冲池临时 xda 物理读取         = 0
总计执行时间（秒.毫秒）         = 0.057497
总计用户 CPU 时间（秒.毫秒）    = 0.000000
总计系统 CPU 时间（秒.毫秒）    = 0.000000
语句文本                      = SELECT TABLE_CAT, TABLE_SCHEM, TABLE_NAME, CASE TABLE_TYPE WHEN 'NICKNAME' THEN 'SYNONYM' ELSE TABLE_TYPE END as TABLE_TYPE, REMARKS  FROM DB2ADMIN.TEST11 WHERE TABLE_SCHEM = 'DB2ADMIN' AND TABLE_TYPE IN ('TABLE') ORDER BY 4,1,2,3

    这里我们假设在db2diag.log里报出的是表DB2ADMIN.TEST11发生锁问题，那么就找根此表相关的SQL语句，比如红字标识出的SQL语句，通过这些SQL来定位最终是由于哪个应用程序或SQL造成的锁定。

4. DB2PD程序快速定位锁定SQL语句
    当使用list applications命令还能看到运行状态还是锁定等待的应用时，可以立即使用db2pd 命令来查找到底锁定是被哪个程序哪个SQL语句挂起的。从版本 8.2.2开始DB2也可以使用 db2pd 命令来获取死锁信息。
    我们用如下命令生成锁定信息，生成的信息存入locklog 文件内
db2pd -db sample -locks -transactions –applications -dynamic -file locklog

    我们会在文件中看到锁

Locks: Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg 0x7F890990 2 03000500040080020000000052 Row ..X G 2 1 0 0x08 0x40000000 0x7F890D80 7 03000500040080020000000052 Row .NS W 0 1 0 0x00 0x00000001

    从中会发现 TranHdl 7 正在等待 TranHdl 2 挂起的锁定，他们共同要求锁定名称相同（03000500040080020000000052）。

    下面我们找跟TranHdl 相关的AppHandl

Transactions: Address AppHandl [nod-index] TranHdl Locks State Tflag Tflag2 Firstlsn Lastlsn LogSpace SpaceReserved TID AxRegCnt GXID 0x7FC21A80 20 [000-00020] 2 4 WRITE 0x00000000 0x00000000 0x000003A9A957 0x000003ACD1FD 154 282 0x000000001345 1 0 0x7FC25B80 25 [000-00025] 7 4 READ 0x00000000 0x00000000 0x000000000000 0x000000000000 0 0 0x000000001672 1 0

    从中会发现TranHdl 2，7对应的AppHandl 是 20，25

    然后我们再查找AppHandl20,25对应的动态 SQL 语句的当前和最后一个锚点标识和语句唯一标识(C-AnchID C-StmtUID  L-AnchID L-StmtUID)。
    这允许直接从应用程序映射至动态 SQL 语句。

Applications: Address AppHandl [nod-index] NumAgents CoorEDUID Status C-AnchID C-StmtUID L-AnchID L-StmtUID Appid WorkloadID WorkloadOccID 0x7AEFBF30 25 [000-00025] 1 396 Lock-wait 116 1 116 1 *LOCAL.DB2.071127074823 1 2 0x7AED8080 20 [000-00020] 1 420 UOW-Waiting 0 0 13 1 *LOCAL.DB2.071127074818 1 1

    其中AppHandl 20对应的C-AnchID C-StmtUID  L-AnchID L-StmtUID 是 0， 0， 13 ， 1
    AppHandl 25对应的C-AnchID C-StmtUID  L-AnchID L-StmtUID 是 116， 1， 116 ， 1
    这里我只需要关注AppHandl 20对应的 ID

    最后我们再查找C-AnchID C-StmtUID  L-AnchID L-StmtUID 对应的sql语句

Dynamic SQL Statements: Address AnchID StmtUID NumEnv NumVar NumRef NumExe Text

0x7EA75AE0 13 1 1 1 1 1 insert into test1 values(2,'test1')

    完成上面过程我们发现了是insert into test1 values(1,'test1') sql导致了死锁或锁超时，因为test1表就是锁定超时报告文件中检测出来的锁定表。

    但需要注意的是，当前发现的sql语句  insert into test1 values(2,'test1') 是在其应用程序没有后续的其他sql语句发生时找到的结果，在实际的程序中在insert into test1 values(2,'test1') 语句后面很有可能会有其他sql语句执行，而在db2pd跟踪中Applications所能反应的只能是当前执行的sql语句和上一个执行的sql语句，这两个sql语句，不一定涉及到锁定超时报告文件当中的test1表，所以需要你在db2pd的检测文件仔细查找跟test1表相关的sql语句。