关于DB2 数据库并发性（2）

从DB2 V8以后陆续引入了三个注册表变量：DB2_EVALUNCOMMITTED、DB2_SKIPDELETED、DB2_SKIPINSERTED，为什么要引入这三个变量呢？在DB2没有这三个变量前，如果一个用户正在更改一行数据，那么DB2会在这一行加上排他锁，别的用户不能访问，除非使用UR隔离级别。

DB2为了改善应用程序并发性，从DB2 V8以后就陆续引入了这三个变量。这三个变量并不会改变锁的本质，只不过通过了解它们的工作方式和机制可以使我们根据我们的业务逻辑来合理的设置调整以提高应用程序并发性。这三个变量改变加锁的时机 , 减少锁冲突 ( 这样其行上的 insert/update/delete 操作不会锁住未命中的 select 操作 )

　　db2set DB2_EVALUNCOMMITTED=ON

　　 db2set DB2_SKIPDELETED=ON 　　 db2set DB2_SKIPINSERTED=ON

 

注解：

1. db2set DB2_EVALUNCOMMITTED=ON - 这个参数将在记录锁之前进行谓词检查，尽量减少锁的时间
2. db2set DB2_SKIPINSERTED=ON - 这个参数将新 insert 且没有提交的数据跳过；例如，SELECT/UPDATE 语句不会发现这条记录
3. db2set DB2_SKIPDELETED=ON - 这个参数将新 delete 且没有提交的数据跳过；例如，SELECT/UPDATE 语句不等待这条记录的提交，并且认为他已经被删除了

下面我们通过实验来演示这三个变量是如何影响SQL语句的行为的：

表 4. 实验使用的数据模型

表名	表结构	数据
TEST		包含如下 10 行数据： '1', '1'、'2', '2'、'3', '3'、'4', '4'、'5','5'、'6','6'、'7','7'、'8', '8'、'9','9'、'10','10'

 

在 session1 中插入一条记录 ('11','11') 到 TEST 表中，见图 30：

图 30. 执行 INSERT 事务的 session1 窗口

在 session2 中根据条件 a= ’ 1 ’ or a= ’ 11 ’查询 TEST 表，session2 被锁住，为什么呢？这是因为 session1 中 INSERT 事务还没有 COMMIT，所以这个时候 session 2 处于锁定等待 状态，见图 31、图 32：

图 31. 执行查询事务的 session2 窗口


图 32. 快照监控窗口

正常情况下上述事务的加锁机制就是这样的，但是有时候用户希望这个时候能够查询到数据，那么怎么解决这个矛盾呢？下面我们来仔细讲解这三个变量。

设置 DB2_EVALUNCOMMITTED

DB2 V8.1.4 版本中首次引入了 DB2_EVALUNCOMMITTED 这个 DB2 注册表变量。当它被启用（=TRUE | ON | YES | 1）时，它将修改 DB2 中只读查询的行为，使之允许在索引扫描（必须是 Type 2 索引）或表访问时推迟锁，直到限定语句的所有谓词都是已知的。引入这个新的注册表变量是为了可选地提高一些应用程序的并发性，其实质是允许读扫描推迟或避免行锁，直到适合特定查询的一个数据记录成为已知。

在 DB2 V8.1.4 之前（并且没有设置这个注册表变量），DB2 将执行保守式的锁：在验证行是否满足查询的排除谓词之前，它将锁定每个被访问的行，不管数据行是否被提交落实，以及根据语句的谓词它是否被排除，对于索引扫描和表访问都执行这样的锁定操作。

下面我们举一个简单的例子来演示：

表 5. 实验使用的数据模型

表名	表结构	数据	备注
TEST		包含如下 10 行数据： '1', '1'、'2', '2'、'3', '3'、'4', '4'、'5','5'、'6','6'、'7','7'、'8', '8'、'9','9'、'10','10'	暂时未建索引

 

现在有两个session发出了下面的SQL语句，见图33、图34：

图 33. 执行 INSERT 事务的 session1 窗口


图 34. 执行查询事务的 session2 窗口

我们查看session 2的状态，可以看见session2处于锁定等待 状态，见图 35：

图 35. 快照监控窗口

