关于 DB2 数据库并发性的探讨

marb

浏览: 419867 次
性别:
来自: 北京

最近访客更多访客>>

hanzh33

nucleus

dwd_china

zhangxpower

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

DB(biz)

概述

OLTP 数据库通常是高并发的使用模式，具有高的并发性对 OLTP 系统来说至关重要，并发事务实际上取决于资源的使用状况，原则上应尽量减少对资源的锁定时间，减少对资源的锁定范围，从而能够尽量增加并发事务的数量，那么影响并发的因素有哪些呢？这篇系列文章之一将从 DB2 数据库本身所提供的机制对并发性的影响进行一些探讨，这些机制包括隔离级别、数据库锁参数、实例注册表变量、乐观锁定。

同时本文讨论及实验所基于的环境如下，见图 1：

图 1. DB2 版本

设置 DB2 注册表变量 DB2OPTIONS，将自动提交功能关闭，后面的实验默认都基于这个设置。

db2set DB2OPTIONS=+c 
 db2 terminate 
 db2stop 
 db2start

回页首

隔离级别与并发性

当多个用户访问同一数据库时会发生的现象介绍如下：

在单用户环境中，每个事务都是顺序执行的，而不会遇到与其他事务的冲突。但是，在多用户环境下，多个事务可以（而且常常）同时执行。因此每个事务都有可能与其他正在运行的事务发生冲突。有可能与其他事务发生冲突的事务称为交错的或并行的事务，而相互隔离的事务称为串行化事务，这意味着同时运行它们的结果与一个接一个连续地运行它们的结果没有区别。在多用户环境下，在使用并行事务时，会发生四种现象：

丢失更新：这种情况发生在两个事务读取并尝试更新同一数据时，其中一个更新会丢失。例如：事务 1 和事务 2 读取同一行数据，并都根据所读取的数据计算出该行的新值。如果事务 1 用它的新值更新该行以后，事务 2 又更新了同一行，则事务 1 所执行的更新操作就丢失了。由于设计 DB2 的方法，DB2 不允许发生此类现象。
脏读：当事务读取尚未提交的数据时，就会发生这种情况。例如：事务 1 更改了一行数据，而事务 2 在事务 1 提交更改之前读取了已更改的行。如果事务 1 回滚该更改，则事务 2 就会读取被认为是不曾存在的数据。
不可重复的读：当一个事务两次读取同一行数据，但每次获得不同的数据值时，就会发生这种情况。例如：事务 1 读取了一行数据，而事务 2 在更改或删除该行后提交了更改。当事务 1 尝试再次读取该行时，它会检索到不同的数据值（如果该行已经被更新的话），或发现该行不复存在了（如果该行被删除的话）。
幻像：当最初没有看到某个与搜索条件匹配的数据行，而在稍后的读操作中又看到该行时，就会发生这种情况。例如：事务 1 读取满足某个搜索条件的一组数据行，而事务 2 插入了与事务 1 的搜索条件匹配的新行。如果事务 1 再次执行产生原先行集的查询，就会检索到不同的行集。

维护数据库的一致性和数据完整性，同时又允许多个应用程序同时访问同一数据，这样的特性称为并发性。 DB2 数据库用来尝试强制实施并发性的方法之一是通过使用隔离级别——通过‘事务、隔离级别、锁’机制，它决定在第一个事务访问数据时，如何对其他事务锁定或隔离该事务所使用的数据。 DB2 使用下列隔离级别来强制实施并发性：

可重复的读（Repeatable Read）
读稳定性（Read Stability）
游标稳定性（Cursor Stability）
未提交的读（Uncommitted Read）

可重复的读隔离级别可以防止所有现象，但是会大大降低并发性的程度（可以同时访问同一资源的事务数量）。未提交的读隔离级别提供了最大的并发性，但是后三种现象都可能出现。

DB2 UDB 支持以下隔离级别：可重复读（Repeatable read，RR）、读稳定性（Read stability，RS）、游标稳定性（Cursor stability，CS）、未提交读（Uncommitted read，UR），下面分别讲述：

可重复读（Repeatable read，RR） 
确保 工作单元
（UOW）期间的任何表行
读操作直到 UOW 完成，
不会被其他应用程序进程更改。类似地，由另一个应用程序进程更改的任何行直到由该应用程序进程提交，不会被读取。
运行在 RR 级别的应用程序进程是完全与并发应用程序进程的效果相隔离的。

RR 隔离级别通常会直接对表加 S 锁，所以对并发的影响最大，但有一种情况例外：如果 WHERE 条件字段上建有主键或者 UNIQUE INDEX，并且通过主键或者 UNIQUE INDEX 进行查询，那么数据库将只对表加 IS 锁，结果行加 S 锁——在锁列表足够用，没有发生锁升级的情况下才成立，也就是说，这个时候 RR 级别 =RS 级别，这时不允许其他事务对这些行进行更新或者删除，因为对行的更新或者删除会对相应的行加 X 锁，这和行 S 锁相排斥；其他情况下，会直接对表加 S 锁，这时将不允许其他事务对任何行进行更新或者删除。

下面我们做个实验，验证使用RR 隔离级别的查询对表的加锁情况。

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

表名	表结构	主键/ UNIQUE INDEX	数据规模
TEST_HAVE_PRI		A 列上建有主键	10 行
TEST_NO_PRI		无主键和 UNIQUE INDEX	同样的 10 行数据

使用 RR 隔离级别对 TEST_HAVE_PRI 表进行查询，见图 2：

图 2. 执行查询的 session1 窗口

可以看到表 TEST_HAVE_PRI 上加了 IS 锁，行上加了 S 锁，这是通过主键查询时锁的使用情况，见图 3：

图 3. 快照监控窗口

通过主键字段查询后，被 RR 隔离级别锁定的行，不能够被其他事务更新（UPDATE/DELETE），session2 中的 UPDATE 事务将被锁住，见图 4：

图 4. 执行 UPDATE 事务的 session2 窗口

通过 DB2 控制中心工具可以查看锁定链条——即谁锁定了谁，打开‘应用程序’窗口，可以看到选中的应用程序状态为‘正在等待锁定’——就是 session2 中的事务的状态，见图 5：

图 5. 控制中心应用程序界面

点击‘显示锁定链’按钮，进入下一窗口，下图如何解释呢？

图 6. 应用程序锁定链界面

请点击‘图注’按钮，先了解一下各种图形元素所代表的意思，见图 7：

图 7. 图注界面

根据图 7 所示，图 6 所表示的意思为：下方框事务正在等待上方框事务释放锁定，也就是 session2 中的事务正在等待 session1 中的事务释放锁定。

更详细的信息，可以通过右键点击方框，选择‘显示锁定详细信息’菜单，见图 8：

图 8. 应用程序锁定链界面

下图为 session1 中的查询事务获得的锁的详细信息——获得一个表 IS 锁、一个行 S 锁，见图 9：

图 9. 锁定详细信息界面

