DB2环境变量设置引起的锁等待超时问题

zhouyi

1.  现象、问题描述

PISA B07系统测试时发现一个问题，CS在大批量进行业务定购流程时，会经常有数据库操作操作失败的日志出现。

<Error> [2006-06-27 23:12:49.647] [0:0] [cssercommon.cpp:4102] Error in FetchNext()! ErrNativeCode is [-911], ErrText is [[IBM][CLI Driver][DB2/LINUX] SQL0911N  The current transaction has been rolled back because of a deadlock or timeout.  Reason code "68".  SQLSTATE=40001].

日志的含义还是比较明确的，DB2的错误码SQL0911N的意思是因为死锁或超时导致当前的事务回滚，其中的Reason code "68"表明是锁定后锁应用锁等待超时而导致的事务回滚（Reason code "2"表示是死锁导致的事务回滚）。

2.  关键过程、根本原因分析
第一步肯定是怀疑代码中的业务逻辑不合理，出现了较大的事务而长时间不提交，那么其他连接上的事务就会根据数据库的参数LOCKTIMEOUT设置，等待相应的秒数后就会超时回滚，B07测试用的数据库锁超时参数是10秒。
Lock timeout (sec)                        (LOCKTIMEOUT) = 10

但经谢红波检视代码没有发现代码有什么不合理的地方，这时候想到使用事件观察器进行跟踪。

db2 "create event monitor 监视器名 for STATEMENTS write to file '目录名'"

db2evmon -path 目录名

时间观察器的结果如下（省略了大部分不相关的结果）

305) Statement Event ...

  Appl Handle: 255

  Appl Id: *LOCAL.db2inst1.060626032804

  Appl Seq number: 3294



  Record is the result of a flush: FALSE

  -------------------------------------------

  Type     : Dynamic

  Operation: Execute

  Section  : 4

  Creator  : NULLID 

  Package  : SYSSH200

  Consistency Token  : SYSLVL01

  Package Version ID  :

  Cursor   : SQL_CURSH200C4

  Cursor was blocking: FALSE

  Text     : insert into ServiceOrder (UserID,ServiceID,OrderTime,CancelOrderTime,OrderState, LastChargeTime,ChargeFlag) values('+86134061000000',1,'20060626165547','00000000000000',0,'00000000000000',0)
353) Statement Event ...

  Appl Handle: 255

  Appl Id: *LOCAL.db2inst1.060626032804

  Appl Seq number: 3305



  Record is the result of a flush: FALSE

  -------------------------------------------

  Type     : Dynamic

  Operation: Prepare

  Section  : 4

  Creator  : NULLID 

  Package  : SYSSH200

  Consistency Token  : SYSLVL01

  Package Version ID  :

  Cursor   : SQL_CURSH200C4

  Cursor was blocking: FALSE

  Text     : select UserID, ServiceID, OrderTime, CancelOrderTime, OrderState,  LastChargeTime, ChargeFlag from ServiceOrder where UserID = '+86134061000001'  and ServiceID = 10

  -------------------------------------------
从事件观察器可以发现发生锁等待超时的时候， ServiceOrder表上的只有两种SQL语句在操作，insert语句和select语句，分别是插入定购关系和查询已定购的业务。

insert into ServiceOrder(UserID, ServiceID, OrderTime, CancelOrderTime, OrderState, LastChargeTime, ChargeFlag) values('+86134061000000', 1, '20060626165547', '00000000000000', 0, '00000000000000', 0)

select UserID, ServiceID, OrderTime, CancelOrderTime, OrderState,  LastChargeTime, ChargeFlag from ServiceOrder where UserID = '+86134061000001'  and ServiceID = 10
而发生锁等待超时的正是select语句，根据之前开发的经验，似乎没有出现过这种情况，在insert的时候，应该是可以同步进行select操作的。同时申振国测试发现，虽然现在的库有问题，但另外一台246机器上有PISA B05的数据库，如果在其基础上升级到B07的库，并把B07数据库的数据完全导入，测试就没有问题。也就是说锁等待超时应该和代码以及数据库中的数据没有关系。

于是在246机器上建了一个SAMPLE数据库进行试验：
db2sampl

使用telnet方式建立两个连接，再使用db2 +c设置手动提交。在其中一个连接上执行相同的insert操作，在另一个连接上再执行select操作，此时却立刻能返回结果。

此时基本确定是数据库的参数问题，对比两个数据库的数据库参数和实例参数，发现几乎没有什么不同。经咨询IBM的支持工程师并做简单测试，发现是数据库的环境变量导致的问题。
下面介绍一下相关的几个DB2设置并行操作的参数。

Ø         关于DB2的几个并行参数设置

缺省的情况下，DB2在表扫描或索引扫描期间，扫描每一行时会首先执行行锁定，然后再判断该行是否符合查询的条件。这样当进行插入等操作时，其他的查询操作就会锁等待，因此业务量大的时候就可能出现锁等待超时。

为了提高数据库操作的并发性，DB2允许在某些情况下对游标稳定性（CS）或读稳定性（RS）进行隔离扫描延迟行锁定，直到知道一条记录满足查询的谓词为止。即为了提高扫描的并发性，可以延迟行锁定，直到确定某行符合查询要求为止。

这个功能需要打开如下几个注册表变量：

db2set DB2_EVALUNCOMMITTED=ON

db2set DB2_SKIPDELETED=ON

db2set DB2_SKIPINSERTED=ON
注：注册表变量是全局级的参数，使用db2set可查看已设置的注册表变量。

设置这些参数后，在表扫描期间会跳过未落实行。未落实的删除行则被视为已删除的行，而未落实的插入行则被视为尚未插入的行。启用这些参数会使得数据库得并发性更强，它是大部分应用程序的首选设置。

但这些参数也不是在所有情况下均适用的，需要区分场景，在某些应用场景下就不能使用这些参数。例如在如下情况下，DB2_SKIPINSERTED参数应该启用其默认值OFF。

Ÿ           假定两个应用程序使用一个表来在它们之间传递数据，第一个应用程序将数据插入到表中，第二个应用程序从表中读取数据。第二个应用程序必须按照数据在表中的显示顺序来处理数据，如果第一个应用程序正在插入要读取的下一行，则第二个应用程序必须等待直到插入被落实为止。