session1 执行 DB2 INSERT INTO TABLE TEST VALUES ('11', '11') 将阻塞所有其他的扫描器扫描它，因为它持有行上的 X 锁，所以 session2 执行 DB2 SELECT * FROM TEST 将被阻塞，直到 session1 提交或回滚。但是我们假设 session2 执行的语句是 DB2 SELECT * FROM TEST WHERE id='10' ，在此情况下，即使 session2 中事务 与 session1 中事务未提交的任何值没有关系，它也仍将被阻塞，处于锁定等待 状态。在 DB2 中，默认情况下将发生这一系列的事件，因为默认的隔离级别是 CS，这种隔离级别表明，一个查询访问的任何一行在游标定位到该行时都必须被锁定。在 session1 释放它用于更新（INSERT）表 TEST 的锁之前，session2 不能包含表 TEST 第一行上的锁。如果 DB2 事先能够知道值 A='11' 不是 session2 的数据请求的一部分（换句话说，它在锁行之前计算了谓词），就可以避免阻塞，这是合情合理的，现在 DB2 数据库已经支持这种行为，通过启用 DB2_EVALUNCOMMITTED 注册变量实现，该实例变量设置后需要重启实例，见图 36：

图 36. 设置实例注册表变量 DB2_EVALUNCOMMITTED

DB2_EVALUNCOMMITTED 变量影响 DB2 在游标稳定性（CS）和读稳定性（RS）隔离级别下的行锁机制。当你启用该功能时，DB2 可以对于未提交的更新数据（INSERT/UPDATE）事先进行谓词判断，如果未提交数据不符合该条语句的谓词判断条件，DB2 将不对未提交数据加锁，这样就避免了因为要对未提交数据加锁而引起的锁等待状态，提高了应用程序访问的并发性，同时 DB2 在进行表扫描时，会无条件地忽略被删除的行数据（不管是否满足谓词判断条件，不管是否提交）。这样一定程度上缓解了锁的问题，不会因为 INSERT/UPDATE/DELETE 一条记录而可能造成整个表被锁住。

下面我们通过一个实验来演示：

实验使用的数据模型见表 5：

现在有 6 个 session 窗口按照下面的命令序列做实验：

session1 窗口：db2 delete from test where a='1' session2 窗口：db2 select * from test session1 窗口：db2 rollback session2 窗口：db2 rollback session3 窗口：db2 insert into test values('11', '11') session4 窗口：db2 select * from test where a='10' session3 窗口：db2 rollback session4 窗口：db2 rollback session5 窗口：db2 update test set a='100' where a='1' session6 窗口：db2 select * from test where a='10' session5 窗口：db2 rollback session6 窗口：db2 rollback

 

在未设置DB2_EVALUNCOMMITTED=ON时，session2/session4/session6肯定都将处于锁等待 状态的，现在我们设置了DB2_EVALUNCOMMITTED=ON后，我们来看看session2/session4/session6能否检索到数据，通过这个实验我们发现当启用 DB2_EVALUNCOMMITTED=ON 时，对于delete/insert/update操作的处理，DB2 在进行表扫描时会无条件地忽略被删除的记录（不管是否满足谓词判断条件，不管是否提交），见图 37、图 38，而对于未提交的更新数据（INSERT/UPDATE）会事先进行谓词判断，如果未提交的记录不符合该条语句的谓词判断条件，DB2 将不对未提交记录加锁，这样就避免了因为要对未提交记录加锁而引起的查询事务锁等待状态，见图 39、图 40、图 41、图 42：

图 37. 执行 DELETE 事务的 session1 窗口


图 38. 执行查询事务的 session2 窗口

在 session1/session2 窗口使用 db2 rollback 命令将实验场景恢复原状。

图 39. 执行 INSERT 事务的 session3 窗口


图 40. 执行查询事务的 session4 窗口

在 session3/session4 窗口使用 db2 rollback 命令将实验场景恢复原状。

图 41. 执行 UPDATE 事务的 session5 窗口


图 42. 执行查询事务的 session6 窗口

在 session5/session6 窗口使用 db2 rollback 命令将实验场景恢复原状。

现在在TEST表上创建一个type-2的索引（在字段A上创建索引），然后再来做刚才的那个实验：

我们发现session2处于锁等待状态（见图 44），为什么呢？

当您的DB2环境中启用了evaluate uncommitted行为时，您应该清楚，谓词计算可能发生在未提交的数据上。我们知道在表扫描访问中，被删除行被无条件忽略，而对于使用type-2索引进行扫描，被删除的键不会被忽略（除非您还设置了DB2_SKIPDELETED注册表变量，DB2_SKIPDELETED变量我们稍后介绍），实验见图43、图44、图45；如果您要在环境中单独设置DB2_SKIPDELETED注册表变量，DB2将也允许在表扫描访问时无条件地忽略被删除行，并忽略通过type-2索引扫描访问的伪删除索引键。

图 43. 执行 DELETE 事务的 session1 窗口


图 44. 执行查询事务的 session2 窗口