图 10 为 session2 中的更新事务获得的锁的详细信息，这个事务处于正在等待锁定状态，获得了一个表 IX 锁，同时想要获得行 X 锁，但是不成功，于是发生了锁等待，与句柄为 901 的代理程序（即运行查询事务的 session1 应用）中的行 S 锁冲突（见图 9），所以只能等待，直到句柄为 901 的代理程序提交或者回滚工作单元，才能真正获得行 X 锁。

图 10. 锁定详细信息界面

表中其他的行可以被其他事务更新，见图 11：

图 11. 执行 UPDATE 操作的 session3 窗口

使用 RR 隔离级别对 TEST_NO_PRI 表进行查询，让我们看看不通过主键字段查询时锁的使用情况，见图 12：

图 12. 执行查询的 session1 窗口

可以看到表 TEST_NO_PRI 上直接加了 S 锁，其他任何事务不能对此表做任何更新操作 (DELETE/UPDATE/INSERT)，见图 13：

图 13. 快照监控窗口

读稳定性
（Read stability，RS）类似于 RR 。但是，运行在 RS 级别的应用程序进程不是完全与并发应用程序
进程的效果相隔离的。如果这样的应用程序进程不止一次发出同样的查询，它就会看到更改了的数据或者由其他
应用程序进程添加的新的“幻影（phantom）”行。

RS 隔离级别会对表加 IS 锁，结果行加 NS 锁，这时不允许其他事务对这些行进行更新（UPDATE/DELETE），但是允许插入任何行，因为对这些行的更新会对相应的行加 X 锁，这和行 NS 锁相排斥——上述说法基于锁列表足够用，没有发生锁升级的情况下才成立。

下面我们做个实验，验证使用RS 隔离级别的查询对表的加锁情况：

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

表名	表结构	索引
TEST		A 列上建有索引

使用 RS 隔离级别对 TEST 表查询，见图 14：

图 14. 执行查询的 session1 窗口

可以看到表 TEST 上加了 IS 锁，行上加了 NS 锁（存在 32768 个行 NS 锁，这里不一一列举），见图 15：

图 15. 快照监控窗口

预更新（UPDATE/DELETE）这些行的事务将被锁住，见图 16、图 17：

图 16. 执行 UPDATE 事务的 session2 窗口

图 17. 执行 DELETE 事务的 session3 窗口

但是可以插入任何行，见图 18：

图 18. 执行 INSERT 事务的 session4 窗口

游标稳定性
（Cursor stability，CS）也确保由另一个应用程序进程更改的任何行
直到被那个应用程序进程提交，
不会被读取。 CS 隔离级别只确保每个可更新游标
的当前行
不被其他应用程序进程更改；
在 UOW 期间读过的行可以被其他应用程序进程更改。针对可滚动更新游标，在提交之前，会对所有结果行一直加 U 锁，
无论游标滚动到什么地方； CS 隔离级别针对不可更新游标会对表加 IS 锁，如果未在 WHERE 条件字段上创建索引，
查询首先会查找锁列表，检查锁列表中是否存在与 IS 锁相排斥的锁，如果存在的话，
那么将等待所有持有排斥锁的事务提交，查询才能执行下去；如果 WHERE 条件字段创建了索引，并且使用了索引，
那么查询将通过索引得到结果行，然后检查锁列表中是否存在与结果行相排斥的锁，如果存在的话，
那么将等待所有持有排斥锁的事务提交，查询才能执行下去 .

下面我们做个实验，验证使用CS 隔离级别的查询对表的加锁情况：

使用 CS 隔离级别的可更新游标对 TEST_HAVE_PRI 表进行查询，并 FETCH 第 1 行（见图 19），使用的数据模型见表1：

图 19. 执行查询事务的 session1 窗口

可以看到表 TEST_HAVA_PRI 上加了 IX 锁，被取的当前行 (a= ’ 1 ’ ) 加了 U 锁，符合条件的其他行暂时不加锁，见图 20：

图 20. 快照监控窗口

这时其他事务不可以对这行进行更新操作 (UPDATE/DELETE) ——因为行上的 U 锁与 X 锁排斥，但是可以对其他行进行更新（比如对 a= ’ 3 ’这行），见图 21、图 22：

图 21. 执行 UPDATE 事务的 session2 窗口

图 22. 执行 UPDATE 事务的 session3 窗口

转到 session1 窗口，继续 FETCH 下一行，这时将释放前一行 (a= ’ 1 ’ ) 上的 U 锁，在当前行上 (a= ’ 2 ’ ) 加 U 锁，见图 23：

图 23. FETCH 下一行

转到 session2 窗口，可以看到之前被锁住的 UPDATE 语句已经执行成功，见图 24：

图 24. 执行 UPDATE 事务的 session2 窗口

在 session4 窗口对 a= ’ 2 ’这行进行 UPDATE 操作，可以预见将被锁住，见图 25：

图 25. 执行 UPDATE 事务的 session4 窗口

转到 session1 窗口，继续 FETCH 下一行，将释放 a= ’ 2 ’这行上的 U 锁（session4 中对 a= ’ 2 ’这行的 UPDATE 事务将成功执行，为什么呢？），将要在 FETCH 的下一行上加 U 锁，发现 session1 中的游标 FETCH 操作被锁住，这又为什么呢？

因为 session1 中的事务释放了 a= ’ 2 ’上的 U 锁，所以 session4 中被锁住的针对 a= ’ 2 ’这条记录进行的 UPDATE 事务能够执行成功，见图 26：

图 26. 执行 UPDATE 事务的 session4 窗口

为什么 session1 中的事务（FETCH 下一条记录）被锁住呢？是因为这次恰好要 FETCH a= ’ 3 ’这条记录，需要在行上加 U 锁，这与 session3 中的 update 事务对这行所加的 X 锁相排斥，所以 session1 窗口的 FETCH 下一行操作发生了锁等待，直到 session3 中的事务提交或者回滚为止，见图 27：

图 27. 执行 FETCH 操作的 session1 窗口

未提交读
（Uncommitted read，UR）对于某些操作，允许在 UOW 期间读过的任何行可以被其他应用程序进程
更改，并允许读任何被另一个应用程序进程更改过的行，即使该更改还没有提交。对于其他操作，UR 类似于 CS 。

下面我们做个实验，验证使用UR 隔离级别的查询是不会获得任何级别锁的：

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

表名	表结构	索引
TEST_NO_IND		没有索引

采用 UR 隔离级别查询 TEST_NO_IND 表，同时运行快照，可见，使用 UR 隔离级别查询时，对表 TEST_NO_IND 加了 IN 锁，IN 就是 intent none 意思，就是不加任何锁，见图 28、图 29：

图 28. 执行查询事务的 session1 窗口

图 29. 快照监控窗口

综上所述，离级别对并发性具有最显著的影响，不同隔离级别获得的资源的锁定范围也不同，如果所有事务都能做到不过分贪婪的占有锁资源——锁的范围大、占用时间长，那么事务之间发生锁冲突的可能性将大大降低，事务的并发性也将会很好。那么如何选择正确的隔离级别呢？

使用的隔离级别不仅影响数据库对并发性的支持如何，而且影响并发应用程序的性能。通常，使用的隔离级别越严格，并发性就越小，某些应用程序的性能可能会越低，因为它们要等待资源上的锁被释放。那么，如何决定要使用哪种隔离级别呢？最好的方法是确定哪些现象是不可接受的，然后选择能够防止这些现象发生的隔离级别：

如果正在执行大型查询，而且不希望并发事务所做的修改导致查询的多次运行返回不同的结果，则使用可重复的读隔离级别。
如果希望在应用程序之间获得一定的并发性，还希望限定的行在事务执行期间保持稳定，则使用读稳定性隔离级别。
如果希望获得最大的并发性，同时不希望查询看到未提交的数据，则使用游标稳定性隔离级别。
如果正在只读的表 / 视图 / 数据库上执行查询，或者并不介意查询是否返回未提交的数据，则使用未提交的读隔离级别。对于统计类报表，不需要得到十分精确的数据，那么最好使用 UR 隔离级别，既可以节省昂贵的锁列表资源，也不会因为锁冲突影响其他事务的执行，同时也不会受到其他事务的影响，顺利的得到统计结果。未提交的读隔离级别通常用于那些访问只读表和视图的事务，以及某些执行 SELECT 语句的事务（只要其他事务的未提交数据对这些语句没有负面效果）。顾名思义，其他事务对行所做的更改在已经提交之前对于使用未提交的读隔离级别的事务是可见的。但是，此类事务不能看见或访问其他事务所创建的表、视图或索引，直到那些事务被提交为止。类似地，如果其他事务删除了现有的表、视图或索引，那么仅当进行删除操作的事务终止时，使用未提交的读隔离级别的事务才能知道这些对象不再存在了。（一定要注意一点：当运行在未提交的读隔离级别下的事务使用可更新游标时，该事务的行为和在游标稳定性隔离级别下运行一样，并应用游标稳定性隔离级别的约束。）

回页首

数据库锁参数与并发性

当应用程序挂起的锁定总数达到可供应用程序使用的最大锁定列表空间量时，锁定将会升级，将影响到应用程序并发性。可用锁定列表空间量由 locklist 和 maxlocks 配置参数确定。

当应用程序达到允许的最大锁定数并且没有其他要升级的锁定时，它将使用锁定列表中为其他应用程序分配的空间。当整个锁定列表已满时，将发生错误。

以下几种原因可能会导致产生过量锁定升级：

锁定列表大小（locklist ）对于并行应用程序数目而言可能太小
可供每个应用程序使用的锁定列表百分比（maxlocks ）可能太小
一个或多个应用程序使用的锁定数可能过量。

要解决这些问题，可以：

增加 locklist 配置参数值。
增加 maxlocks 配置参数值。
标识具有大量锁定（请参阅locks_held_top 监视器元素）的应用程序，或借助以下公式并将该值与maxlocks 进行比较以标识在锁定列表中挂起过量锁定的应用程序：
(((locks held * 36) ⁄ (locklist * 4096)) * 100)

这些应用程序还可能因为在锁定列表中使用过量资源而导致其他应用程序中发生锁定升级。这些应用程序可能需要求助于使用表锁定（而不是行锁定），尽管表锁定可能导致lock_waits 和lock_wait_time 增加。

锁参数（LOCKLIST、MAXLOCKS 和 LOCKTIMEOUT）背景知识

这些与锁相关的控制都是数据库配置参数：

LOCKLIST 表明分配给锁列表的存储容量。每个数据库都有一个锁列表，锁列表包含了并发连接到该数据库的所有应用程序所持有的锁。锁定是数据库管理器用来控制多个应用程序并发访问数据库中数据的机制。行和表都可以被锁定。根据对象是否还持有其它锁，每把锁需要的锁列表字节数不一样：

在 32 位平台上，每个锁需要 48 或 96 字节的锁定列表，这取决于是否对该对象挂起了其他锁：

- 对于没有挂起其他锁的对象，挂起一个锁需要96 字节
- 对于已经挂起了锁的对象，记录一个锁需要48 字节

在64 位平台（HP-UX/PA-RISC 除外）上，每个锁需要64 或128 字节的锁定列表，这取决于在该对象上是否挂起了其他锁定：

- 对于没有挂起其他锁定的对象，挂起一个锁定需要128 字节
- 对于存在一个挂起的锁定的对象，记录一个锁定需要64 字节。
MAXLOCKS 定义了应用程序持有的锁列表的百分比，在数据库管理器执行锁升级之前必须填充该锁列表。当一个应用程序所使用的锁列表百分比达到MAXLOCKS 时，数据库管理器会升级这些锁，这意味着用表锁代替行锁，从而减少列表中锁的数量。当任何一个应用程序所持有的锁数量达到整个锁列表大小的这个百分比时，对该应用程序所持有的锁进行锁升级。如果锁列表用完了空间，那么也会发生锁升级。数据库管理器通过查看应用程序的锁列表并查找行锁最多的表，来决定对哪些锁进行升级。如果用一个表锁替换这些行锁，将不再会超出MAXLOCKS 值，那么锁升级就会停止。否则，锁升级就会一直进行，直到所持有的锁列表百分比低于MAXLOCKS 。MAXLOCKS 参数乘以MAXAPPLS 参数不能小于100 。

虽然升级过程本身并不用花很多时间，但是锁定整个表（相对于锁定个别行）降低了并发性，而且数据库的整体性能可能会由于对受锁升级影响的表的后续访问而降低。

下面是一些控制锁列表大小的建议：

- 经常进行提交以释放锁。
- 当执行大量更新时，更新之前，在整个事务期间锁定整个表（使用SQL LOCK TABLE 语句）。这只使用了一把锁从而防止其它事务妨碍这些更新，但是对于其他用户它的确减少了数据并发性。
- 使用alter TABLE 语句的LOCKSIZE 参数控制如何在持久基础上对某个特定表进行锁定。
- 查看应用程序使用的隔离级别。使用可重复读隔离级别在某些情况下可能会导致自动执行表锁定。当有可能减少所持有共享锁的数量时，可以使用游标稳定性（Cursor Stability ）隔离级别。如果没有损害应用程序完整性需求，那么可以使用未提交的读隔离级别而不是游标稳定性隔离级别，以进一步减少锁的数量。

使用下列步骤确定数据库锁列表所需的页数：

计算锁列表大小的下限：(512 * 32 * MAXAPPLS) / 4096 ，其中512 是每个应用程序平均所含锁数量的估计值，32 是对象（已有一把锁）上每把锁所需的字节数。
计算锁列表大小的上限：(512 * 64 * MAXAPPLS) / 4096 ，其中64 是某个对象上第一把锁所需的字节数。
对于您的数据，估计可能具有的并发数，并根据您的预计为锁列表选择一个初始值，该值位于您计算出的上限和下限之间。