图 45. 快照监控窗口

设置 DB2_SKIPDELETED

DB2_SKIPDELETED=ON 该变量被启用时，将允许使用 CS 或 RS 隔离级别的语句在索引扫描期间无条件地跳过被删除的键，而在表访问期间则无条件地跳过被删除的行。当 DB2_EVALUNCOMMITTED 被启用时，被删除的行会被自动跳过，但是除非同时启用了 DB2_SKIPDELETED，否则 type-2 索引中未提交的伪删除键不会被跳过。

在上面的实验中，我们发现当我们仅仅设置了 DB2_EVALUNCOMMITTED 变量时，如果表上有 type-2 索引，那么在我们通过索引读取数据时，被删除的索引键不会被忽略。这种情况下如果你希望跳过被删除的键，可以通过设置 DB2_SKIPDELETED=ON 来实现。

下面我们做个实验来演示一下：

首先打开实例注册表变量 DB2_SKIPDELETED，见图 46：

图 46. 设置实例注册表变量 DB2_SKIPDELETED=ON

重新做刚才的实验，我们可以看到在设置DB2_SKIPDELETED=ON后，即使test表上有type-2的索引，那么在扫描type-2索引的时候仍然忽略这个被删除的行。见图47、图48：

图 47. 执行 DELETE 事务的 session1 窗口


图 48. 执行查询事务的 session2 窗口

设置 DB2_SKIPINSERTED

虽然当 SELECT 语句由于一个未提交的 INSERT 操作而被锁住的这种行为是正确的 —— 但是有些特殊情况下希望 DB2 忽略正在等待提交的被插入的行，就好像它没有发生一样，这可以通过设置 DB2_SKIPINSERTED 注册表变量来达到这种目的。 DB2_SKIPINSERTED=OFF 是默认设置，这使得 DB2 的行为和预期的一样：SELECT 事务一直等到 INSERT 事务提交或回滚，然后返回数据；如果设置 DB2_SKIPINSERTED=ON，那么 SELECT 事务将忽略尚未提交的 INSERT 事务（只对于 CS 和 RS 隔离级别），不论表上是否存在索引。该特性增加了并发性，同时又不牺牲隔离语义。

下面我们来看设置DB2_SKIPINSERTED变量前后的例子：

表 6. 实验使用的数据模型

表名	表结构	数据	备注
TEST		包含如下 10 行数据： '1', '1'、'2', '2'、'3', '3'、'4', '4'、'5','5'、'6','6'、'7','7'、'8', '8'、'9','9'、'10','10'	未建索引

 

虽然之前已经打开了 DB2_EVALUNCOMMITTED 注册表变量，但是在打开 DB2_SKIPINSERTED 注册表变量前，下面的 session2 查询事务将处于 锁定等待 状态，为什么呢？因为 session1 中的 INSERT 事务插入了 1 条记录 '11', '11'，而 session2 查询事务事先根据谓词条件判断 session1 中插入的记录在条件范围之内，所以 session2 被锁住；然而 session3 查询事务却可以正常执行（也是通过全表扫描，因为未建索引），因为 session3 查询事务事先根据谓词条件判断 session1 中插入的记录不在条件范围之内（因为已经打开了DB2_EVALUNCOMMITTED变量，所以可以提前判断未提交的INSERT/UPDATE事务所涉及到的记录是否在谓词条件范围之内，如果不在条件范围之内的话，DB2将不对未提交的记录加锁，这样就避免了因为要对未提交记录加锁而引起的查询事务锁等待状态），所以 session3 查询事务可以正常执行，见图 49、图 50、图 51、图 52：

图 49. 执行 INSERT 事务的 session1 窗口


图 50. 执行查询事务的 session2 窗口


图 51. 快照监控窗口


图 52. 执行查询事务的 session3 窗口

如果这种情况下 session2 希望能够跳过未提交 的 insert 操作而得到数据，那么可以打开 DB2_SKIPINSERTED 注册表变量，见图 53：

图 53. 设置实例注册表变量 DB2_SKIPINSERTED=ON

然后再重复刚才的实验，我们发现这个时候，session2 已经可以查询到数据了，见图 54、图 55

图 54. 执行 INSERT 事务的 session1 窗口


图 55. 执行查询事务的 session2 窗口

可见session1中插入的记录被忽略掉了。

总结

总的来说这 3 个注册表变量会影响到并发性。通过合理设置这些变量可以改善并发性，但是也会影响到应用程序的行为，所以建议综合考虑业务的需求和结合自己的业务逻辑，来考量是否适合启用相应的注册表变量。