使用数据库系统监视器调优MAXLOCKS 值。

设置MAXLOCKS 时，请考虑锁列表的大小（LOCKLIST ）：

MAXLOCKS = 100 * (512 锁／应用程序* 32 字节／锁* 2) / (LOCKLIST * 4096 字节)

该样本公式允许任何应用程序持有的锁是平均数的两倍。如果只有几个应用程序并发地运行，则可以增大MAXLOCKS ，因为在这些条件下锁列表空间中不会有太多争用。

LOCKTIMEOUT 指定了应用程序为获取锁所等待的秒数。这有助于应用程序避免全局死锁。
- 如果将该参数设置成0 ，那么应用程序将不等待获取锁。在这种情形中，如果请求时没有可用的锁，那么应用程序立刻会接收到-911 。
- 如果将该参数设置成-1 ，那么将关闭锁超时检测。在这种情形中，应用程序将等待获取锁（如果请求时没有可用的锁），一直到被授予了锁或出现死锁为止。

如何更改这些参数

要更改锁参数，请运行以下命令：

db2 -v update db cfg for DB_NAME using LOCKLIST a_number 
 db2 -v update db cfg for DB_NAME using MAXLOCKS b_number 
 db2 -v update db cfg for DB_NAME using LOCKTIMEOUT c_number 
 db2 -v terminate

监控步骤

一旦锁列表满了，由于锁升级生成更多的表锁和更少的行锁，因此减少了数据库中共享对象的并发性，从而降低了性能。另外，应用程序间可能会发生更多死锁（因为它们都等待数量有限的表锁），这会导致事务被回滚。当数据库的锁请求达到最大值时，应用程序将接收到值为-912 的SQLCODE 。如果锁升级造成并发性方面的问题，则可能需要增大LOCKLIST 参数或MAXLOCKS 参数的值。可以使用数据库系统监视器来确定是否发生锁升级，跟踪应用程序（连接）遭遇锁超时的次数，或者数据库检测到的所有已连接应用程序的超时情形。

首先，运行下面这个命令以打开针对锁的DB2 监视器：

db2 -v update monitor switches using lock on 
	 db2 -v terminate

然后收集数据库快照：

db2 -v get snapshot for database on DB_NAME

在快照输出中，检查下列各项：

Locks held currently = 0 
	 Lock waits = 0 
	 Time database waited on locks (ms) = 0 
	 Lock list memory in use (Bytes) = 504 
	 Deadlocks detected = 0 
	 Lock escalations = 0 
	 Exclusive lock escalations = 0 
	 Agents currently waiting on locks = 0 
	 Lock Timeouts = 0

如果“Lock list memory in use (Bytes) ” 超过定义的LOCKLIST 大小的50% ，那么就增加LOCKLIST 数据库配置参数中的4KB 页的数量。锁升级、锁超时和死锁将表明系统或应用程序中存在某些潜在问题。锁定问题通常表明应用程序中存在一些相当严重的并发性问题，在增大锁列表参数的值之前应当解决这些问题。

避免死锁

从事务双方的锁定关系上来讲有死锁和活锁 2 种，所谓活锁：举例来说可能会存在事务 A 在等待事务 B 释放所占用的锁资源，但是事务 B 并不等待事务 A 释放所占用的锁资源，所谓死锁：举例来说可能会存在事务 A 在等待事务 B 释放所占用的锁资源，同时事务 B 在等待事务 A 释放所占用的锁资源，也就是说，事务之间互相等待，如果没有合理设置检查死锁的时间间隔 (DLCHKTIME) 参数，可能会导致相关事务双方永远等待下去，DLCHKTIME 参数默认设置为 10000 毫秒——即 10 秒，也就是说每隔 10 秒钟，死锁检测进程会自动检查数据库范围内有无死锁存在，如果发现了死锁存在，那么将随机挑选一个事务并强制终止它，这个事务将被回滚，那么这时另外一个事务将可以顺利执行下去。

总的来说，死锁可能是由下列情况导致的：

数据库发生锁定升级
在系统生成的行锁定已足够的情况下应用程序显式锁定了表
绑定时应用程序使用了不适当的隔离级别
目录表已被锁定以供可重复读
应用程序正以不同的顺序获取相同的锁定，从而导致死锁。

回页首

实例注册表变量与并发性

从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

注解：

db2set DB2_EVALUNCOMMITTED=ON - 这个参数将在记录锁之前进行谓词检查，尽量减少锁的时间
db2set DB2_SKIPINSERTED=ON - 这个参数将新 insert 且没有提交的数据跳过；例如，SELECT/UPDATE 语句不会发现这条记录
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 个注册表变量会影响到并发性。通过合理设置这些变量可以改善并发性，但是也会影响到应用程序的行为，所以建议综合考虑业务的需求和结合自己的业务逻辑，来考量是否适合启用相应的注册表变量。

回页首

乐观锁定、增强的乐观锁定与并发性

乐观锁定是与悲观锁定相对应的，所以要谈乐观锁定，就不可避免的要谈谈什么是悲观锁定，什么是乐观锁定？

悲观锁定

所谓悲观锁定，就是查询表之后和尝试搜索的更新或删除记录操作之间的时间段挂起锁定的一种锁定策略。我们假设有这样一个场景，见图 56：

图 56. 应用程序场景

悲观锁定策略是：用户使用 RR/RS 隔离级别执行 SELECT 操作或以排他 exclusive 模式锁定表并执行查询操作（这里举例说明），通过游标得到一个结果集——对结果集记录加锁或者锁表，然后遍历这个游标，对这个结果集的每条记录做进一步的判断，符合条件的做更新操作，不符合条件的跳过，一直到游标遍历结束后，执行 COMMIT 操作，释放由 SELECT 操作获得的锁以及更新操作获得的锁，见图 57：

图 57. 悲观锁定策略

悲观锁定的优点就是能够保证实现一致且安全的更改。但是这种锁定策略的主要缺点就是在数据处理期间，一直占据着资源——被处理的资源的锁生命周期比较长，这样的话，可能会降低数据库的并发性，对于具有大并发用户的系统，需要等待资源释放的概率则会增加。

乐观锁定

乐观锁定是一项技术，它用于在选择 (SELECT) 行与更新或删除行之间未拥有行锁定的数据库应用程序。应用程序乐观地假定在更新或删除操作前未锁定的行不可能更改。如果行更改，那么更新或删除操作将失败，并且应用程序逻辑将通过重试查询操作（此处是举例说明）来处理这种故障。乐观锁定策略的主要优点是最小化给定资源对于其他事务的不可用时间，因此，它具有比悲观锁定更好的并行性，因为其他应用程序可以读写该行。它的一个缺点是应用程序中需要有更多的重试逻辑。

基于上述同样的场景，乐观锁定的策略是用户使用 CS/RS 隔离级别执行 SELECT 操作（此处是举例说明），得到一个结果集存放到客户端，然后执行 COMMIT 操作，释放对资源的锁定，然后在客户端遍历这个结果集，对这个结果集中的每条数据做进一步的判断，符合条件的做更新操作（如果确实这一行已经被修改了，那么更新操作将失败，应用程序逻辑将处理这些失败，例如，重新尝试查询），不符合条件的跳过，一直到遍历结束，执行 COMMIT 操作，释放相关资源上的锁，见图 58：

图 58. 乐观锁定策略

DB2 V9.5 之前版本的 DB2 应用程序只能通过构建搜索式 UPDATE 语句启用按值乐观锁定，该语句查找具有与所选值完全相同的值的行。如果行的列值已更改，那么搜索式 UPDATE 语句将失败。但是，按值乐观锁定具有一些缺点：

标识主动错误信息，这可能会更新错误的行
构建 UPDATE 搜索条件对应用程序来说很复杂
DB2 服务器根据值来搜索目标行的效率不高
某些客户机类型与数据库类型之间的数据类型不匹配，例如，时间戳记不允许在搜索式 UPDATE 中使用所有列。

增强的乐观锁定

在 DB2 V9.5 之前，乐观锁定是按值乐观锁定，完全由应用程序本身逻辑控制实现的，而从 DB2 V9.5 开始支持增强的乐观锁定，除了应用程序本身逻辑控制实现之外，DB2 本身还提供了一些增强的机制来提高执行的效率。

DB2 V9.5 增加了速度更快的乐观锁定的支持，这种乐观锁定不会产生主动错误信息（误判），

DB2 V9.5 的乐观锁定特性最小化了给定资源对于其他事务的不可用时间，进一步改善了并发性。这一支持通过如下所示的新 SQL 函数、表达式和特性实现的：

行标识符（RID_BIT 或RID ）内置函数：该内置函数可用于 SELECT 结果列表或谓词语句。例如，在谓词 WHERE RID_BIT(tab)=? 中，RID_BIT 等于谓词被实现为一种新的直接访问方法（避免了表扫描），从而可以更有效地定位行。在以前，被称为值乐观锁定的技术确定值的方式为：将所有选择（SELECT）的列值添加到谓词，然后应用某些惟一的列组合来筛选出单个行，这种通过表扫描方式访问方法效率较低。
ROW CHANGE TOKEN 表达式：这种新的表达式返回一个标记作为 BIGINT 。这个标记表示某一行的修改序列中的一个相对点。应用程序可以将某行的当前行更改标记值与上次取回行时保存的行更改标记值进行比较，以判断行是否发生修改。表需要定义一个行修改时间戳列来保存时间戳值，是否提供行修改时间戳列将影响乐观锁定的行为，因为该列有助于将行更改标记的粒度从页级别提高到行级别，这对乐观锁定应用程序非常有利。

使用这种编程模型的应用程序将从增强的乐观锁定特性中获益。

在应用程序中使用增强的乐观锁定

在 DB2 V9.5 中，对普通的表即可使用针对乐观锁定的新 SQL 表达式和属性，但是，如果对普通的表不进行 DDL 修改的情况下，乐观锁定应用程序可能会产生更多的漏判。因此，要避免发生漏判，执行乐观锁定的目标表应执行以下任意一种操作来增加 ROW CHANGE TIMESTAMP 列：

创建表时定义 ROW CHANGE TIMESTAMP 列
对表进行修改以包含 ROW CHANGE TIMESTAMP 列

要在应用程序中启用增强的乐观锁定支持，需要执行以下基本步骤：

在初始查询中，对要进行处理的所有行的行标识符和行更改标记执行 SELECT（使用 RID_BIT() 和 RID() 内置函数），使它们被包含在查询列表中。
释放行锁定，以便其他应用程序可以对表执行选择、插入、更新和删除操作。
通过在搜索条件中使用行标识符和行更改标记对目标行执行搜索式 UPDATE 或 DELETE，并乐观地假定在执行原始查询语句后未锁定的行尚未更改。
如果行已更改，那么更新操作将失败，并且应用程序逻辑必须处理该故障。例如，应用程序将重试查询和更新操作。

注：行标识符、行更改标记的用途如下

        —搜索行标识符以直接访问目标行，而不是通过全表扫描或者索引扫描

        —搜索行更改标记以确认行的状态，即如果行更改标记没变，表明相应记录未被任何事务更改，
        反之表明记录已经被更改。

针对乐观锁定设计并已启用乐观锁定的应用程序，将按照下列操作顺序向数据库发送请求：

清单 1. 乐观锁定方式执行查询

 

SELECT SALARY, row change token FOR STAFF, RID_BIT(STAFF) 
 INTO :h_SALARY, :h_rct, :h_rid 
 FROM STAFF WHERE ID = 240

在此方案中，应用程序首先读取所需的每行。我们准备在应用程序中使用乐观锁定策略，所以选择列表包括保存在 :h_rid 主变量中的行标识符值和保存在 :h_rct 主变量中的行更改标记值，见清单 1 。

在启用了乐观锁定的情况下，应用程序乐观地假定更新或删除操作的任何目标行都保持不变。为了提高数据库并行性，应用程序使用下列其中一种方法除去行锁定：

落实工作单元，在这种情况下行锁定将被除去
使用 WITH RELEASE 子句关闭游标，在这种情况下行锁定将被除去
使用较低的隔离级别：
- 游标稳定性（CS），在这种情况下，在游标访存到下一行或结果表末尾后行未锁定。
- 未落实的读（UR），在这种情况下，任何未落实的数据将具有新的（未落实）行更改标记值。如果回滚未落实的数据，那么已落实的旧行更改标记将是另一个值。

注：假定通常不回滚更新，使用 UR 将允许最大并行性。

断开与数据库的连接，因此释放应用程序的所有 DB2 服务器资源。

在释放查询操作所带来的行锁定之后，在应用程序逻辑中可能对上述查询获得的结果集进行相关判断处理，得到想要继续向下处理的行的行标识符和行更改标记，然后应用程序乐观地更新这些行：

清单 2. 乐观锁定方式执行更新

UPDATE STAFF SET SALARY = SALARY * 1.2 WHERE row change token 
FOR STAFF = :h_rct AND RID_BIT(STAFF) = :h_rid

通过行标识符、行更改标记对目标行进行 UPDATE 是最快的访问方案，因为避免了对表进行扫描，而通过直接访存的方式（通过 ROWID 直接定位到该行）：

				 RID_BIT(STAFF) = :h_rid

如果 RID_BIT() 谓词未找到行，那么表示行已删除并且更新操作由于未找到行而失败。

假定 RID_BIT() 谓词找到了行，那么在行更改标记未更改时，行更改标记谓词 FOR STAFF = :h_rct 将找到该行。如果在查询操作后行更改标记已更改，那么 UPDATE 语句将由于未找到行而失败。

下表总结了应用程序在启用了乐观锁定之后，可能出现的一些情况。

表 7. 列示了在启用乐观锁定后可能出现的情况

情况标识操作结果

情况 1	表中定义了行更改时间戳记列，并且其他应用程序未更改行。	由于行更改标记谓词成功找到 :h_rid 所标识的行，所以更新操作成功。
情况 2	表中定义了行更改时间戳记列。另一个应用程序在查询操作后在更新操作前更新了行，从而更新了行更改时间戳记列。	在查询操作时获得的行更改标记与行中当前行更改标记不匹配，因此 UPDATE 语句找不到行。
情况 3	表中定义了行更改时间戳记列。另一个应用程序更新了行，因此行具有新的行更改标记。此应用程序使用隔离级别 UR 查询行，并获取未落实的新的行更改标记。	此应用程序运行 UPDATE 语句，这将锁定等待，直到其他应用程序释放其行锁定为止。如果其他应用程序使用新标记落实更改，那么行更改标记谓词将成功，因此 UPDATE 语句成功。如果其他应用程序回滚到旧标记，那么行更改标记谓词将失败，因此 UPDATE 语句找不到行。
情况 4	表中未定义任何行更改时间戳记列。在查询操作后在更新操作前，在同一页面上更新、删除或插入了另一行。	该页面上的所有行共享一个行更改标记，该页面上任何行的更新，都会导致此页面上所有行的行更改标记更新。由于该页面上所有行的行更改标记值已更改，因此 UPDATE 语句匹配不到行。如果本场景中添加了行更改时间戳记列，那么 UPDATE 语句将成功更新目标行。
情况 5	改变 (ALTER) 了表以便包含行更改时间戳记列，并且在改变操作后查询返回的行还未修改。另一个应用程序更新该行，从而在此过程中将行更改时间戳记列添加至该行（具有当前时间戳记）。	行更改标记谓词将先前生成的标记值与根据行更改时间戳记列创建的标记值进行比较，因为找不到匹配行，因此 UPDATE 语句执行失败。
情况 6	在查询操作后在更新操作前重组了表。 :h_rid 所标识的行标识找不到行，或者它包含具有另一个标记的行（经过重组，已经不是原来的那行），因此更新操作失败。这是无法避免的被动错误信息情况。	记录本身未被重组操作更新，记录中所有的列值并未变化，只是记录的位置发生了变更，因此重组后谓词的 RID_BIT 部分无法标识原始行（实际上 UPDATE 语句根据行标识符、行更改标识无法匹配到行），因此 UPDATE 操作失败。

举例说明

假设有这样的一个场景：2 个数据库管理员同时接收到某个职员的请求，职员要求将自己的工作岗位变更一下。于是两名 DBA（DBA1/DBA2）通过人力资源系统更新 SAMPLE 数据库的 STAFF 表中的员工记录。此时存在一种可能，即两个 DBA 可能同时对同一名职员的记录进行更新。下面设计了几个场景，针对表 7 中所有的情况进行演示。

第 1 种情况

STAFF 表包含一个 ROW CHANGE TIMESTAMP 列 TS（后来添加）并且只有 DBA1 访问了该表。 DBA1 从 STAFF 表中查询数据并在稍后尝试将 ID 为 70 的员工的工作从 Sales 更新为 Mgr 。更新成功。

清单 3. 将时间戳记列添加到 STAFF 表

			
ALTER TABLE STAFF ADD COLUMN TS TIMESTAMP NOT NULL 
GENERATED ALWAYS FOR EACH ROW ON UPDATE AS 

ROW CHANGE TIMESTAMP

清单 4. DBA1 执行查询语句

 

通过查询得到结果集的行标识符、时间标识符 , 然后执行 COMMIT 操作。

SELECT RID_BIT(STAFF),ROW CHANGE TOKEN FOR STAFF,ID,NAME,DEPT,JOB,TS
 FROM STAFF WHERE ID=70 
 COMMIT

表 8. DBA1 执行查询的结果

乐观锁定表达式	STAFF 表
RID_BIT	ROW CHANGE TOKEN	ID	NAME	DEPT	JOB	ROW CHANGE TIMESTAMP
x'0A000006000000000000000000FCF84F'	74904229642240	70	Rothman	15	Sales	0001-01-01-00.00.00.000000

清单 5. DBA1 执行 UPDATE 语句

 

使用 DBA1 之前查询出来的行标识符、时间标识符来更新行。

UPDATE STAFF SET JOB = 'Mgr' 
 WHERE RID_BIT(STAFF)=x'0A000006000000000000000000FCF84F' 
 AND ROW CHANGE TOKEN FOR STAFF=74904229642240

表 9. 执行 UPDATE 语句后的结果

乐观锁定表达式	STAFF 表
RID_BIT	ROW CHANGE TOKEN	ID	NAME	DEPT	JOB	ROW CHANGE TIMESTAMP
x'0A000006000000000000000000FCF84F'	141401933370756520	70	Rothman	15	Mgr	2009-07-02-15.10.17.921000

第 2 种情况

STAFF 表包含一个 ROW CHANGE TIMESTAMP 列 TS，并且 DBA1 和 DBA2 同时访问该表。 DBA1 从 STAFF 表中查询数据并执行提交操作，稍后尝试更新这些数据。然而，在 DBA1 查询数据到执行更新操作期间，DBA2 对相同的记录进行了更新。 DBA2 执行的更新成功，而随后 DBA1 执行的更新失败。

清单 6. 为 STAFF 表添加时间戳记列

ALTER TABLE STAFF ADD COLUMN TS TIMESTAMP NOT NULL GENERATED
 ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

表 10. DBA1 执行查询的结果

乐观锁定表达式	STAFF 表
RID_BIT	ROW CHANGE TOKEN	ID	NAME	DEPT	JOB	ROW CHANGE TIMESTAMP
x'0A00400B00000000000000000465A5C1'	141401934713623382	70	Rothman	15	Mgr	2009-07-02-15.30.18.562006

清单 7. DBA2 执行 UPDATE 语句

 
UPDATE STAFF SET JOB = 'Engineer' WHERE ID=70 
 OR 
 UPDATE STAFF SET JOB = 'Engineer' 
 WHERE RID_BIT(STAFF)= x'0A00400B00000000000000000465A5C1' 
 AND ROW CHANGE TOKEN FOR STAFF=141401934713623382

表 11. DBA2 执行 UPDATE 语句后的结果

乐观锁定表达式	STAFF 表
RID_BIT	ROW CHANGE TOKEN	ID	NAME	DEPT	JOB	ROW CHANGE TIMESTAMP
x'0A00400B00000000000000000465A5C1'	141401934875088080	70	Rothman	15	Engineer	2009-07-02-15.32.44.546000

清单 8. DBA1 执行 UPDATE 语句

 

使用 DBA1 之前查询得到的行标识符、时间标识符来更新行。

UPDATE STAFF SET JOB = 'Engi' 
 WHERE RID_BIT(STAFF)= x'0A00400B00000000000000000465A5C1' 
 AND ROW CHANGE TOKEN FOR STAFF=141401934713623382

DBA1 更新失败。由于 DBA2 执行了 UPDATE，ROW CHANGE TOKEN 发生了改变，因此当 DBA1 把执行查询时取回的标记与 DBA2 执行更新后的标记当前值进行比较时，无法找到匹配行，因此 DBA1 更新失败。

第 3 种情况

STAFF 表包含一个 ROW CHANGE TIMESTAMP 列 TS，并且 DBA1 和 DBA2 同时访问该表，DBA1 对行进行了更新，但还未提交修改，DBA2 使用 UR 隔离级别从 STAFF 表中查询数据，接着 DBA1 提交它做出的修改，然后 DBA2 尝试对相同的数据进行更新，DBA2 执行更新能够成功，因为 DBA2 之前执行脏读查询读取的是 DBA1 未提交的更新，这时变化后的 ROW CHANGE TOKEN 被读取到（当 DBA1 后来执行提交操作时，提交前产生变化的 ROW CHANGE TOKEN 不会再发生改变），所以后来 DBA2 执行更新操作能够找到匹配行，执行更新成功。然而，如果 DBA1 回滚更新操作而不是提交更新操作，那么 DBA2 的更新操作将失败。

表 12. DBA1 执行查询的结果

乐观锁定表达式	STAFF 表
RID_BIT	ROW CHANGE TOKEN	ID	NAME	DEPT	JOB	ROW CHANGE TIMESTAMP
x'0A00000600000000000000000465ABC4'	141401934875088080	70	Rothman	15	Mgr	2009-07-02-15.32.44.546000

清单 9. DBA1 执行 UPDATE 语句，但是未提交

 
UPDATE STAFF SET JOB = 'Chief' 
 WHERE RID_BIT(STAFF)= x'0A00000600000000000000000465ABC4' 
 AND ROW CHANGE TOKEN FOR STAFF=141401934875088080

表 13. DBA2 使用隔离级别 UR 查询此行 (WHERE ID=70) 的结果

乐观锁定表达式	STAFF 表
RID_BIT	ROW CHANGE TOKEN	ID	NAME	DEPT	JOB	ROW CHANGE TIMESTAMP
x'0A00000600000000000000000465ABC4'	141401936241179608	70	Rothman	15	Chief	2009-07-02-15.53.03.343000

然后 DBA1 提交之前执行的 UPDATE 语句

清单 10. DBA1 执行提交操作

commit

表 14. DBA1 查询提交后的结果

可以发现时间标识符与 DBA2 查询到的一致，实际上反映的都是新的时间标识符（不管是否提交）。

乐观锁定表达式	STAFF 表
RID_BIT	ROW CHANGE TOKEN	ID	NAME	DEPT	JOB	ROW CHANGE TIMESTAMP
x'0A00000600000000000000000465ABC4'	141401936241179608	70	Rothman	15	Chief	2009-07-02-15.53.03.343000

清单 11. DBA2 执行 UPDATE 语句

 
UPDATE STAFF SET JOB = 'GM' WHERE ID=70 
 OR 
 UPDATE STAFF SET JOB = 'GM' 
 WHERE RID_BIT(STAFF)= x'0A00000600000000000000000465ABC4' 
 AND ROW CHANGE TOKEN FOR STAFF=141401936241179608

表 15. DBA2 执行 UPDATE 语句后的结果

乐观锁定表达式	STAFF 表
RID_BIT	ROW CHANGE TOKEN	ID	NAME	DEPT	JOB	ROW CHANGE TIMESTAMP
x'0A00000600000000000000000465ABC4'	141401937033810064	70	Rothman	15	GM	2009-07-02-16.00.55.250000

第 4 种情况

STAFF 表不包含 ROW CHANGE TIMESTAMP 列。 DBA1 查询一行 (ID=70)，获得 ROW CHANGE TOKEN/RID_BIT()，然后 DBA2 更新了同一数据页中的其他记录 (ID=110, 这时 ROW CHANGE TOKEN 将发生改变，这个页中所有记录的 ROW CHANGE TOKEN 都是一样的，这时 ROW CHANGE TOKEN 是基于页的 )，然后，DBA1 尝试更新之前查询到的记录（通过 ROW CHANGE TOKEN/RID_BIT() 来定位），更新将会失败，因为 ROW CHANGE TOKEN 已经发生了改变，找不到匹配的记录了。

清单 12. DBA1 执行查询语句

通过查询得到结果集的行标识符、时间标识符 , 然后执行 COMMIT 操作。

SELECT RID_BIT(STAFF),ROW CHANGE TOKEN FOR STAFF,ID,NAME,DEPT,JOB 
FROM STAFF WHERE ID = 70 
 COMMIT

表 16. DBA1 执行查询的结果

乐观锁定表达式	STAFF 表
RID_BIT	ROW CHANGE TOKEN	ID	NAME	DEPT	JOB
x'0A00400B00000000000000000465EFF9'	7632868011317133312	70	Rothman	15	Mgr

表 17. 同一数据页面上还存在的其他一些记录

下面几条记录都属于同一数据页面。

乐观锁定表达式	STAFF 表
RID_BIT	ROW CHANGE TOKEN	ID	NAME	DEPT	JOB
x'0B00400B00000000000000000465EFF9'	7632868011317133312	80	James	20	Clerk
x'0C00400B00000000000000000465EFF9'	7632868011317133312	90	Koonitz	42	Sales
x'0D00400B00000000000000000465EFF9'	7632868011317133312	100	Plotz	42	Mgr
x'0E00400B00000000000000000465EFF9'	7632868011317133312	110	Ngan	15	Clerk

清单 13. DBA2 执行 UPDATE 语句

			
更新同一数据页面上的另外一条记录 (ID=110)

UPDATE STAFF SET JOB = 'Mgr' WHERE ID=110

表 18. DBA2 执行 UPDATE 语句后的结果

下表为同一数据页面上的部分记录，可以看到虽然只更新了ID=110 这条记录，但是此页面上所有记录的 ROW CHANGE TOKEN 都共享 ID=110 这条记录的 ROW CHANGE TOKEN，也就是说表在没有时间戳记列存在的情况下，ROW CHANGE TOKEN 是基于页存在的。

乐观锁定表达式	STAFF 表
RID_BIT	ROW CHANGE TOKEN	ID	NAME	DEPT	JOB
x'0A00400B00000000000000000465EFF9'	-1300866274502377472	70	Rothman	15	Mgr
x'0B00400B00000000000000000465EFF9'	-1300866274502377472	80	James	20	Clerk
x'0C00400B00000000000000000465EFF9'	-1300866274502377472	90	Koonitz	42	Sales
x'0D00400B00000000000000000465EFF9'	-1300866274502377472	100	Plotz	42	Mgr
x'0E00400B00000000000000000465EFF9'	-1300866274502377472	110	Ngan	15	Clerk

清单 14. DBA1 执行 UPDATE 语句

			
通过之前查询得到的 ROW CHANGE TOKEN/RID_BIT() 定位。
 UPDATE STAFF SET JOB = 'Clerk' 
 WHERE RID_BIT(STAFF)= x'0A00400B00000000000000000465EFF9' 
 AND ROW CHANGE TOKEN FOR STAFF=7632868011317133312

更新将失败，已经找不到匹配的记录了，因为整个页面的时间标识符已经由于 DBA2 执行 UPDATE 语句而改变了。

第 5 种情况

STAFF 表包含 ROW CHANGE TIMESTAMP 列 TS，DBA1 和 DBA2 访问该表。 DBA1 从中查询一行，然后 DBA2 更新了同一数据页中的另外一行，接着 DBA1 更新之前查询到的那条记录，更新可以成功。

清单 15. DBA1 执行查询操作

通过查询得到结果集的行标识符、时间标识符 , 然后执行 COMMIT 操作。

SELECT RID_BIT(STAFF),ROW CHANGE TOKEN FOR STAFF,ID,NAME,DEPT,JOB,TS 
FROM STAFF WHERE ID=120 
 COMMIT

表 19. DBA1 执行查询的结果

乐观锁定表达式	STAFF 表
RID_BIT	ROW CHANGE TOKEN	ID	NAME	DEPT	JOB	ROW CHANGE TIMESTAMP
x'0F0000060000000000000000055F4927'	141402635178433027	120	Naughton	38	Clerk	2009-07-07-18.36.01.875011

表 20. 同一数据页面上还存在的其他一些记录

下面几条记录都属于同一数据页面。

乐观锁定表达式	STAFF 表
RID_BIT	ROW CHANGE TOKEN	ID	NAME	DEPT	JOB
x'0B0000060000000000000000055F4927'	141402635178433023	80	James	20	Clerk
x'0C0000060000000000000000055F4927'	141402635178433024	90	Koonitz	42	Sales
x'0D0000060000000000000000055F4927'	141402635178433025	100	Plotz	42	Mgr
x'0E0000060000000000000000055F4927'	141402635178433026	110	Ngan	15	Clerk

清单 16. DBA2 执行 UPDATE 操作

更新同一数据页面上的另外一条记录。
 UPDATE STAFF SET JOB = 'VP' 
 WHERE RID_BIT(STAFF)= x'0D0000060000000000000000055F4927' 
 AND ROW CHANGE TOKEN FOR STAFF=141402635178433025

表 21. DBA2 执行 UPDATE 语句后的结果

乐观锁定表达式	STAFF 表
RID_BIT	ROW CHANGE TOKEN	ID	NAME	DEPT	JOB	ROW CHANGE TIMESTAMP
x'0D0000060000000000000000055F4927'	141402635937539040	100	Plotz	42	VP	2009-07-07-18.47.21.812000

清单 17. DBA1 执行 UPDATE 语句

 

通过之前查询得到的 ROW CHANGE TOKEN/RID_BIT() 定位，更新成功。

UPDATE STAFF SET JOB = 'Mgr' 
 WHERE RID_BIT(STAFF)= x'0F0000060000000000000000055F4927' 
 AND ROW CHANGE TOKEN FOR STAFF=141402635178433027

表 22. DBA1 执行 UPDATE 语句后的结果

乐观锁定表达式	STAFF 表
RID_BIT	ROW CHANGE TOKEN	ID	NAME	DEPT	JOB	ROW CHANGE TIMESTAMP
x'0F0000060000000000000000055F4927'	141402643285428659	120	Naughton	38	Mgr	2009-07-07-20.28.53.281011

第 6 种情况

STAFF 表具有一个 ROW CHANGE TIMESTAMP 列 TS，并且只有 DBA1 访问该表。 DBA1 先从中查询一条记录用于以后更新它。然后表被离线重组。最后 DBA1 尝试更新数据，更新失败。更新失败是因为执行 REORG 后 RID_BIT 指向的已经不是原来的那一条记录，而且所有记录的 ROW CHANGE TIMESTAMP 列值也发生了变化，所以 DBA1 根据之前查询得到的 RID_BIT/ROW CHANGE TOKEN 已经找不到匹配的行，所以更新失败。

清单 18. DBA1 执行查询操作

通过查询得到结果集的行标识符、时间标识符 , 然后执行 COMMIT 操作。

SELECT RID_BIT(STAFF),ROW CHANGE TOKEN FOR STAFF,ID,NAME,DEPT,JOB,TS 
FROM STAFF WHERE ID=150 
 COMMIT

表 23. DBA1 执行查询的结果

乐观锁定表达式	STAFF 表
RID_BIT	ROW CHANGE TOKEN	ID	NAME	DEPT	JOB	ROW CHANGE TIMESTAMP
x'1200400B00000000000000000465EFF9'	74904229642240	150	Williams	51	Sales	0001-01-01-00.00.00.000000

清单 19. 重组 STAFF 表

 
REORG TABLE STAFF

表 24. 重组表 STAFF 后记录发生的变化（ID=150）

乐观锁定表达式	STAFF 表
RID_BIT	ROW CHANGE TOKEN	ID	NAME	DEPT	JOB	ROW CHANGE TIMESTAMP
x'12000006000000000000000004A3C230'	141401962414435156	150	Williams	51	Sales	2009-07-02-21.59.04.093012

可以发现ID=150 这条记录已经被移动到别的页面上去了，因为 RID_BIT() 已经发生了变化，而且 ROW CHANGE TOKEN 也发生了变化。

清单 20. DBA1 执行 UPDATE 语句

 

根据之前查询得到的行标识符、行更改标记执行更新操作，很明显，更新操作不能成功。
即使行标识符可能仍然指向有效的记录（已经不是原来的那条记录，因为这个位置可能已经被新的记录所占据），
但是行更改标记也已经发生了改变，所以使用之前查询得到的行标识符、行更改标记进行匹配，
匹配不到记录，所以更新操作不能成功。

UPDATE STAFF SET JOB = 'Mgr' 
 WHERE RID_BIT(STAFF)= x'1200400B00000000000000000465EFF9' 
 AND ROW CHANGE TOKEN FOR STAFF=74904229642240

结束语

为了避免在使用悲观锁定技术时可能引发的锁等待而导致的并发性问题，乐观锁定技术最小化了给定资源对于其他事务的不可用时间。通过使用乐观锁定，数据库管理器在完成读操作之后可以立即释放锁。

DB2 V9.5 支持更高效的乐观锁定，而且避免了误判的发生。这些支持通过行标识符（RID_BIT 或 RID）内置函数、行更改标识符 (ROW CHANGE TOKEN 表达式 ) 实现的。使用这种编程模型的应用程序可以从增强的乐观锁定特性受益，并且能够进一步增强并发性。

分享到：

关于 DB2 数据库并发性的探讨之二 | DB2对sql语句的性能检测

2011-12-02 10:24
浏览 1262
评论(0)
分类:数据库
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论