db2高级锁

备注：

db2 +c "select * from test for update with rs" 会在行上加U锁，

其它普通 select * from test  可以查，

但 select * from test for update with rs 语句不能查(不能在U锁上试图再加U锁)。

================

我们在进行客户支持时遇到最多的话题之一就是锁。“为什么 DB2 锁住了这个表、行或者对象？”，“这个锁会阻塞多长时间及为什么？”；“为什么出现了死锁？”，“我的锁请求在等待什么？”，诸如此类问题等等。更仔细地分析一些常见的锁示例可以说明 DB2 锁定策略背后的原则。在国内很多 DB2 用户都会碰到有关锁等待、死锁和锁升级等锁相关的问题，本章将会对这些问题以及解决方法做详细的讲解。

本章主要讲解如下内容：

• 隔离级别和锁
• 加锁总结
• 乐观锁
• 内部锁
• 设置锁相关的注册变量

6.1  隔离级别和锁

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

• 可重复读 (Reapeatable Read，RR)
• 读稳定性 (Read Stability，RS)
• 游标稳定性 (Cursor Stability，CS)
• 未提交的读 (Uncommitted Read，UR)

隔离级别是根据称为现象 (Phenomena) 的三个禁止操作序列来声明的：

• 脏读 (Dirty Read)：在事务 A 提交修改结果之前，其他事务即可看到事务Ａ的修改结果。
• 不可重复读 (Non-Repeatable Read)：在事务Ａ提交之前，允许其他事务修改和删除事务Ａ涉及的数据，导致事务Ａ中执行同样操作的结果集变小。
• 幻像读 (Phantom Read)：事务Ａ在提交查询结果之前，其他事务可以插入或者更改事务 A 涉及的数据，导致事务 A 中执行同样操作的结果集增大。

数据库并发性 ( 可以同时访问同一资源的事务数量 ) 因隔离级别不同而有所差异，可重复读隔离级别可以防止所有现象，但是会大大降低并发性。未提交读隔离级别提供了最大的并发性，但可能会造成“脏读”、“幻像读”或“不可重复读”现象。 DB2 默认的隔离级别是 CS 。

6.1.1  可重复读

可重复读隔离级别是最严格的隔离级别。在使用它时，一个事务的操作结果完全与其他并发事务隔离，脏读、不可重复读、幻像读都不会发生。当使用可重复读隔离级别时，在事务执行期间会共享 (S) 锁定该事务以任何方式引用的所有行，在该事务中多次执行同一条 SELECT 语句，得到的结果数据集总是相同的。因此，使用可重复读隔离级别的事务可以多次检索同一行集，并可以对它们执行任意操作，直到提交或回滚操作终止事务。但是，在事务提交前，不允许其他事务执行会影响该事务正在访问的任何行的插入、更新或删除操作。为了确保这种行为，需要锁定该事务所引用的每一行—— 而不是仅锁定被实际检索或修改的那些行。因此，如果一个表中有 1000 行，但只检索两行，则整个表 (1000 行，而不仅是被检索的两行 ) 都会被锁定。输出结果如下：

C:\>db2 +c select empno,firstnme,salary from employee where empno 
between '000010' and '000020' withrrEMPNO FIRSTNME  SALARY 
 ------ ------------ ----------- 
 000010 CHRISTINE  152750.00 
 000020 MICHAEL  94250.00 
  2 条记录已选择。

 

我们通过“ get snapshot for locks on sample ”命令来监控表加锁情况，输出结果如下：

C:\>db2 update monitor switches using lock on 
 DB20000I  UPDATE MONITOR SWITCHES 命令成功完成。
 C:\>db2 get snapshot for locks on sample | more 
 -------------- 略 ------------------ 
锁定列表
锁定名称  = 0x020006000E0040010000000052 
锁定属性  = 0x00000010 
发行版标志 = 0x00000004 
锁定计数  = 1 
挂起计数  = 0 
锁定对象名  = 20971534 
对象类型=表
表空间名= USERSPACE1表模式= DB2ADMIN表名= EMPLOYEE方式= S  --注：虽然读取了两行，但是整个表加S锁

 

如果使用这种隔离级别，不管你从表中读多少数据，整个表上都加 S 锁，直到该事务被提交或回滚，表上的锁才会被释放。这样可以保证在一个事务中即使多次读取同一行，都会得到相同结果集。另外，在同一事务中如果以同样的搜索标准重新打开已被处理过的游标，那么得到的结果集不会改变。可重复读相对于读稳定性而言，加锁的范围更大：对于读稳定性，应用程序只对符合要求的所有行加锁；而对于重复读，应用程序将对整个表都加 S 锁。

可重复读会锁定应用程序在工作单元中引用的整个表。利用可重复读，一个应用程序在打开游标的相同工作单元内发出一个 SELECT 语句两次，每次都返回相同的结果。利用可重复读隔离级别，不可能出现丢失更新、脏读和幻像读的情况。

在该工作单元完成之前，“可重复读”应用程序可以多次检索和操作这些行。但是，在该工作单元完成之前其他应用程序均不能更新、删除或插入可能会影响结果表的行。“可重复读”应用程序不能查看其他应用程序未提交的更改。

6.1.2  读稳定性

读稳定性隔离级别没有可重复读隔离级别那么严格；因此，它没有将事务与其他并发事务的效果完全隔离。读稳定性隔离级别可以防止脏读和不可重复读，但是可能出现幻像读。在使用这个隔离级别时，只锁定事务实际检索和修改的行。因此，如果一个表中有 1000 行，但只检索两行 ( 通过索引扫描 )，则只锁定被检索的两行 ( 而不是所扫描的 1000 行 ) 。因此，如果在同一个事务中发出同一个 SELECT 语句两次或更多次，那么每次产生的结果数据集可能不同。

与可重复读隔离级别一样，在读稳定性隔离级别下运行的事务可以检索一个行集 (ROWS SET)，并可以对它们执行任意操作，直到事务终止。在这个事务存在期间，其他事务不能执行那些会影响这个事务检索到的行集的更新或删除操作，但是可以执行插入操作。如果插入的行与第一个事务的查询的选择条件匹配，那么这些行可能作为幻像出现在后续产生的结果数据集中。其他事务对其他行所作的更改，在提交之前是不可见的。下面我们还用上面的那个例子锁定读稳定性，输出结果如下：

C:\>db2 +c select empno,firstnme,salary from employee where empno 
between '000010' and '000020' withrsEMPNO FIRSTNME  SALARY 
 ------ ------------ ----------- 
 000010 CHRISTINE  152750.00 
 000020 MICHAEL  94250.00 
  2 条记录已选择。

 

我们通过“ get snapshot for locks on sample ”命令来监控表加锁情况，输出结果如下：

C:\>db2 update monitor switches using lock on 
 DB20000I  UPDATE MONITOR SWITCHES 命令成功完成。
 C:\>db2 get snapshot for locks on sample | more 
 -------------- 略 ------------------ 
锁定列表
锁定名称    = 0x02000600050040010000000052 
锁定属性    = 0x00000010 
发行版标志    = 0x00000001 
锁定计数    = 1 
挂起计数    = 0 
锁定对象名    = 20971525 
对象类型  = 行
表名  　　　　　 =EMPLOYEE方式    =S --注：只在读取的行上加S锁
锁定名称    = 0x02000600040040010000000052 
锁定属性    = 0x00000010 
发行版标志    = 0x00000001 
锁定计数    = 1 
挂起计数    = 0 
锁定对象名    = 20971524 
对象类型  = 行
表名 　　　　   =EMPLOYEE方式= S --注：只在读取的行上加S锁
锁定名称  = 0x02000600000000000000000053 
锁定属性  = 0x00000010 
发行版标志  = 0x00000001 
锁定计数  = 1 
挂起计数             = 0 
锁定对象名  = 6 
对象类型=表
表名  　　　　　= EMPLOYEE方式= IS --注：表上加IS锁

 

如果使用这种隔离级，那么在一个事务中将有 N+1 个锁，其中 N 是所有被读取 ( 通过索引扫描 ) 过的行的数目，这些行上都会被加上 NS 锁，在表上加上 1 个 IS 锁。这些锁直到该事务被提交或回滚才会被释放。这样可以保证在一个事务中即使多次读取同一行，得到的值也不会改变。但是使用这种隔离级别，在一个事务中，如果使用同样的搜索标准重新打开已被处理过的游标，则结果集可能改变 ( 可能会增加某些行，这些行被称为幻影行 (Phantom)) 。这是因为 RS 隔离级别不能阻止通过插入或更新操作在结果集中加入新行。

注意：

NS 是下一键共享锁，此时锁拥有者和所有并发的事务都可以读 ( 但不能更改 ) 被锁定行中的数据。这种锁用来在使用读稳定性或游标稳定性事务隔离级别读取的数据上代替共享锁。

读稳定性 (RS) 只锁定应用程序在工作单元中检索的那些行。它确保在某个工作单元完成之前，在该工作单元运行期间的任何限定行读取不被其他应用程序进程更改，且确保不会读取由另一个应用程序进程所更改的任何行，直至该进程提交了这些更改。也就是说，不可能出现“不可重复读”情形。

“读稳定性”隔离级别的其中一个目标是提供较高并行性以及数据的稳定视图，为了有助于达到此目标，优化器确保在发生锁定升级前不获取表级锁定。

“读稳定性”隔离级别最适用于包括下列所有特征的应用程序：

• 在并发环境下运行。
• 需要限定某些行在工作单元运行期间保持稳定。
• 在工作单元中不会多次发出相同的查询，或者在同一工作单元中发出多次查询时并不要求该查询获得相同的回答。

6.1.3  游标稳定性

游标稳定性隔离级别在隔离事务效果方面非常宽松。它可以防止脏读；但有可能出现不可重复读和幻像读。这是因为在大多数情况下，游标稳定性隔离级别只锁定事务声明并打开的游标当前引用的行。

当使用游标稳定性隔离级别的事务通过游标从表中检索行时，其他事务不能更新或删除游标所引用的行。但是，如果被锁定的行本身不是用索引访问的，那么其他事务可以将新的行添加到表中，以及对被游标锁定行前后的行进行更新或删除操作。所获取的锁一直有效，直到游标重定位或事务终止为止 ( 如果游标重定位，原来行上的锁就被释放，并获得游标现在引用的行上的锁 ) 。此外，如果事务修改了它检索到的任何行，那么在事务终止之前，其他事务不能更新或删除该行，即使在游标不再位于被修改的行时。与可重复读和读稳定性隔离级别一样，其他事务在其他行上进行的更改，在这些更改提交之前对于使用游标稳定性隔离级别的事务 ( 这是默认的隔离级别 ) 是不可见的。我们还用上面那个例子，一个表中有 1000 行数据，我们只检索其中两行数据。那么对于可重复读隔离级别会锁住整个表，对于读稳定性隔离级别会对读到的数据 ( 两行 ) 加锁，而对于游标稳定性隔离级别只对游标当前所在那一行加锁，游标所在行的前一行和下一行都不加锁。下面我们举一个游标稳定性的例子，输出结果如下：

C:\>db2 +c declare c1 cursor for  select empno,firstnme,salary from employee 
where empno between '000010' and '000020' with cs 
 C:\>db2 +c open c1 
 C:\>db2 +c fetch c1 
 EMPNO FIRSTNME   SALARY 
 ------ ------------ ----------- 
 000010 CHRISTINE 152750.00--注：游标当前所在行，DB2只对这一行加锁。游标的
				上一行和下一行都不加锁。当游标移动到下一行时，锁自动释放。
  1 条记录已选择。

 

我们通过“ get snapshot for locks on sample ”命令来监控表加锁情况，输出结果如下：

C:\>db2 update monitor switches using lock on 
 DB20000I  UPDATE MONITOR SWITCHES 命令成功完成。
 C:\>db2 get snapshot for locks on sample | more 
 -------------- 略 ------------------ 
锁定名称   = 0x02000600040040010000000052 
锁定属性   = 0x00000010 
发行版标志   = 0x00000001 
锁定计数   = 1 
挂起计数   = 0 
锁定对象名   = 20971524 
对象类型=行
表名= EMPLOYEE方式= S --注：只在游标所在行上加S锁
锁定名称   = 0x02000600000000000000000053 
锁定属性   = 0x00000010 
发行版标志   = 0x00000001 
锁定计数   = 1 
挂起计数   = 0 
锁定对象名   = 6 
对象类型=表
表名= EMPLOYEE方式= IS --注：表上加IS锁

 

如果使用这种隔离级，那么在一个事务中只有两个锁：结果集中只有正在被读取的那一行 ( 游标指向的行 ) 被加上 NS 锁，在表上加 IS 锁。其他未被处理的行上不加锁。这种隔离级别只能保证正在处理的行的值不会被其他并发的程序所改变。该隔离级别是 DB2 默认的隔离级别。

游标稳定性 (CS) 当在行上定位游标时会锁定任何由应用程序的事务所访问的行。此锁定在读取下一行或终止事务之前有效。但是，如果更改了某一行上的任何数据，那么在对数据库提交更改之前必须挂起该锁定。

对于具有“游标稳定性”的应用程序已检索的行，当该行上有任何可更新的游标时，任何其他应用程序都不能更新或删除该行。“游标稳定性”应用程序不能查看其他应用程序的未提交操作。

使用“游标稳定性”，可能会出现不可重复读和幻像读现象。“游标稳定性”是默认隔离级别，应在需要最大并行性，但只看到其他应用程序中的已提交行的情况下才使用。

6.1.4  未提交读

未提交读隔离级别是最不严格的隔离级别。实际上，在使用这个隔离级别时，仅当另一个事务试图删除或更改被检索的行所在的表时，才会锁定一个事务检索的行。因为在使用这种隔离级别时，行通常保持未锁定状态，所以脏读、不可重复读和幻像读都可能会发生。因此，未提交读隔离级别通常用于那些访问只读表和视图的事务，以及某些执行 SELECT 语句的事务 ( 只要其他事务的未提交数据对这些语句没有负面效果 ) 。

顾名思义，其他事务对行所做的更改在提交之前对于使用未提交读隔离级别的事务是可见的。但是，此类事务不能看见或访问其他事务 DDL(CREATE、ALTER 和 DROP) 语句所创建的表、视图或索引，直到那些事务被提交为止。类似地，如果其他事务删除了现有的表、视图或索引，那么仅当进行删除操作的事务终止时，使用未提交读隔离级别的事务才能知道这些对象不再存在了。

一定要注意一点：当运行在未提交读隔离级别下的事务使用可更新游标时，该事务的行为和在游标稳定性隔离级别下运行一样，并应用游标稳定性隔离级别的约束。下面我们举一个例子。

我们编写一个 SQL 存储过程，在存储过程中我们显式地在 SELECT 语句中使用 UR 隔离级别。

创建一个存储过程，保存为 LOCKS.SQL，输出结果如下：

CREATE PROCEDURE locks() 
 LANGUAGE SQL 
 BEGIN 
 declare c1 cursor for select * from staff with UR; 
 open c1; 
 while 1=1 do 　——注：死循环
 end while; 
 END @

 

为了方便抓住锁信息，我们在这个存储过程的结尾处使用了一个死循环。利用一个命令窗口运行存储过程，输出结果如下：

C:\ >db2 – td@ -vf locks.sql 
 C:\ >db2 "call locks()"

 

再打开一个新的窗口，得到在 STAFF 表上的当前锁信息，输出结果如下：

C:\>db2pd -db sample -locks show detail 
 Locks: 
 Address TranHdl Lockname Type Mode Sts Owner Dur HldCnt Att ReleaseFlg 
 0x408E0290 2 00020003000000000000000054 Table .ISG 2 1 0 0x0000 0x00000001 
 TbspaceID 2 TableID 3

 

但是会发现此时在 STAFF 表上出现的是 IS 锁，而不是 IN 锁。是什么原因呢？这是因为 UR 隔离级别允许应用程序存取其他事务的未落实的更改，但是对于只读和可更新这两种不同的游标类型，UR 的工作方式有所不同。对于可更新的游标，当它使用隔离级别 UR 运行程序时，应用程序会自动使用隔离级别 CS 。

在上面的例子当中，虽然显式地指定了 SQL 语句的隔离级别是 UR，但是，由于在存储过程中使用的游标是模糊游标 ( 也就是没有显式地声明游标是只读的还是可更新的 )，因而系统会默认地将这个模糊游标当成可更新游标处理，存储过程的隔离级别自动从 UR 升级为 CS 。要防止此升级，可以采用以下办法：

• 修改应用程序中的游标，以使这些游标是非模糊游标。将 SELECT 语句更改为包括 FOR READ ONLY 子句。
• 将模糊游标保留在应用程序中，但是预编译程序或使用 BLOCKING ALL 和 STATIC READONLY YES 选项绑定它以允许在运行该程序时将任何模糊游标视为只读游标。

我们还是使用上面的例子，显式地将该游标声明成只读游标，输出结果如下：

declare c1 cursor for select * from stafffor read onlywith UR;

 

此时我们再运行这个存储过程，并利用 DB2PD 获取锁的情况，输出结果如下：

c:\> db2pd -db sample -locks show locks 
 Locks: 
 Address TranHdl Lockname Type Mode Sts Owner Dur HldCnt Att ReleaseFlg 
 0x408E07E0 2 00020003000000000000000054 Table.ING 2 1 0 0x0000 0x00000001 
 TbspaceID 2 TableID 3

 

-注：可以看到STAFF表上出现的锁是IN锁。

从上面的例子中我们可以看到：“未提交读 (UR) ”隔离级别允许应用程序访问其他事务的未提交的更改。除非其他应用程序尝试删除或改变该表，否则该应用程序也不会锁定正读取的行而使其他应用程序不能访问该行。对于只读和可更新的游标，“未提交的读”的工作方式有所不同。

如果使用这种隔离级别，那么对于只读操作不加行锁。典型的只读操作包括： SELECT 语句的结果集只读 ( 比如语句中包括 ORDER BY 子句 ) ；定义游标时指明起为 FOR FETCH ONLY 或 FOR READ ONLY 。

该隔离级别可以改善应用程序的性能，同时可以达到最大程度的并发性。但是，应用程序的数据完整性将受到威胁。如果需要读取未提交的数据，该隔离级是唯一选择。

使用“未提交的读”，可能出现不可重复读行为和幻像读现象。“未提交读”隔离级别最常用于只读表上的查询，或者在仅执行选择语句且不关心是否可从其他应用程序中看到未提交的数据时也最常用。

以上我们所讲的隔离级别的加锁范围和持续时间都是针对读操作而言的。对于更改操作，被修改的行上会被加上 X 锁，无论使用何种隔离级别，X 锁都直到提交或回滚之后才会被释放。

6.1.5  隔离级别加锁示例讲解

假设有一张表 EMP1，表中有 42 条记录，我们使用 FOR READ ONLY 分别在 UR、CS、RS 和 RR 隔离级别下加锁。

EMP1 表在本章后续的内容中也会使用到，其创建过程如下：

C:\> db2 "create table emp1 like employee" 
 C:\> db2 "insert into emp1 select * from employee"

 

我们使用 EMP1 表中 JOB 字段内容为 'CLERK' 的数据，输出结果如下：

C:\>db2 +c select empno,job,salary from emp1 where job='CLERK'  
for read only  EMPNO JOB  SALARY 
 ------ -------- ----------- 
 000120 CLERK  49250.00 
 000230 CLERK  42180.00 
 000240 CLERK  48760.00 
 000250 CLERK  49180.00 
 000260 CLERK  47250.00 
 000270 CLERK    37380.00 
 200120 CLERK  39250.00 
 200240 CLERK  37760.00 
  8 条记录已选择。

 

在上面的 SQL 语句中，我们从表的 42 条记录中返回 8 条记录。下面我们分别看看这条语句在不同的隔离级别下加锁的情况：

UR 隔离级别，输出结果如下：

C:\>db2 +c select empno,job,salary from emp1 where job='CLERK'  
for read onlywith urEMPNO JOB    SALARY 
 ------ -------- ----------- 
 000120 CLERK  49250.00 
 000230 CLERK  42180.00 
 000240 CLERK  48760.00 
 000250 CLERK  49180.00 
 000260 CLERK  47250.00 
 000270 CLERK  37380.00 
 200120 CLERK  39250.00 
 200240 CLERK  37760.00 
  8 条记录已选择。

 

在另外一个窗口中使用“ db2 get snapshot for locks on sample ”命令监控，发现在 UR 隔离级别下，在表上有一个 IN 锁，没有加任何行锁。

CS 隔离级别，输出结果如下：

C:\>db2 +c declare c1 cursor for select empno,job,salary from emp1  
where job='CLERK'  for read onlywith CS
C:\>db2 +c open c1C:\>db2 +c fetch c1 
 EMPNO JOB     SALARY 
 ------ -------- ----------- 
 000120 CLERK  49250.00 
  1 条记录已选择。

 

在另外一个窗口中使用“ db2 get snapshot for locks on sample ”命令监控，发现在 CS 隔离级别下，共有两个锁：在表上有一个 IS 锁，在行上有一个 NS 锁。

RS 隔离级别，输出结果如下：

C:\>db2 +c select empno,job,salary from emp1 where job='CLERK'  
for read onlywith RSEMPNO JOB  SALARY 
 ------ -------- ----------- 
 000120 CLERK  49250.00 
 000230 CLERK  42180.00 
 000240 CLERK  48760.00 
 000250 CLERK  49180.00 
 000260 CLERK  47250.00 
 000270 CLERK  37380.00 
 200120 CLERK  39250.00 
 200240 CLERK  37760.00 
  8 条记录已选择。

 

在另外一个窗口中使用“ db2 get snapshot for locks on sample ”命令监控，发现在 RS 隔离级别下，共有 9 个锁：在表上有一个 IS 锁，在读取的 8 行上分别有 1 个 NS 锁。

RR 隔离级别，输出结果如下：

C:\>db2 +c select empno,job,salary from emp1 where job='CLERK'  
for read onlywith RREMPNO JOB   SALARY 
 ------ -------- ----------- 
 000120 CLERK  49250.00 
 000230 CLERK  42180.00 
 000240 CLERK  48760.00 
 000250 CLERK  49180.00 
 000260 CLERK  47250.00 
 000270 CLERK  37380.00 
 200120 CLERK  39250.00 
 200240 CLERK  37760.00 
  8 条记录已选择。

 

在另外一个窗口中使用“ db2 get snapshot for locks on sample ”命令监控，发现在 RR 隔离级别下，分为两种情况：

如果该 SQL 语句使用全表扫描，那么即使只读取了 8 行，也会在整个表上加一个 S 锁，输出结果如下：

C:\>dynexpln -d sample -q  "select empno,job,salary from emp1 where job='CLERK' 
 for read only with rr" – t 
 Access Table Name = DB2ADMIN.EMP1  ID = 3,12 
 |  #Columns = 2 
 |  Relation Scan 　 -- 注：全表扫描
 |  |  Prefetch: Eligible 
 |  Isolation Level: Repeatable Read 　 -- 注：RR隔离级别
 |  Lock Intents|  |  Table: Share --注：整个表上加S锁
 |  |  Row  : None 
 |  Sargable Predicate(s) 
 |  |  #Predicates = 1 
 |  |  Return Data to Application 
 |  |  |  #Columns = 3 
 Return Data Completion 
 End of section

 

如果创建索引，并进行索引扫描，那么表上加 IS 锁，读取的每行上加 S 锁。所以对于 RR 隔离级别来说，为了保证并发，尽可能创建合理的索引以减少加锁的范围，输出结果如下：

C:\>db2 create index job on DB2ADMIN.emp1(job) 
 DB20000I  SQL 命令成功完成。
 C:\>db2 runstats on table DB2ADMIN.emp1 and indexes all 
 DB20000I  RUNSTATS 命令成功完成。
 C:\>dynexpln -d sample -q  "select empno,job,salary from emp1 where job='CLERK' 
 for read only with rr" -t 
 Access Table Name = DB2ADMIN.EMP1  ID = 3,12 
 |  Index Scan:  Name = DB2ADMIN.JOB  ID = 1 　--注：索引扫描
 |  |  Regular Index (Not Clustered) 
 |  |  Index Columns: 
 |  |  |  1: JOB (Ascending) 
 |  #Columns = 2 
 |  #Key Columns = 1 
 |  |  Start Key: Inclusive Value 
 | | | | 1: 'CLERK  ' 
 |  |  Stop Key: Inclusive Value 
 | | | | 1: 'CLERK  ' 
 |  Data Prefetch: Eligible 0 
 |  Index Prefetch: None|  Isolation Level: Repeatable Read --注：RR隔离级别
 |  Lock Intents|  |  Table: Intent Share --注：表上加IS锁|  |  Row  : Share --注：行上加S锁
 |  Sargable Predicate(s) 
 |  |  Return Data to Application 
 |  |  |  #Columns = 3 
 Return Data Completion 
 End of section

 

6.1.6  隔离级别摘要

表 6-1 按不期望的结果概述了几个不同的隔离级别。

表 6-1   隔离级别摘要

隔离级别 访问未提交的数据 不可重复读 幻像读现象 可重复读 (RR) 不可能 不可能 不可能

读稳定性 (RS)	不可能	不可能	可能
游标稳定性 (CS)	不可能	可能	可能
未提交读 (UR)	可能	可能	可能

 

表 6-2 提供了简单的试探方法，以帮助您为应用程序选择初始隔离级别。首先考虑表中列示的方法，并参阅先前对影响各隔离级别因素的讨论，可能会找到另一个更适合的隔离级别。

表 6-2  选择隔离级别的准则

应用程序类型 需要高数据稳定性 不需要高数据稳定性 读写事务 RS CS

只读事务	RR 或 RS	UR

 

为避免应用程序出现用户无法容忍的现象，必须为其选择适当的隔离级别。在不同隔离级别下，应用程序锁定或释放资源需要不同的 CPU 和内存资源，所以隔离级别不但影响应用程序之间的隔离程度，还可能影响应用程序的个别性能特征。潜在的锁等待情况也会随隔离级别的不同而不同。

因为隔离级别确定访问数据时如何锁定数据并使数据不受其他进程影响，所以您在选择隔离级别时应该平衡并行性和数据完整性需求。您指定的隔离级别在工作单元运行期间生效。

选择正确的隔离级别

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

• 如果正在执行大型查询，而且不希望并发事务所做的修改导致查询的多次运行返回不同的结果，则使用可重复读隔离级别。
• 如果希望在应用程序之间获得一定的并发性，还希望限定的行在事务执行期间保持稳定，则使用读稳定性隔离级别。
• 如果希望获得最大的并发性，同时不希望查询看到未提交的数据，则使用游标稳定性隔离级别。
• 如果正在只读的表 / 视图 / 数据库上执行查询，或者并不介意查询是否返回未提交的数据，则使用未提交读隔离级别。

设置隔离级别

尽管隔离级别控制事务级上的行为，但实际上它们是在应用程序级被指定的：

• 对于嵌入式 SQL 应用程序，在预编译时或在将应用程序绑定到数据库 ( 如果使用延迟绑定 ) 时指定隔离级别。在这种情况下，使用 PRECOMPILE 或 BIND 命令的 ISOLATION 选项来设置隔离级别。
• 对于开放数据库连接 (ODBC) 和调用级接口 (Call Level Interface，CLI) 应用程序，隔离级别是在应用程序运行时通过调用指定了 SQL_ATTR_TXN_ISOLATION 连接属性的 SQLSetConnectAttr() 函数进行设置的。另外，也可以通过指定 DB2CLI.INI 配置文件中的 TXNISOLATION 关键字的值来设置 ODBC/CLI 应用程序的隔离级别；但是，这种方法不够灵活，不能像第一种方法那样为一个应用程序中的不同事务修改隔离级别。
• 对于 Java 数据库连接 (JDBC) 和 SQLJ 应用程序，隔离级别是在应用程序运行时通过调用 DB2 的 JAVA.SQL 连接接口中的“ setTransactionIsolation() ”方法设置的。

当没有使用这些方法显式指定应用程序的隔离级别时，默认使用游标稳定性 (CS) 隔离级别。这个默认设置被应用于从命令行处理程序 (CLP) 执行的 DB2 命令、SQL 语句和脚本，以及嵌入式 SQL、ODBC/CLI、JDBC 和 SQLJ 应用程序。因此，也可以为从 CLP 执行的操作 ( 以及传递给 DB2 CLP 进行处理的脚本 ) 指定隔离级别。在这种情况下，隔离级别是通过在建立数据库连接之前在 CLP 中执行 CHANGE ISOLATION 命令设置的，输出结果如下：

C:\pp>db2 change isolation to ur 
 DB21027E  当连接至数据库时未能更改隔离级别。
 C:\pp>db2 connect reset 
 DB20000I  SQL 命令成功完成。
 C:\pp>db2 change isolation to ur 
 DB21053W  当连接至不支持 UR 的数据库时，会发生自动升级。
 DB20000I  CHANGE ISOLATION 命令成功完成。

 

在 DB2 V7.1 及更高版本中，能够指定特定查询所用的隔离级别，方法是在 SELECT SQL 语句中加上 WITH [RR | RS | CS | UR] 子句。大家可以看到，本章前面的示例均使用这种方法举例。

---

回页首

6.2  加锁总结

6.2.1  如何获取锁

在大多数情况下，DB2 数据库管理程序在需要锁时隐式地获取它们，因此这些锁在 DB2 数据库管理程序的控制之下。除了使用未提交读隔离级别的情况外，事务从不需要显式地请求锁。实际上，唯一有可能被事务显式锁定的数据库对象是表 (LOCK TABLE) 。图 6-1 说明了用何种逻辑确定为所引用的对象获取什么类型的锁。

图 6-1  如何获取锁

从图 6-1 中我们可以看到，数据库首先判断该 SQL 语句是采用全表扫描还是索引扫描。如果是全表扫描，那么会在整个表上加表级别的锁；如果是读操作，那么获取表级 S 锁；如果是 DML(INSERT、UPDATE 和 DELETE) 操作，那么获取表级 X 锁。假设 SQL 语句采用的是索引扫描，如果是读操作，在读取的行上加 NS 锁，同时在表上加 IS 锁；如果是 DML 操作，那么在操作的行上加 X 锁，同时在表上加 IX 锁。

注意：

假设一个表中有 1000 行数据，某个 SQL 语句访问该表中的两行数据。如果该表没有索引，那么这条 SQL 只能进行全表扫描，这种情况下即使你只访问两行数据，但是由于没有索引也必须进行全表扫描，这时整个表都被加锁。

DB2 数据库管理程序默认总是尝试获取行级锁。但是，可以通过执行特殊形式的 ALTER TABLE 语句来修改这种行为，输出结果如下：

ALTER TABLE [TableName] LOCKSIZE TABLE

 

其中的 TableName 标识一个现有表的名称，所有事务在访问它时都要获取表级锁。 ALTER TABLE 语句的 LOCKSIZE 子句指定行级别或表级别的锁定作用域 ( 详细程度 ) 。默认情况下，使用行锁定。这些已定义的表锁定仅请求 S( 共享 ) 和 X( 互斥 ) 锁定。 ALTER TABLE 语句的 LOCKSIZE ROW 子句不会阻止正常的锁定升级。

也可以在应用程序中通过执行 LOCK TABLE 语句，强制 DB2 数据库管理程序为特定事务在表上获取表级锁，输出结果如下：

LOCK TABLE [TableName] IN [SHARE | EXCLUSIVE] MODE

 

其中的 TableName 标识一个现有表的名称，对于这个表应该获取表级锁 ( 假定其他事务在该表上没有不兼容的锁 ) 。如果在执行这个语句时指定了共享 (SHARE) 模式，就会获得一个允许其他事务读取 ( 但不能更改 ) 表中数据的表级锁；如果执行时指定了互斥 (EXCLUSIVE) 模式，就会获得一个不允许其他事务读取或修改表中数据的表级锁。

在下列情况下，由 ALTER TABLE 语句定义的永久表锁定可能比使用 LOCK TABLE 语句获得的单个事务表锁定更可取，原因如下：

• 表是只读的，且将始终只需要 S 锁定，其他用户也可以获取表的 S 锁定。
• 表通常由只读应用程序访问，但有时由单个用户访问可以进行简要维护，而该用户需要 X 锁定。当维护程序运行时，将只读应用程序锁定在外，但在其他情况下，只读应用程序可以使用最小的锁定开销同时访问表。

总结一下：ALTER TABLE语句全局指定锁定，它影响访问该表的所有应用程序和用户。单个应用程序可以使用LOCK TABLE语句来指定应用程序级别的表锁定。

6.2.2  意图锁和非意图锁

对于 IN、IX、IS 和 SIX 这些意图 (INTENT) 锁，读者可以这样理解：严格来说它们并不是一种锁，而是用来存放表中行锁的信息。举个通俗的例子，我们去住一个酒店。我们把整个酒店比喻成一张表，每个房间是一行。那么当我们预订一个房间时，就对该行 ( 房间 ) 加 X 锁，但是同时会在酒店的前台对该行 ( 房间 ) 做一个信息登记 ( 旅客姓名、身份证、住多长时间等 ) 。大家可以把意图锁当成是这个酒店前台的登记信息，它并不是真正意义上的锁，而是维护表中每行的加锁情况，所有访问这个表的应用程序共用这个意图锁。后续的旅客来时通过酒店前台来看哪个房间是可住的。那么如果没有意图锁，会出现什么情况呢？假设我要预订房间，那么每次我都需要到每一个房间查看确认这个房间有没有住旅客，这样的效率显然是很低下的。其实最早的 DB2 版本是没有意图锁的，但是这对并发影响非常大，后来就增加了意图锁。所有的数据库 (Oracle、Informix 和 Sybase) 都有意图锁的实现机制。在一个表上只有一个意图锁，所有应用程序共用这个意图锁，但是可能经常会更改。

6.2.3  读锁和写锁

在 DB2 数据库中有两种主要类型的锁：读锁 (S) 和写 (X) 锁。

一般来说读锁是在如下情况下加的：

NS 是在 RS 和 CS 隔离级别下对读取到的行加的锁。而 S 锁是在 RR 隔离级别下对读取到的表 ( 使用全表扫描 ) 或行 ( 使用索引扫描 ) 加的锁。 U 锁是在“ select * from t1 for update ”情况下加的锁。这些锁都是在读取 (SELECT) 期间加的锁。

一般来说写锁是在如下情况下加的：

Z 锁是超级排它锁，它不允许任何隔离级别的读取，一般是在数据物理结构发生改变的情况下加的锁。例如：CREATE、ALTER、DROP、离线 REORG 和离线 LOAD 期间会加 Z 锁。 X 锁是在做 INSERT、UPDATE 和 DELETE 期间加的锁，它允许使用 UR 隔离级别进行未提交读取。 NW 锁表示当一行被插入到索引中的时候，该行的下一行会被加上该锁。锁的拥有者可以读但不能更改锁定行。该锁与 X 锁类似，只是与 NS 锁兼容。

6.2.4  LRB(Lock Resource Block)

每个数据库都有一个锁列表，该列表包含所有同时连接到数据库的应用程序所持有的锁。在 32 位平台上，一个对象上的第一个锁要求占 72 字节，而其他锁要求占 36 字节。在 64 位平台上，第一个锁要求占 128 字节 (HP 平台为 80 字节 )，而其他锁要求占 64 字节。 关于锁占用资源块 (LRB:Lock Resource Block)，在各个版本还不一样，表 6-3 是 DB2 V9 中 LRB 占用资源的情况。

表 6-3  DB2 V9 中 LRB 占用资源的情况

Architecture	LRB Size	First Transaction to Lock	Subsequent Locks
32-bit	48 bytes	96 bytes	48 bytes
64-bit	64 bytes	128 bytes	64 bytes
64-bit HP_UX	80 bytes	160 bytes	80 bytes

 

注意：

关于 LRB，在 DB2 的各个版本很不一样。在 DB2 V8 之前，在 32 位平台上，在一个没有持有其他锁定的对象上持有一个锁定需要 72 字节，在一个持有了现存锁定的对象上记录一个锁定需要 36 字节；在 DB2 V8 的后期版本中，在一个没有持有其他锁定的对象上持有一个锁定需要 64 字节，在一个持有了现存锁定的对象上记录一个锁定需要 32 字节；在 64 位平台上，要对没有其他锁定的对象上保留锁定需要 112 字节，要对具有现有锁定的对象上保留锁定需要 56 字节。 DB2 V9 中的 LRB 情况如表 6-3 所示。

6.2.5  USE AND KEEP LOCKS

在 DB2 中，默认情况下锁都是由 DB2 数据库管理器根据应用程序的隔离级别自动设置锁类型。 DB2 提供了一种方式允许用户明确地向 DB2 数据库管理器请求锁类型：

• USE AND KEEP EXCLUSIVE LOCKS：向 DB2 数据库管理器明确请求在数据上加排它锁。
• USE AND KEEP UPDATE LOCKS：向 DB2 数据库管理器明确请求在数据上加更新锁。
• USE AND KEEP SHARE LOCKS：向 DB2 数据库管理器明确请求在数据上加共享锁。

例如：

DECLARE c1 CURSOR FOR select empno,job,salary from emp 
where job='CLERK' FOR UPDATE WITH RS USE AND KEEP EXCLUSIVE LOCKS

 

在上面的语句中，如果没有带 USE AND KEEP EXCLUSIVE LOCKS 子句，默认情况下 DB2 会向行加更新锁 (U 锁 )，使用了该子句后将会变为排它锁 (X 锁 ) 。

再如：

DECLARE c1 CURSOR FOR select empno,job,salary from emp 
where job='CLERK' FOR FETCH ONLY WITH RR USE AND KEEP UPDATE LOCKS

 

在上面的语句中，如果没有带 USE AND KEEP EXCLUSIVE LOCKS 子句，默认情况下 DB2 会向行加下一键共享锁 (NS 锁 )，使用了该子句后将会变为更新锁 (U 锁 ) 。

再如：

DECLARE c1 CURSOR FOR select empno,job,salary from emp 
where job='CLERK' FOR UPDATE WITH RS USE  AND  KEEP SHARE LOCKS

 

在上面的语句中，如果没有带 USE AND KEEP EXCLUSIVE LOCKS 子句，默认情况下 DB2 会向行加更新锁 (U 锁 )，使用了该子句后将会变为下一键共享锁 (NS 锁 ) 。

为什么要这样显式请求锁类型呢？这是因为 USE AND KEEP LOCKS 显式请求锁类型有助于避免多个存取数据库的独立进程的应用程序可能产生的死锁。例如，在一个应用程序中的数个进程存取同一个表，对该表并行进行读取及写入操作。如果这些进程执行读 SQL 查询，然后再对同一表执行 SQL 更新，那么各个进程间对同一数据潜在的争用会使得死锁的几率增大。例如，如果两个进程读该表，然后更新该表，那么 A 进程先获得 S 锁，同时 B 进程也获得 S 锁。当 A 进程发出更新语句时试图获得对行的 X 锁定，而 B 进程对该行具有 S 锁定。此时 A 进程进入锁等待 (Lock Wait) 状态，等待 B 进程释放 S 锁。当后来进程发出更新语句时试图获得对行的 X 锁定，而进程 A 对该行具有 S 锁定。此时 B 进程进入锁等待 (LOCK WAIT) 状态，等待 A 进程释放 S 锁。这样就产生了死锁，为了避免发生这种死锁，存取具有修改意向的数据的应用程序应该执行下列其中一项操作：

• 执行选择操作时使用 FOR UPDATE OF 子句。此子句确保当 A 进程试图读取该数据时进行 U 锁定，禁用行分块 (BLOCKING) 。
• 执行查询时使用 WITH RR USE AND KEEP UPDATE LOCKS 子句或 WITH RS USE AND KEEP UPDATE LOCKS 子句。任一子句都确保当 A 进程试图读取该数据时进行 U 锁定，并且允许行分块 (BLOCKING) 。

6.2.6  索引类型和下一键锁

DB2 中有两种索引类型：type-1 索引和 type-2 索引。这两种索引加锁的情况是不一样的，下面我们来分别介绍这两种索引的加锁算法。

type-1 索引加锁算法

在 DB2 V8 之前，DB2 只有一种索引类型，也就是我们今天称之为的 TYPE-1 索引，这种索引在删除和插入的时候特别容易引起死锁从而影响并发。是什么原因呢，下面我们举一个使用 TYPE-1 索引的例子：

假设一个索引的叶子 (LEAF) 中包含 1、5、6、7、8、12 6 个 KEY 。

• 假如现在交易 1 删除 KEY VALUE 8 对应的行，在删除期间，KEY VALUE 8 对应的行上会加 X 锁。当 KEY VALUE 8 被删除以后，就会在索引的下一键也就是 8 的下一个键 12 上加 NX 锁，相应地会在 KEY VALUE 12 对应的行上加 X 锁。
• 如果另外一个交易 2 删除 KEY VALUE 5 对应的行，在删除期间，KEY VALUE 5 对应的行上会加 X 锁。当 KEY VALUE 5 被删除以后，就会在索引的下一键也就是 5 的下一个键 6 上加 NX 锁，相应地会在 KEY VALUE 6 对应的行上加 X 锁。
• 假设现在交易 1 插入一行 KEY VALUE 4 相应的行，这一行会加 W 锁，当插入新的 KEY 到索引的时候，KEY VALUE 6 对应的行会加 NW 锁。因为此时交易 2 对应的行上持有 X 锁，这时它不得不等待交易 2 释放掉该锁。
• 同样假设现在交易 2 插入一行 KEY VALUE 9 相应的行，这一行会加 W 锁，当插入新的 KEY 到索引的时候，KEY VALUE 12 对应的行会加 NW 锁。因为此时交易 1 对应的行上持有 X 锁，这时它不得不等待交易 1 释放掉该锁。

所以在 type-1 索引时，因为更改期间加锁的方式是 W 和 NW，所以很容易造成死锁，这会极大影响数据库的并发。

type-2 索引加锁算法

DB2 从 V8 以后所有新创建的索引都是 type-2 类型的索引。 type-2 索引可以大大減少 NEXT KEY 锁从而改进了性能，因为各项是标记为删除的而不是从页面中物理删除的 ( 伪删除 ) 。 type-2 索引同时允许索引列大于默认的 255 字节，同时还可以在线运行 REORG 和 RUNSTATS，并且支持新的多维群集 (MDC) 功能。在 DB2 V8 中，所有新的索引都是以 type-2 类型创建的，只有已经在表上定义了 ( 迁移前 ) type-1 索引的時候除外。可以使用 REORG INDEXES 将 type-1 索引转换为 type-2 索引。 Type-2 索引采用的是伪删除算法，图 6-2 是两种索引类型加锁的比较。

图 6-2  type-1 和 type-2 索引加锁比较

在 DB2 V8 之前的版本中，插入过程中可能使用 W 或 NW 锁，但是在 DB2 V8 以后只有在使用了隔离级别为 RR 的情况下才会出现这两种锁。因此，应尽可能避免这种情况。

6.2.7  扫描方式加锁情况

在 DB2 数据库中，不同的扫描方式在不同的隔离级别下加锁的情况也是不一样的，在 DB2 中主要有以下几种扫描方式：全表扫描、索引扫描和 RID 扫描 ( 注：如果使用了 MDC，那么还会有其他扫描方式，但此处我们不讨论 ) 。其中 RID 扫描也算是索引扫描。表 6-5 和表 6-6 总结了在全表和索引扫描方式下加锁的情况。

表 6-4  表扫描时在表 / 行上加锁情况


表 6-5  索引扫描时在表 / 行上的加锁情况

关于在不同隔离级别和扫描方式下表 / 行上的加锁情况，我们可以在 DB2 解释工具的输出中查看加锁情况。下面我们分别对全表扫描和索引扫描的加锁情况举例：

全表扫描加锁情况如下：

dynexpln – d sample – q "select * from employee with rr" -t 
 ---------------------------- 略 -------------------------- 
 |  Isolation Level: Repeatable Read 
 |  Lock Intents 
 |  |  Table: Share 
 |  |  Row  : None 
 |  Sargable Predicate(s) 
 ---------------------------- 略 ------------------------------ 
 dynexpln – d sample – q "select * from employee with rs" -t|  Relation Scan --注：全表扫描 
|  |  Prefetch: Eligible|  Isolation Level: Read Stability --注：隔离级别为RS
|  Lock Intents|  |  Table: Intent Share --注：表上加IS锁
 |  |  Row  : Next Key Share--注：行上加 NS 锁
 |  Sargable Predicate(s)

 

索引扫描加锁情况如下：

dynexpln – d sample – q "select * from employee where empno='000100' with rs" -t 
 ---------------------------- 略 -------------------------- 
 |  Index Scan:  Name = ORACLE.PK_EMPLOYEE  ID = 1 
 |  |  Regular Index (Not Clustered) 
 |  |  Index Columns: 
 |  |  |  1: EMPNO (Ascending) 
 |  #Columns = 13 
 |  Volatile Cardinality 
 |  Single Record 
 |  Fully Qualified Unique Key 
 |  #Key Columns = 1 
 |  |  Start Key: Inclusive Value 
 |  |  |  |  1: '000100' 
 |  |  Stop Key: Inclusive Value 
 |  |  |  |  1: '000100' 
 |  Data Prefetch: None 
 |  Index Prefetch: None|  Isolation Level: Read Stability --注：隔离级别为RS
|  Lock Intents|  |  Table: Intent Share --注：表上加IS锁
|  |  Row  : Next Key Share --注：行上加NS锁

 

通过上面两个例子，我们希望读者能够明白关于扫描方式和隔离级别的加锁情况，关键是如何分析加锁情况。

---

回页首

6.3  乐观锁

6.3.1  悲观锁定和乐观锁定

悲观锁定

DB2 默认情况下都是采用悲观锁定方式工作，本节上面讲述的各个例子都属于这种情况。

悲观锁定策略的前提是，另一个用户很可能试图修改您正在修改的某个表行。如果选择了某个行，并检测到其他用户试图对该行执行更新或删除操作，那么该行将在这段时间内持有锁 ( 例如，通过使用 RR 隔离级别或以排它锁 (X) 模式锁定表 ) 。悲观锁定的优点就是能够保证实现一致且安全的更改。但是这种锁定策略的主要缺点就是并发性较差。对于具有大量用户或运行长期事务的系统，或者涉及大量 SQL 操作的事务，需要等待锁释放的概率则会增加。

图 6-3 阐释了悲观锁定的功效。事务 1 读取某条特定记录并对该行应用一个锁，并且需要花些时间确定是否要对这个行执行更新操作。同时，事务 2 希望访问这个行，但是它必须等待事务 1 释放该行的锁。也就是说，只有事务 1 释放锁后，事务 2 才能收到SELECT操作的结果并继续执行它的业务逻辑。

图 6-3   悲观锁定的工作示意图

乐观锁定

悲观锁定方法的主要问题是事务之间必须互相等待。避免发生这种情况的方法就是使用乐观锁定策略，即假设在修改某行时，另一个用户试图对这一行进行修改的可能性极低。如果确实对这一行进行了修改，那么更新或删除操作将会失败，应用程序逻辑将处理这些失败，例如重新尝试选择。通过使用这种方法，使得事务在对行执行选择、更新或删除操作期间，不会持有行锁。但是，由此产生的问题是需要一种方式确保数据在被读取和修改期间没有发生变化。尽管应用程序需要更多的重试逻辑，但乐观锁定策略的主要优点是通过最小化给定资源对其他事务的不可用时间，来减少锁竞争，同时不会牺牲数据完整性 ( 虽然乐观锁定刚刚被引入 DB2 FOR Linux/UNIX/Windows，但是早已被提供给 DB2 FOR z/OS 用户 ) 。因此，它具有比悲观锁定更好的伸缩性。

图 6-4 阐释了乐观锁定背后的思想。与图 6-3 类似，事务 1 读取某个特定记录，但随后即释放锁。因此，事务 2 现在可以顺利地对同一行进行检索。在提交事务之前，事务 1 和事务 2 都必须检查该行在执行前面的SELECT之后是否发生改变。如果发生了一处修改，事务就必须重新执行新的SELECT来检索当前数据。然而，若该行在执行SELECT之后未发生变化，则可以成功更新数据。

图 6-4  乐观锁定的工作示意图

6.3.2  DB2 V9.5 的乐观锁定

DB2 V9.5 的乐观锁定特性最小化了给定资源对于其他事务的不可用时间，进一步改善了并发性。由于数据库管理器能够确定某一行何时会被修改，因此可以保证数据完整性，同时限制持有锁的时间。通过实现乐观并发控制，数据库管理器可以在完成读操作后立即释放行或页锁。

DB2 V9.5 支持的乐观锁定特性简便、快捷，并且不会产生误判 (False Positive) 。这一特性通过如下所示的新 SQL 函数、表达式和特性来实现：

• 行标识符(RID_BIT或RID)内置函数：该内置函数可用于SELECT列表或谓词语句。例如，在谓词“WHERE RID_BIT(tab)=? ”中，RID_BIT等于谓词被实现为一种新的直接访问方法，从而可以更有效地定位行。在 DB2 V9.5 以前，这种称为值乐观锁定的技术确定值的方式为：将所有选择的列值添加到谓词，然后应用某些唯一的列组合来筛选出单个行，这种访问方法效率较低。
• ROW CHANGE TOKEN表达式：这种新的表达式返回一个标记作为 BIGINT 。这个标记表示某一行的修改序列中的一个相对点。应用程序可以将某行的当前行修改标记值与上次取回行时保存的行修改标记值进行比较，以判断行是否发生修改。
• 基于时间的更新检测：这个特性通过ROW CHANGE TIMESTAMP表达式添加到 SQL 中。要支持这一特性，表需要定义一个新生成的行修改时间戳列来保存时间戳值。这可以通过ALTER TABLE语句添加到现有表，或者在创建新表时定义行修改时间戳列。是否提供行修改时间戳列还将影响乐观锁定的行为，因为该列有助于将行修改标记的粒度从页级别提高到行级别，这对乐观锁定应用程序非常有利。
• 隐式隐藏列：从兼容性方面来说，这个特性有助于将行修改时间戳列应用到现有表和应用程序。在使用隐式列列表时，隐式隐藏列不会被外部化。例如，对表执行SELECT* 时不会在结果表中返回隐式隐藏的列，并且执行不包含列列表的INSERT语句时也不会要求提供隐式隐藏列的值，但是隐式隐藏列必须定义为允许 NULL 值或具有另一个默认值。

使用上述编程模型的应用程序将从增强的乐观锁定特性中获益。注意，未使用这种编程模型的应用程序被认为是非乐观锁定应用程序，它们将按照以前的方式工作。

图 6-5 阐释了 DB2 V9.5 的乐观锁定特性的功效。事务 1 和事务 2 同时读取相同的行，包括 RID_BIT 和 ROW CHANGE TOKEN 值。随后，事务 1 在执行完SELECT并确保该行未发生修改后，通过将RID_BIT 和 ROW CHANGE TOKEN谓词添加到UPDATE语句对行进行更新。现在，当事务 2 尝试使用与事务 1 相同的谓词对同一行进行更新时，它无法查找到该行，因为ROW CHANGE TOKEN的值已经根据事务 1 的UPDATE进行了更改。事务 2 必须进行重试更新，以取回最新的数据。

图 6-5  DB2 V9.5 的乐观锁定特性

启用乐观锁定特性

由于不需要对表进行 DDL 修改即可使用针对乐观锁定的新 SQL 表达式和属性，因此可以轻松地在您的测试应用程序中尝试乐观锁定特性。

注意，在不修改表结构增加 ROW CHANGE TIMESTAMP 列的情况下，乐观锁定应用程序可能会产生更多的误判 (False Positive) 。在生产环境中，如果应用程序发生了误判 (False Positive)，就不能够实现较好的并发，因为误判很可能造成大量重试操作。因此，要避免发生误判，执行乐观锁定的目标表应执行以下任意一种 DDL 操作：

• 创建时定义 ROW CHANGE TIMESTAMP 列
• 修改以增加 ROW CHANGE TIMESTAMP 列

当在应用程序中希望直接启用乐观锁定支持而不愿意修改表结构时，只需要执行以下基本步骤即可：

(1) 在初始查询中，对要进行处理的所有行的行标识符和行修改标记执行SELECT( 使用RID_BIT() 和 RID()内置函数 ) 。

(2) 释放行锁，以便其他应用程序可以对表执行SELECT、INSERT、UPDATE 和 DELETE( 例如，使用游标稳定性 (CS) 隔离级别或未提交读 (UR) 隔离级别 ) 。

(3) 对目标行执行可搜索的UPDATE或DELETE，在搜索条件中使用行标识符和行修改标记，乐观地假定自从执行最初的SELECT语句后，未锁定的行没有发生过修改。

(4) 如果行发生了修改，UPDATE操作将失败，应用程序逻辑必须处理这一失败。例如，应用程序将重试SELECT 和 UPDATE操作。

运行以上步骤之后，如果存在下述情况，则可以考虑更新表结构增加 ROW CHANGE TIMESTAMP 列：

• 如果应用程序执行重试的次数超过预期值或与预期值相同，那么向表添加一个 ROW CHANGE TIMESTAMP 列，以确保RID_BIT函数对行标识符作出的修改只会使行修改标记无效，而同一数据页上的其他活动不受影响。
• 要查看给定时间范围内执行了插入或更新操作的行，需要创建或修改表以包含一个 ROW CHANGE TIMESTAMP 列。该列由数据库管理器自动维护，并可以通过列名或ROW CHANGE TIMESTAMP表达式进行查询。

对于 ROW CHANGE TIMESTAMP 列 ( 只针对这种列 )，如果该列使用IMPLICITLY HIDDEN属性定义，那么当对表列进行隐式引用时，不会对该列执行外部化。然而，在 SQL 语句中，可以始终显式引用一个隐式隐藏的列。当向表添加列可能造成使用隐式列列表的应用程序失败时，这一特性非常有用。

行修改标记的粒度和误判

RID_BIT()内置函数和行修改标记是实现乐观锁定的唯一需求。然后使用 ROW CHANGE TIMESTAMP 列会促使 DB2 服务器保存最后一次修改 ( 或第一次插入 ) 行的时间。这提供了一种方式捕获最近一次修改行的时间戳。可以通过以下任意一条语句定义行修改时间戳列：

• GENERATED ALWAYSFOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
  该时间戳列始终由数据库管理器维护。
• GENERATED BY DEFAULTFOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
  该时间戳列默认情况下由数据库管理器维护，但是也接受用户提供的输入值。

当应用程序对表使用新的 ROW CHANGE TOKEN 表达式时，需要考虑以下两种可能性：

• 表没有定义行修改时间戳列：ROW CHANGE TOKEN 表达式返回一个派生的 BIGINT 值，由同一页面中的所有行共享。如果页面中的某行被更新，那么将针对该页面中的所有行修改行修改标记。这意味着对其他行进行修改时更新会失败，这一特性被称为误判。

注意：

只有在应用程序可以容忍误判，并且不希望向每一行添加针对 ROW CHANGE TIMESTAMP 列的额外存储的情况下，才使用这种模式。

• 表具有一个行修改时间戳列：ROW CHANGE TOKEN 表达式返回一个从列的时间戳值获得的 BIGINT 值。在这种情况下，发生误判的几率大大减少。如果对表进行了重组和重新分布，那么当移动某行并且应用程序使用以前的RID_BIT()值时，将发生误判现象。

可通过以下SELECT 语句检查行修改时间戳列是否存在，SELECT 查询输出如下所示：

SELECT COLNAME, ROWCHANGETIMESTAMP, GENERATED FROM SYSCAT.COLUMNS 
 WHERE TABNAME='tablename' AND ROWCHANGETIMESTAMP='Y' 
  COLNAME ROWCHANGETIMESTAMP  GENERATED 
  ------------ ------------------  --------- 
  ROWCHGTS Y      A

 

在上述输出中，存在一个行修改时间戳列 ROWCHGTS，并通过 GENERATED ALWAYS 子句定义 ( 值“ A ”表示 GENERATED ALWAYS，而值“ D ”表示 GENERATED BY DEFAULT) 。

基于时间的更新检测

某些应用程序需要了解特定时间范围内的数据库更新，以便进行数据复制、场景审计等等。这可以通过包含行修改时间戳列的表实现，通过定义行修改时间戳列来保存ROW CHANGE TIMESTAMP表达式生成的时间戳值。这种新的ROW CHANGE TIMESTAMP表达式返回的时间戳表示最后一次进行行修改的时间，使用类似于CURRENT TIMESTAMP的本地时间表示。对于已经更新过的行，将反映对行执行的最近更新。否则，该值将对应于最初的行插入时间。

自ALTER TABLE语句之后未进行更新的行将返回列的类型默认值，该值为 0001 年 1 月 1 日午夜。只有进行过更新的行才具有唯一的时间戳。使用离线表重组对时间戳进行具体化的行将返回一个唯一的时间戳，该时间戳在表重组期间生成。REORG仅仅使用INPLACE选项无法满足需求，因为它没有对模式修改进行具体化。 下面是一些基于时间的更新检测示例，具有行修改时间戳列的表如下所示：

CREATE TABLE EMPLOYEE (EMPNO CHAR(6) NOT NULL, 
  ...... 
  ROWCHGTS TIMESTAMP NOT NULL 
 GENERATED ALWAYS 
  FOR EACH ROW ON UPDATE AS 
  ROW CHANGE TIMESTAMP)

 

对于未定义行修改时间戳列的表，可稍后通过下面这条 ALTER TABLE 语句添加，如下所示：

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

 

选择在最近 30 天内发生修改的所有行的 SQL 语句如下所示：

SELECT * FROM EMPLOYEE WHERE 
  ROW CHANGE TIMESTAMP FOR EMPLOYEE <= CURRENT TIMESTAMP AND 
  ROW CHANGE TIMESTAMP FOR EMPLOYEE >= CURRENT TIMESTAMP - 30 days

 

表 6-6 展示了在创建具有行修改时间戳列的表后，使用 INSERT、IMPORT 或 LOAD 填充后的 ROW CHANGE TIMESTAMP 列的内容。

表 6-6  INSERT、IMPORT 或 LOAD 填充后的 ROW CHANGE TIMESTAMP 列的内容

EMPNO	FIRSTNME	LASTNAME	PHONENO	ROW CHANGE TIMESTAMP
000010	CHRISTINE	HAAS	3978	2008-12-20 13:53:01.296000
…	…	…	…	…
000030	SALLY	KWAN	4738	2008-12-20 13:53:01.312001

 

表 6-7 展示了将行修改时间戳列添加到现有表后，ROW CHANGE TIMESTAMP 列的内容。

表 6-7  行修改时间戳列添加到现有表后，ROW CHANGE TIMESTAMP 列的内容

EMPNO	FIRSTNME	LASTNAME	PHONENO	ROW CHANGE TIMESTAMP
000010	CHRISTINE	HAAS	3978	0001-01-01 00:00:00.000000
…	…	…	…	…
000030	SALLY	KWAN	4738	0001-01-01 00:00:00.000000

 

隐式隐藏列

这一特性有利于将行修改时间戳列应用到现有表和应用程序中。CREATE或ALTER TABLE语句中的IMPLICITLY HIDDEN属性表示：除非根据名称显式引用列，否则该列在 SQL 语句中不可见。例如，假设某个表包含一个使用IMPLICITLY HIDDEN子句定义的列，SELECT *操作的结果将不会包含隐式隐藏的列。然而，如果SELECT显式引用隐式隐藏列的名称，那么它将在结果表中包含该列。只有 ROW CHANGE TIMESTAMP 列才能使用IMPLICITLY HIDDEN 属性。行修改时间戳的隐式隐藏列的声明方法如下：

CREATE TABLE SALARY_INFO (

LEVEL INT NOT NULL, 
  SALARY INT NOT NULL, 
  UPDATE_TIME TIMESTAMP NOT NULL 
  IMPLICITLY HIDDEN 
  GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP) 
  或
  ALTER TABLE SALARY_INFO 
  ADD COLUMN UPDATE_TIME TIMESTAMP NOT NULL 
  IMPLICITLY HIDDEN 
  GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

 

使用 DESCRIBE 命令显示表列的结果输出，如下所示：

DESCRIBE TABLE SALARY_INFO 
  Data type  Column 
  Column name  schema  Data type name  Length    Scale   Nulls 
  -----------   --------- -------------------   ---------- ----- ------ 
  LEVEL  SYSIBM  INTEGER  4  0    No 
  SALARY  SYSIBM  INTEGER  4  0    No 
  UPDATE_TIME  SYSIBM  TIMESTAMP  10  0   No

 

针对隐式隐藏的列执行 INSERT 和 SELECT 操作，如下所示：

INSERT INTO SALARY_INFO VALUES (1, 50000) 
  SELECT * FROM SALARY_INFO 
  LEVEL   SALARY 
  ----------- ----------- 
  1   50000

 

可以通过下面的 SQL 语句显式引用隐式隐藏列的 INSERT 和 SELECT：

INSERT INTO SALARY_INFO (LEVEL, SALARY, UPDATE_TIME) 
  VALUES (2, 30000, DEFAULT) 
 SELECT LEVEL, SALARY, UPDATE_TIME FROM SALARY_INFO  WHERE LEVEL = 2 
  LEVEL  SALARY  UPDATE_TIME 
  -----------   ----------- -------------------------- 
 2  30000   2008-12-18-15.34.24.437000

 

乐观锁定特性的局限性和注意事项

• 不能指定为 ROW CHANGE TIMESTAMP 的列包括：主键、外键、多维聚合 (MDC) 列、范围分区 (RANGE PARTITION) 列、数据库哈希分区键、DETERMINED BY 约束列和别名。
• DPF 配置不支持RID()函数。
• 在乐观锁定场景中，在取回到更新操作期间执行在线或离线表REORG可能会造成更新失败，但是普通的应用程序重试逻辑应该能够处理。
• 在 DB2 V9.5 中，IMPLICITLY HIDDEN属性只能应用于 ROW CHANGE TIMESTAMP 列以实现乐观锁定。
• 对于后来才添加 ROW CHANGE TIMESTAMP 列的表，在保证所有行已被具体化之前，INPLACE REORG的使用会受到限制 ( 返回错误代码 SQL2219, REASON CODE=13) 。这可通过LOAD REPLACE命令或典型的表REORG实现。这将防止发生误判，具有 ROW CHANGE TIMESTAMP 列的表没有限制。

6.3.3  乐观锁应用案例

上面我们讲解了乐观锁的原理和实现技术，现在我们来举一个乐观锁的应用案例。假设某员工履行新的工作职责并被调到另一个部门工作。公司的两名经理 ( 原部门的经理 MANAGER1 和新部门的经理 MANAGER2) 正在使用人事管理应用程序更新 SAMPLE 数据库的 EMPLOYEE 表中的员工记录。此时存在一种可能，即两名经理可能同时对该员工的记录进行更新。当 MANAGER1 选择并更新这条员工记录时，MANAGER2 也对同一条记录执行更新。这时，乐观锁定特性将发挥作用，例如，它使 MANAGER2 在使用当前应用程序更新时了解到某个特定记录已经被更新过。这样，应用程序可以更容易地执行指令，因为它不必实现自己的更新检测逻辑。下面我们讲述几个常见的应用场景：

场景 1

EMPLOYEE 表包含一个隐式隐藏的 ROW CHANGE TIMESTAMP 列 ( 后来添加的 )，并且只有 MANAGER1 访问了该表。 MANAGER1 从 EMPLOYEE 表中选择数据，并在稍后尝试将“ Christine Haas ”的电话号码 3978 更新为 1092 。更新成功。这个场景的主要步骤如下：

(1) MANAGER1 执行下面的 SELECT 语句：

SELECT RID_BIT(EMPLOYEE),

ROW CHANGE TOKEN FOR EMPLOYEE, 
  EMPNO, FIRSTNME, LASTNAME, PHONENO, ROWCHGTS 
  FROM EMPLOYEE FETCH FIRST 3 ROWS ONLY

 

(2) SELECT 的输出结果如下：

乐观锁定表达式 EMPLOYEE 表

RID_BIT ROW CHANGE TOKEN  EMPNO  FIRSTNME  LASTNAME  PHONENO ROW CHANGE TIMESTAMP 
 x'04004001000000000000000000FA9023' 74904229642240  000010  CHRISTINE  
HAAS  3978  0001-01-01 00:00:00.000000 
 x'05004001000000000000000000FA9023' 74904229642240  000020  MICHAEL 
THOMPSON  3476  0001-01-01 00:00:00.000000 
 x'06004001000000000000000000FA9023' 74904229642240  000030  SALLY  
KWAN  4738  0001-01-01 00:00:00.000000
			

 

(3) MANAGER1 执行下面的 UPDATE 语句：

UPDATE EMPLOYEE SET

(FIRSTNME,LASTNAME,PHONENO) = ('CHRISTINE','HAAS','1092') 
  WHERE RID_BIT(EMPLOYEE)=x'04004001000000000000000000FA9023' AND 
  ROW CHANGE TOKEN FOR EMPLOYEE=74904229642240

 

(4) UPDATE 的输出结果如下：

RID_BIT ROW CHANGE TOKEN EMPNO FIRSTNME LASTNAME  PHONENO ROW CHANGE TIMESTAMP 
 x'04004001000000000000000000FA9023' 141285645885181032 000010 CHRISTINE 
HAAS 1092  2008-12-20 11:55:45.593000 
 x'05004001000000000000000000FA9023' 74904229642240 000020 MICHAEL 
THOMPSON 3476  0001-01-01 00:00:00.000000 
 x'06004001000000000000000000FA9023' 74904229642240 000030 SALLY 
KWAN 4738  0001-01-01 00:00:00.000000

 

在上面的这个场景中，我们可以看到当MANAGER1执行UPDATE操作后，EMPLOYEE表中 ROW CHANGE TIMESTAMP 字段从默认的“ 0001-01-01:00:00:00.000000 ”变为“ 2008-12-20:11:55:45.59 ”，也就是进行更改的那个时刻的时间戳。

场景 2

EMPLOYEE 表包含一个隐式隐藏的 ROW CHANGE TIMESTAMP 列，并且 MANAGER1 和 MANAGER2 同时访问该表。 MANAGER1 从 EMPLOYEE 表中选择数据并稍后尝试更新这些数据。然而，从他选择这些数据到执行更新期间，MANAGER2 对相同的数据进行了更新。 MANAGER2 执行的更新成功，而 MANAGER1 执行的更新失败。主要步骤如下：

(1) MANAGER1 和 MANAGER2 执行 SELECT 的输出结果如下所示：

RID_BIT ROW CHANGE TOKEN EMPNO FIRSTNME LASTNAME  PHONENO ROW CHANGE TIMESTAMP 
 x'04004001000000000000000000FA9023' 74904229642240 000010 CHRISTINE 
HAAS 3978  0001-01-01 00:00:00.000000 
 x'05004001000000000000000000FA9023' 74904229642240 000020 MICHAEL 
THOMPSON 3476  0001-01-01 00:00:00.000000 
 x'06004001000000000000000000FA9023' 74904229642240 000030 SALLY 
KWAN 4738  0001-01-01 00:00:00.000000

 

(2) MANAGER2 执行下面的 UPDATE 语句：

UPDATE EMPLOYEE SET 
  (FIRSTNME,LASTNAME,PHONENO) = ('CHRISTINE','HAAS','1092') 
  WHERE RID_BIT(EMPLOYEE)=x'04004001000000000000000000FA9023' AND 
  ROW CHANGE TOKEN FOR EMPLOYEE=74904229642240

 

(3) MANAGER2 执行 UPDATE 的输出结果如下：

RID_BIT ROW CHANGE TOKEN EMPNO FIRSTNME LASTNAME  PHONENO ROW CHANGE TIMESTAMP 
 x'04004001000000000000000000FA9023' 141285645885181032 000010 CHRISTINE 
HAAS 1092  2008-12-20 11:55:45.593000 
 x'05004001000000000000000000FA9023' 74904229642240 000020 MICHAEL 
THOMPSON 3476  0001-01-01 00:00:00.000000 
 x'06004001000000000000000000FA9023' 74904229642240 000030 SALLY 
KWAN 4738  0001-01-01 00:00:00.000000

 

(4) MANAGER1 执行下面 UPDATE 语句：

UPDATE EMPLOYEE SET 
  (FIRSTNME,LASTNAME,PHONENO) = ('CHRISTINE','HAAS','1092') 
  WHERE RID_BIT(EMPLOYEE)=x'04004001000000000000000000FA9023' AND 
  ROW CHANGE TOKEN FOR EMPLOYEE=74904229642240

 

(5) MANAGER1 执行 UPDATE 失败，返回 SQL0100W 信息。

MANAGER1 的更新失败。由于 MANAGER2 执行了UPDATE，ROW CHANGE TOKEN 发生了改变，因此，当将执行SELECT时取回的标记与由 MANAGER2 的应用程序更新后的当前值进行比较时，MANAGER1 的 UPDATE 语句的 ROW CHANGE TOKEN 谓词失败。因此UPDATE无法找到指定的行，返回消息“SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 ”。

场景 3

EMPLOYEE 表包含一个隐式隐藏的 ROW CHANGE TIMESTAMP 列，并且 MANAGER1 和 MANAGER2 同时访问该表。 MANAGER1 对行进行了更新，但还未提交修改。 MANAGER2 使用 UR 隔离级别从 EMPLOYEE 表中选择数据。 MANAGER1 提交他做出的修改。 MANAGER2 尝试对相同的数据进行更新。 MANAGER2 执行更新成功，因为应用程序读取的是 MANAGER1 未提交的更新。然而，如果 MANAGER1 回滚更新而不是提交更新，MANAGER2 的更新将失败。这个场景的主要步骤如下：

(1) MANAGER1 执行 SELECT 的输出结果如下：

RID_BIT ROW CHANGE TOKEN EMPNO FIRSTNME LASTNAME  PHONENO ROW CHANGE TIMESTAMP 
 x'04004001000000000000000000FA9023' 74904229642240 000010 CHRISTINE 
HAAS 3978  0001-01-01 00:00:00.000000 
 x'05004001000000000000000000FA9023' 74904229642240 000020 MICHAEL 
THOMPSON 3476  0001-01-01 00:00:00.000000 
 x'06004001000000000000000000FA9023' 74904229642240 000030 SALLY 
KWAN 4738  0001-01-0100:00:00.000000

 

(2) MANAGER1 执行如下的 UPDATE 语句，未提交确认：

UPDATE EMPLOYEE SET 
  (FIRSTNME,LASTNAME,PHONENO) = ('CHRISTINE','HAAS','1092') 
  WHERE RID_BIT(EMPLOYEE)=x'04004001000000000000000000FA9023' AND 
  ROW CHANGE TOKEN FOR EMPLOYEE=74904229642240

 

(3) MANAGER2 使用隔离级别 UR 执行 SELECT 的结果输出如下：

RID_BIT ROW CHANGE TOKEN EMPNO FIRSTNME LASTNAME  PHONENO ROW CHANGE TIMESTAMP 
 x'04004001000000000000000000FA9023' 141285665533242120 000010 CHRISTINE 
HAAS 1092  2008-12-20 16:47:03.125000 
 x'05004001000000000000000000FA9023' 74904229642240 000020 MICHAEL 
THOMPSON 3476  0001-01-01 00:00:00.000000 
 x'06004001000000000000000000FA9023' 74904229642240 000030 SALLY 
KWAN 4738  0001-01-01 00:00:00.000000

 

(4) MANAGER1 提交 UPDATE 语句，提交后输出结果如下：

RID_BIT ROW CHANGE TOKEN EMPNO FIRSTNME LASTNAME  PHONENO ROW CHANGE TIMESTAMP 
 x'0400400100000000 
 0000000000FA9023' 141285665533242120 000010 CHRISTINE 
HAAS 1092  2008-12-20 
 16:47:03.125000 
 x'0500400100000000 
 0000000000FA9023' 74904229642240 000020 MICHAEL 
THOMPSON 3476  0001-01-01 
 00:00:00.000000 
 x'0600400100000000 
 0000000000FA9023' 74904229642240 000030 SALLY 
KWAN 4738  0001-01-01 
 00:00:00.000000

 

(5) MANAGER2 执行如下的 UPDATE 语句：

UPDATE EMPLOYEE SET 
  (FIRSTNME,LASTNAME,PHONENO) = ('CHRISTINE','HAAS','1090') 
  WHERE RID_BIT(EMPLOYEE)=x'04004001000000000000000000FA9023' AND 
  ROW CHANGE TOKEN FOR EMPLOYEE=141285665533242120

 

(6) MANAGER1 提交修改而 MANAGER2 尝试更新相同的数据，结果如下：

RID_BIT ROW CHANGE TOKEN EMPNO  FIRSTNME  LASTNAME  PHONENO ROW CHANGE TIMESTAMP 
 x'04004001000000000000000000FA9023' 141285667099502664  000010  CHRISTINE  
HAAS  1090  2008-12-20 16:51:53.125000 
 x'05004001000000000000000000FA9023' 74904229642240  000020  MICHAEL 
THOMPSON  3476  0001-01-01 00:00:00.000000 
 x'06004001000000000000000000FA9023' 74904229642240  000030  SALLY  
KWAN  4738  0001-01-01 00:00:00.000000

 

MANAGER2 最后执行的更新成功，因为应用程序从 MANAGER1 处读取的是未提交的更新； MANAGER2 的UPDATE语句中的ROW CHANGE TOKEN谓词成功，因为 MANAGER1 使用新标记提交了修改。

(7) MANAGER1 发出 ROLLBACK 而不是 COMMIT 时的输出结果如下：

RID_BIT ROW CHANGE TOKEN EMPNO FIRSTNME LASTNAME  PHONENO ROW CHANGE TIMESTAMP 
 x'04004001000000000000000000FA9023' 74904229642240 000010 CHRISTINE 
HAAS 3978  0001-01-01 00:00:00.000000 
 x'05004001000000000000000000FA9023' 74904229642240 000020 MICHAEL 
THOMPSON 3476  0001-01-01 00:00:00.000000 
 x'06004001000000000000000000FA9023' 74904229642240 000030 SALLY 
KWAN 4738  0001-01-01 00:00:00.000000

 

MANAGER1 对修改执行回滚后，MANAGER2 试图根据 MANAGER1 的未提交的UPDATE执行数据更新， MANAGER2 最后执行的更新失败，因为当 MANAGER1 回滚到初始标记时，ROW CHANGE TOKEN谓词失败，因此UPDATE无法找到特定行。

场景 4

EMPLOYEE 表不包含 ROW CHANGE TIMESTAMP 列，并且 MANAGER1 和 MANAGER2 同时访问该表。 MANAGER1 选择一行并尝试更新它。然而，从他进行选择到更新期间，MANAGER2 更新了同一数据页中的其他数据 ( 不一定与 MANAGER1 处理的数据相同，但是位于另一行中 ) 。因此，当 MANAGER1 尝试更新数据时，更新将会失败。 这个场景的主要步骤如下：

(1) MANAGER2 执行如下 UPDATE 语句：

UPDATE EMPLOYEE SET

(FIRSTNME,LASTNAME,PHONENO) = ('CHRISTINE','HAAS','1092') 
  WHERE RID_BIT(EMPLOYEE)=x'04004001000000000000000000FA9023' AND 
  ROW CHANGE TOKEN FOR EMPLOYEE=74904229642240

 

(2) MANAGER2 执行 UPDATE，成功后的输出结果如下：

RID_BIT ROW CHANGE TOKEN EMPNO FIRSTNME LASTNAME  PHONENO 
 x'04004001000000000000000000FA9023' 141285645885181032 000010 CHRISTINE 
HAAS  1092 
 x'05004001000000000000000000FA9023' 141285645885181032 000020 MICHAEL 
THOMPSON  3476 
 x'06004001000000000000000000FA9023' 141285645885181032 000030 SALLY 
KWAN  4738

 

(3) MANAGER1 试图对同一数据页中的另一行执行更新，UPDATE 语句如下所示：

UPDATE EMPLOYEE SET 
  (FIRSTNME,LASTNAME,PHONENO) = ('MICHAEL','THOMPSON','9012') 
  WHERE RID_BIT(EMPLOYEE)=x'04004001000000000000000000FA9023' AND 
  ROW CHANGE TOKEN FOR EMPLOYEE=74904229642240

 

MANAGER1 执行更新失败，因为在比较标记时，由于所有行的 ROW CHANGE TOKEN 值发生了变化 ( 即使 MANAGER1 尝试进行更新的行实际上也没有发生变化 )，ROW CHANGE TOKEN谓词失败。如果向 EMPLOYEE 表添加行修改时间戳列，则误判场景中的UPDATE不会发生失败。

场景 5

EMPLOYEE 表创建时不包含 ROW CHANGE TIMESTAMP 列，但建表后添加了一个 ROW CHANGE TIMESTAMP 列。 MANAGER1 和 MANAGER2 访问该表。 MANAGER1 从中选择一行并尝试更新。然而，从他进行选择到更新期间，MANAGER2 更新了同一数据页中的其他数据 ( 不一定与 MANAGER1 处理的数据相同，而是位于另一行中 ) 。由于已经添加了 ROW CHANGE TIMESTAMP 列，所以如果对不同行进行更新，即使位于相同页面，更新也会成功。这个场景的主要步骤如下：

(1) MANAGER2 执行如下的 UPDATE 语句：

UPDATE EMPLOYEE SET

(FIRSTNME,LASTNAME,PHONENO) = ('CHRISTINE','HAAS','1092') 
  WHERE RID_BIT(EMPLOYEE)=x'04004001000000000000000000FA9023' AND 
  ROW CHANGE TOKEN FOR EMPLOYEE=74904229642240

 

(2) MANAGER2 执行 UPDATE，成功后的输出结果如下：

RID_BIT ROW CHANGE TOKEN EMPNO FIRSTNME LASTNAME  PHONENO ROW CHANGE TIMESTAMP 
 x'04004001000000000000000000FA9023' 141285673714388072 000010 CHRISTINE 
HAAS 10922008-12-20 18:22:25.593000
x'05004001000000000000000000FA9023' 74904229642240 000020 MICHAEL 
THOMPSON 3476  0001-01-01 00:00:00.000000 
 x'06004001000000000000000000FA9023' 74904229642240 000030 SALLY 
KWAN 4738  0001-01-01 00:00:00.000000

 

(3) MANAGER1 对同一数据页上的另一行执行下面的 UPDATE 语句：

UPDATE EMPLOYEE SET 
 (FIRSTNME,LASTNAME,PHONENO) = ('MICHAEL','THOMPSON','9012') 
 WHERE RID_BIT(EMPLOYEE)=x'04004001000000000000000000FA9023' AND 
 ROW CHANGE TOKEN FOR EMPLOYEE=74904229642240

 

(4) MANAGER1 执行 UPDATE 可以成功，成功后的输出结果如下：

RID_BIT ROW CHANGE TOKEN EMPNO FIRSTNME LASTNAME  PHONENO ROW CHANGE TIMESTAMP 
 x'04004001000000000000000000FA9023' 141285673714388072 000010 CHRISTINE 
HAAS 1092  2008-12-20 18:22:25.593000 
 x'05004001000000000000000000FA9023' 141285673726689984 000020 MICHAEL 
THOMPSON 9012  2008-12-20 18:22:37.312000 
 x'06004001000000000000000000FA9023' 74904229642240 000030 SALLY 
KWAN 4738  0001-01-01 00:00:00.000000

 

场景 6

EMPLOYEE 表具有一个 ROW CHANGE TIMESTAMP 列，并且只有 MANAGER1 访问该表。 MANAGER1 从中选择若干行并尝试更新它们。然而，从他进行选择到更新期间，表被离线重组。稍后，当 MANAGER1 尝试更新数据时，更新失败。行更新失败是因为执行REORG后 ROW CHANGE TIMESTAMP 列发生了变化。这个场景的主要步骤如下：

(1) 执行下面的命令重组 EMPLOYEE 表：

REORG TABLE EMPLOYEE

 

(2) 重组表 EMPLOYEE 后，EMPLOYEE 表的 ROW CHANGE TIMESTAMP 列发生变化，输出结果如下所示：

RID_BIT ROW CHANGE TOKEN EMPNO FIRSTNME LASTNAME  PHONENO ROW CHANGE TIMESTAMP 
 x'04004001000000000000000000FA9023' 141285781563232400 000010 CHRISTINE 
HAAS 3978  2008-12-21 11:29:30.250000 
 x'05004001000000000000000000FA9023' 141285781563232401 000020 MICHAEL 
THOMPSON 3476  2008-12-21 11:29:30.250001 
 x'06004001000000000000000000FA9023' 141285781563232402 000030 SALLY 
KWAN 4738  2008-12-21 11:29:30.250002

 

(3) MANAGER1 在运行REORG后尝试更新数据，SQL 语句如下：

UPDATE EMPLOYEE SET

(FIRSTNME,LASTNAME,PHONENO) = ('CHRISTINE','HAAS','1092') 
  WHERE RID_BIT(EMPLOYEE)=x'04004001000000000000000000FA9023' AND 
  ROW CHANGE TOKEN FOR EMPLOYEE=74904229642240

 

MANAGER1 执行更新失败，因为在 MANAGER1 执行SELECT 和 UPDATE期间，另一个任务对表执行了离线重组，因此在将执行SELECT时检索到的标记和当前标记进行比较时，ROW CHANGE TOKEN谓词失败。因此，UPDATE语句无法找到具有ROW CHANGE TOKEN的行，这些行是在发生REORG之前检索得到的。

乐观锁总结

为了避免在使用悲观锁定技术时可能引发的锁等待问题，乐观锁定技术最小化了给定资源对于其他事务的不可用时间。由于数据库管理器可以确定行发生修改的时间，它可以确保数据完整性，同时限制持有锁的时间。通过使用乐观并发控制，数据库管理器在完成读操作之后可以立即释放行或页锁。

DB2 V9.5 支持进行更简便和快捷的乐观锁定，而且避免了误判的发生。这些支持通过行标识符 (RID_BIT或RID) 内置函数、ROW CHANGE TOKEN表达式、基于时间的更新检测和隐式隐藏列实现。使用这种编程模型的应用程序可以从增强的乐观锁定特性要受益，并且能够进一步增强并发性。其实 DB2 V9.5 的乐观锁机制在某种程度上类似 Oracle 数据库的 SCN 实现机制，相信 DB2 在以后的版本中会对这种技术做进一步的改进，从而实现对某行操作的多版本读。

---

回页首

6.4  内部锁

我们在数据库中经常会看到一些内部锁，最常见的是内部 P 锁和内部 V 锁，下面来介绍这些内部锁。

6.4.1  内部方案锁 (Internal Plan Lock)

首先，我们先做一个例子，如果输出结果如下所示：

C:\>db2 +c update employee set salary=1234 where empno='000100' 
 DB20000I  SQL 命令成功完成。 -- 注：执行一条新的SQL语句，+C不提交，否则锁消失了
 C:\>db2 get snapshot for locks on sample----- 监控加锁情况
  数据库锁定快照
数据库名称  = SAMPLE 
数据库路径  = C:\DB2_01\NODE0000\SQL00001\ 
输入数据库别名  = SAMPLE 
挂起的锁定   = 3 
当前已连接的应用程序   = 2 
……………………………………………… . 节省篇幅 , 略…………………………………………………………………………
锁定名称  = 0x53514C4332473133B7F3CE3241 
锁定属性   = 0x00000000 
发行版标志   = 0x40000000 
锁定计数   = 1 
挂起计数   = 0 
锁定对象名   = 0 
对象类型=内部方案锁定(Internal Plan Lock)方式   = S 
……………………………………………… . 节省篇幅 , 略…………………………………………………………………………

 

大家知道无论数据库是在处理静态 SQL 还是动态 SQL，DB2 的相关组件都需要去访问程序包信息和相应的 SQL 查询访问计划。那么默认情况下，DB2 都会去给相应的 Package Cache 加内部 P 锁，以防止其他的什么操作将正在使用的 Package 删除，毫无疑问的是，这样的一个锁对性能多少会有影响，但影响是很小的。

DB2 的变量注册参数里面有个配置参数是 DB2_APM_PERFORMANCE 。如果这个值被设置为 ON，则会启用无包锁定方式。此方式允许全局查询高速缓存运行，而不必使用包锁定，这些锁定是内部系统锁定，可以保护高速缓存的包条目不被除去。无包锁定方式在一定程度上可以提高性能，但它不允许执行某些数据库操作。这些被禁止的操作可能包括：使包无效的操作、使包不起作用的操作、PRECOMPILE、BIND 和 REBIND 。所以，在这里只是让大家知道这个变量和了解程序包高速缓存的内部工作机制，但是不建议大家修改这个值。

在上面的例子中，我们可以看到有一个内部 P 锁。这是因为无论我们执行的是一条静态 SQL 语句还是一条动态 SQL 语句，在执行过程中，都要依次经过语法检查、语义检查、权限检查、查询重写、下推分析等阶段，最后优化器根据统计信息、配置参数、索引等情况为这条 SQL 语句生成一个成本最优的执行方案。该方案决定了使用什么扫描方式、访问表的顺序、表的连接方式和使用哪个索引。那么为了保证这条 SQL 语句在下次执行时不再重复上述步骤，数据库会把这条 SQL 语句的执行方案存放在程序包高速缓存中 (PCKCACHE_SZ) 中，在存放的时候它首先需要在程序包高速缓存中申请一块内存，然后把执行方案 INSERT 到该内存中。在申请时它要在这块内存中加一个锁以确保在 SQL 语句执行期间，这块内存不被别的应用删除 (DROP)，这就是内部 P 锁的作用 ( 在 Oracle 数据库中这种锁叫 LATCH 。 LATCH 是对内存加的锁，不是对数据库对象 ( 表、行 ) 加的锁，所以这种锁我们可以忽略 ) 。

6.4.2  内部 V 锁 (Internal Variation Lock)

当执行动态 SQL 语句时，对 SQL 的处理会在程序包的缓存中存储一个变量条目，为了保证这个变量条目在事务处理期间的有效性，DB2 会为它赋予一个内部 V 锁。下面我们看一个例子，在一个 B/S 结构中，前台运行 Java 应用，应用通过连接池发起动态 SQL 语句到数据库。假设某个时刻应用执行如下 SQL 语句：

update account set transdate= date(current timestamp) -? day

 

在这条 SQL 语句执行期间，我们执行“ get snapshot for locks ”监控发现如下锁的信息，

Application handle = 40 
 Application ID = 0A0B0A49.EB44.020821194232 
 Sequence number = 0001 
 Application name = db2jcc 
 Authorization ID = DB2INST1Application status = Compiling --注：应用程序正在编译
 Status change time = 08-21-2008 9:52:10.800363 
 Application code page = 819 
 Locks held = 2 
 Total wait time (ms) = 0 
 List Of  Locks 
 Lock Object Name = 0 
 Node number lock is held at = 0Object Type = Internal V Lock --注：内部V锁
 Tablespace Name = 
 Table Schema = 
 Table Name = 
 Mode = S 
 Status = Granted 
 Lock Escalation = NO 
 Lock Object Name = 0 
 Node number lock is held at = 0Object Type = Internal P Lock --注：内部P锁
 Tablespace Name = 
 Table Schema = 
 Table Name = 
 Mode = S 
 Status = Granted 
 Lock Escalation = NO 
 --------------------------------- 略 -----------------------------------

 

通过上面的锁的信息的监控，我们发现存在内部 V 锁。下面我们来讲解内部 V 锁的加锁机制。

在“ update account set transdate=date(current timestamp) -? day ”这条 SQL 语句传递给优化器编译 (COMPILING) 时，我们需要为这条 SQL 语句提供一个编译环境，这个编译环境包括把特殊的寄存器 (CURRENT STAMP) 变量转变成真正的值；表 ACCOUNT 没有模式名，需要使用 DEFAULT QUALIFIER 用作模式；我们使用了 DATE 函数，需要知道该函数的函数路径 (CURRENT FUNCTION PATH) ；我们使用了参数标记 ( ？ )，需要把参数标记转变成真正的值。上述的每一个变量都需要一个内部 V 锁来保证在该 SQL 执行期间，这些变量不被别的 DDL 语句修改或者被程序包缓存空间管理算法删除。

6.4.3  内部 S 锁

DB2 中的很多动作实际是作为一个事件来记录的，那么事件的序号的机制保证这些事件是按照一定的正确的顺序来运转。也就是一个特定的代理 AGENT 只能一次处理一个特定的事件。

在分区数据库环境中，这样的内部 S 锁通常是用来在执行无效的 DDL 语句的时候，初始化特定的临时表空间。

在 DB2 V7 到现在的版本中，这样的内部 S 锁实际是作用于临时表空间上的 S 锁。一个特定应用程序都会有自己的一个存在于特定临时表空间里面的临时表，这样的内部 S 锁作用其上，以控制其不被其他的操作删除了相应的临时表空间。内部 S 锁通常用在分区数据库环境中。

6.4.4  内部 C 锁

内部 C 锁 (Internal Catalog Cache Lock) 是为编目缓存 (CATALOGCACHE_SZ) 中某个条目而得到的一个锁，这样的锁用来在编目缓存中参照或者修改任意的条目的保证其完整性和一致性。 DB2 的编目缓存用来缓存系统编目表的信息，以加速 SQL 语句的编译速度和缩短 SQL 语句的编译过程。

从 DB2 组件设计的角度来看，当编目缓存的条目信息被锁的时候，相应的系统编目表的数据行也必须被锁。

所以我们的内部 C 锁的格式和常规的表的数据行的锁的格式是一样的，如下所示：

表空间id+数据库对象id+数据行id

只不过在这样的锁的名字中，会有个 'C' 用来表示这个锁是 Catalog Cache 锁。“ R ”用来表示这个锁是数据记录的锁。在一个事务的处理过程中，如果系统编目表的信息被更新，那么相应的编目缓存的条目的锁就是 X 锁，通常情况下，只会在相应的条目上加 S 锁。

DB2 内部保证在 SQL 处理的过程中只需要对编目缓存持有 S 锁。当 SQL 编译结束，相应的执行段被创建时，DB2 的这个相应的编目缓存条目的锁就会被释放。下面我们举一个内部 C 锁的例子，输出结果如下：

C:\>db2 +c create table t2(id int) 
 DB20000I  SQL 命令成功完成。

 

在另外一个窗口中使用“ get snapshot for locks ”监控信息的输出结果，如下所示 ( 部分 )：

--------------------------------- 省略 ----------------------------------- 
锁定名称  =0x0000050004431A00600E407D43  --
注：Tablespaceid+Tableid+RID锁定属性  = 0x00000000  
发行版标志  = 0x40000000 
锁定计数 = 255 
挂起计数  = 0 
锁定对象名  = 0 
对象类型  = 内部目录高速缓存锁定
方式  = X 

锁定名称  = 0x0100000000000000600E407D43 --
注：43代表Catalog Cache Lock锁定属性 = 0x00000000  -- 
注：52 代表是行锁 (row lock) 
发行版标志  = 0x40000000 
锁定计数  = 255 
挂起计数  = 0 
锁定对象名  = 0 
对象类型  = 内部目录高速缓存锁定
方式  = X 
 --------------------------------- 省略 ----------------------------------- 
锁定名称  = 0x0300000000000000000000004F 
锁定属性  = 0x00000000 
发行版标志  = 0x40000000 
锁定计数  = 2 
挂起计数  = 0 
锁定对象名  = 0 
对象类型  = 内部对象表锁定
方式  = IN 
锁定名称  = 0x03001600000000000000000054 
锁定属性  = 0x00000000 
发行版标志  = 0x40000000 
锁定计数 = 255 
挂起计数  = 0 
锁定对象名  = 22 
对象类型  = 表
表空间名  = IBMDB2SAMPLEREL 
方式  = Z 
 --------------------------------- 省略 -----------------------------------

 

6.4.5  其他内部锁

除了上述我们常见的一些内部锁外，DB2 数据库还存在以下几种内部锁：

Inplace Reorg Lock

我们会经常对表做 REORG ，REOGR 操作会将陈旧的 RID 进行更新。负责控制并发的扫描器会持有这个锁，直到该扫描器发现没有别的应用请求这个旧的 RID 。然后扫描器就释放这个锁，这时我们就可以对这个行做在线 REORG 了。当我们的表很大的时候，如果你不厌烦阅读数据库的快照监控信息的话，你会发现 REORG 总是在等待 Inplace Reorg Lock 。同时在在线重组期间，数据库还会产生一个内部改变锁定，来对重组的表进行碎片整理。下面我们举一个 Inplace Reorg Lock 的例子，输出结果如下：

C:\>db2 +c reorg table DB2ADMIN.dept1 inplace allow read access 
 DB20000I  REORG 命令成功完成。
 DB21024I  此命令为异步的，可能未能立即生效。

 

在另外一个窗口中使用而“ get snapshot for locks ”监控信息输出，结果如下 ( 部分 )：

锁定列表
锁定名称   = 0x0300050002000000000000006A 
锁定属性   = 0x00000000 
发行版标志   = 0x40000000 
锁定计数   = 1 
挂起计数   = 1 
锁定对象名   = 2 
对象类型=原地重组锁定(inplace reorg lock)表空间名   = IBMDB2SAMPLEREL 
表模式   = DB2ADMIN 
表名   = DEPT1 
方式   = IN 
锁定名称  = 0x03000500000000000000000074 -- 注：0300 代表表空间 id ； 0500 表示表 ID 
锁定属性   = 0x00000000 
发行版标志   = 0x40000000 
锁定计数   = 1 
挂起计数   = 1 
锁定对象名   = 5 
对象类型=内部表改变锁定
表空间名   = IBMDB2SAMPLEREL 
表模式   = DB2ADMIN 
表名   = DEPT1 
方式   = X 

锁定名称   = 0x03000500000000000000000054 
锁定属性   = 0x00000000 
发行版标志   = 0x40000000 
锁定计数   = 1 
挂起计数   = 1 
锁定对象名   = 5 
对象类型   = 表
表空间名   = IBMDB2SAMPLEREL 
表模式   = DB2ADMIN 
表名   = DEPT1 
方式   = S

 

内部 L 锁

Internal Long/LOBLock(L-lock) 负责对大对象进行处理。

内部联机备份锁 OLB-LOCK

当我们执行数据库在线备份时，会在表空间级别获得 OLB-LOCK 锁。它主要处理在线备份期间我们对数据库的更改。

内部 O-Lock

Internal-Object Table 主要负责提交 (COMMIT) 同步。

自动调整大小锁定

在 DB2 V9 中很多数据库配置参数默认都被设置为 AUTOMATIC，当我们执行某个特定操作时，DB2 会执行自动调整大小锁定。例如，当我们执行备份时，DB2 会自动调整 UTIL_HEAP_SZ 内存大小。

自动存储器锁定

在 DB2 V8.2.2 以后，如果我们建数据库时使用自动存储 (AUTOMATIC STORAGE)，那么在我们做某些操作时，数据库就会有自动存储锁定。

下面我们举一个例子，这个例子中包含了内部联机备份锁、自动调整大小锁和自动存储器锁三种内部锁。输出结果如下：

C:\>db2 backup db sample online- 注：sample 数据库为自动存储，DB2 版本是 V9.5

 

在另外一个窗口中使用“ get snapshot for locks ”监控信息输出，结果如下 ( 部分 )：

应用程序句柄  = 1125 
应用程序标识  = *LOCAL.DB2_01.081220102511 
序号  = 00001 
应用程序名  = db2bp.exe 
 CONNECT 授权标识  = DB2ADMIN 
应用程序状态   = 正在执行备份
状态更改时间  = 2008-12-20 18:24:55.783743 
应用程序代码页  = 1208 
挂起的锁定  = 18 
总计等待时间 ( 毫秒 )  = 0 
锁定列表
锁定名称  =0x0400000000000000000000006F- 注：表空间ID锁定属性  = 0x00000000 
发行版标志  = 0x40000000 
锁定计数  = 1 
挂起计数  = 0 
锁定对象名  = 4 
对象类型  = 内部联机备份锁定
表空间名  = SYSTOOLSPACE 
方式  = X 
锁定名称  = 0x0700000000000000000000005A 
锁定属性  = 0x00000000 
发行版标志  = 0x40000000 
锁定计数  = 1 
挂起计数  = 0 
锁定对象名  = 7 
对象类型  = 自动调整大小锁定
方式  = S 
锁定名称  = 0x0000000000000000000000007A 
锁定属性 = 0x00000000 
发行版标志  = 0x40000000 
锁定计数  = 1 
挂起计数  = 0 
锁定对象名  = 0 
对象类型  = 自动存储器锁定
方式  = S 
锁定名称  = 0x00000000000000000000000070 
锁定属性  = 0x00000000 
发行版标志  = 0x40000000 
锁定计数  = 1 
挂起计数  = 0 
锁定对象名  = 0 
对象类型  = 表空间
表空间名  = SYSCATSPACE 
方式 = IN

 

上述我们给大家讲解了 DB2 的一些内部锁，其实这些内部锁是 DB2 内部的一种自我保护机制，它类似 Oracle 数据库的 LATCH( 但也不完全一样，因为 Oracle 的 LATCH 只对内存加锁，而 DB2 的内部锁有些对内存加锁，有些对数据库对象加锁 ) 。这些内部锁在通常情况下我们可以不用关注，在这里之所以给大家讲解这么多，主要想要大家了解 DB2 数据库的内部结构。更重要的是，真相知道得越多，越有信心，知其然并且知其所以然。

---

回页首

6.5  设置锁相关的注册变量

DB2 从版本 8 以后先后引入了三个 DB2 注册变量—— DB2_EVALUNCOMMITTED、DB2_SKIPDELETED 和 DB2_SKIPINSERTED 来提高并发。

为什么要引入这三个变量呢？在 DB2 没有这三个变量前，如果一个用户正在更改 (UPDATE)、插入 (INSERT) 或删除 (DELETE) 一行，那么 DB2 会在这一行加上排它锁 (EXCLUSIVE)，别的用户不能读写，除非使用 UR 隔离级别。其实目前市场上除了 Oracle 外，Informix、SQL Server 和 Sybase 等数据库对锁的控制采用的都是这种方式。而 Oracle 数据库有回滚段 (ROLLBACK SEGMENT)，在 Oracle 数据库中对于 INSERT 操作，回滚段记录插入记录的 ROWID ；对于 UPDATE 操作，回滚段记录更新字段的旧值 (BEFORE IMAGE) ；对于 DELETE 操作，回滚段记录整行的数据。由于 Oracle 有了回滚段，可以实现多版本读，所以在用 Oracle 进行数据库开发时，很少关注锁的情况，因为大部分情况下应用都是可以读的，只不过有的时候大不了读以前的“ BEFORE IMAGE ”罢了。所以很多使用 Oracle 进行开发的用户在转向 DB2 开发时，都特别郁闷。而 DB2 为了改善应用程序并发性，从 DB2 V8 以后就陆续引入了这三个变量。这三个变量也是 DB2 客户提出要求 IBM 改进的，这种需求最初是由 SAP 提出的。这三个变量并不会改变锁的本质，只不过是了解它们的工作方式和机制可以使我们根据我们的业务逻辑来合理地设置调整以提高应用程序并发。

下面我们先通过一个例子来说明没有这三个变量之前的一些锁的情况。假设 T1 表中有 5 条记录，分别为 11、22、33、44、55 。其中第 2 条记录 22 被删除了，现在有一个 Session 1 要重新插入一条新的记录 22 ；同时第二个 Session 2 执行了“ db2 select * from t1 where id >11 and id<44 ”，正常的话它应该检索到 33 这条记录，但是由于现在插入的记录 22 也包含在这个谓词限定范围内，所以这个时候 Session 2 处于 LOCKWAIT 状态，输出结果如下：

Session 1   Session 2 
 db2 CONNECT TO SAMPLE db2 +c INSERT INTO t1 VALUES(22)  
 db2 CONNECT TO SAMPLE db2 SELECT * FROM t1 WHERE id >11 and id <44

 

我们通过监控看到的输出结果如图 6-6 所示。

图 6-6  逻辑输出结果

从 DB2 的角度来说好像这是合理的，但是从用户角度和业务逻辑来说希望这个时候能够读取到数据，那么怎么解决这个矛盾呢？下面我们来仔细讲解这三个变量。

6.5.1  DB2_EVALUNCOMMITTED

DB2 V8.1.4 版本中首次引入了 DB2_EVALUNCOMMITTED 这个 DB2 注册表变量。当它被启用 (=TRUE | ON | YES | 1) 时，它将修改 DB2 中只读查询的行为，以减少锁冲突，使之允许在索引扫描 ( 必须是 type-2 索引，对于 type-1 索引该特性不受支持 ) 或表访问时推迟锁，直到限定语句的所有谓词都是已知的。引入这个新的注册表变量是为了可选地提高一些应用程序的并发性，其实质是允许读扫描推迟或避免行锁，只能获得那些符合某个谓词的行上的锁，而并不是获得被检查的所有行上的锁。直到适合特定查询的一个数据记录成为已知。

注意：

在 DB2 V8.1 和更高版本中，所有新索引都创建为 type-2 索引。一个例外是当您在已具有 type-1 索引的表上添加索引时，仅在这种情况下，新索引也将是 type-1 索引。要了解一个表存在什么类型的索引，执行 INSPECT CHECK 命令。要将 type-1 索引转换为 type-2 索引，执行 REORG INDEXES CONVERT 命令。

在 DB2 V8.1.4 之前 ( 并且没有设置这个注册表变量 )，DB2 将执行保守式的锁：在验证行是否满足查询的排除谓词之前，它将锁定每个被访问的行。不管数据行是否被提交，以及根据语句的谓词它是否被排除，对于索引扫描和表访问都执行这样的锁定操作。下面我们举一个简单的例子，输出结果如下：

db2 create table t1(id int) 
 db2 insert into t1 values(11) 
 db2 commit

 

现在有两个 Session 分别发出了下面的 SQL 语句，输出结果如下：

Session 1   Session 2 
 db2 CONNECT TO SAMPLE db2 +c INSERT INTO t1 VALUES(22) 
 db2 CONNECT TO SAMPLE db2 SELECT * FROM t1

 

我们查看 Session 2 的状态，输出结果如图 6-7 所示。

图 6-7  查看 Session 的状态

第一条语句“ db2 +cinsert into table t1 values(22) ”阻塞所有其他 Session 的扫描，因为它持有行上的锁。如果第二个 Session 执行“ db2 select * form t1 ”那么它将被阻塞，直到事务 1 提交或回滚。但是我们假设第二条语句是“ db2 select * form t1 where id=11 ”。在此情况下，即使 Session 2 与列 ID=22 中的任何值 ( 还没有被提交 ) 都没有关系，它也仍将被阻塞，处于锁等待 (LOCK WAIT) 状态。在 DB2 中，默认情况下将发生这一系列的事件，因为默认的隔离级别是 CS 。这种隔离级别表明，一个查询访问的任何一行在游标定位到该行时都必须被锁定。在语句 1 释放它用于更新表 T1 的锁之前，语句 2 不能包含表 T1 第一行上的锁。如果 DB2 知道 ID=11 值不是语句 2 的数据请求的一部分 ( 换句话说，它在锁行之前计算了谓词 )，那么就可以避免阻塞，这是合情合理的，因为语句 2 将不会尝试锁定表中的第一行。

现在我们启用 DB2_EVALUNCOMMITTED 注册变量，该实例设置后需要重启实例才能生效，输出结果如下：

db2set DB2_EVALUNCOMMITTED=ON – i 
 db2stop force 
 db2start

 

在启用该实例后，再重复刚才的实验，我们发现第二条 SQL 语句“ select * from t1 where id=11 ”可以执行而不会被阻塞。所以 DB2_EVALUNCOMMITTED 注册变量的作用是判断该 SQL 谓词所扫描的行是否有锁，如果没有就可以检索到数据。

EVAL UATE UNCOMMITTED 第一次在 DB2 V8.1.4 中被引入时，带有以下限制：

• 该特性只能用于 CS 和 RS 隔离级别。
• SARGABLE 谓词必须存在，以便计算。
• 不适用于系统编目表上的扫描。
• 当扫描一个 MDC 表时，对于索引扫描，块锁可以推迟。然而，对于表扫描，块锁不会推迟。
• 被推迟的锁不会发生在正在执行在线表重组的表上。
• INDEX MANAGER 不可能在没有锁行的情况下回调 DATA MANAGER 来取数据记录。这意味着 ISCAN-FETCH 计划不能在 DATA MANAGER 中推迟锁 ( 唯一的例外是对一个 MDC 表的块索引，它的 INDEX EVALUATION 谓词是一个 ISCAN 计划 ) 。

DB2 V8.2.2 通过去掉 DB2 V8.1.4 中第一阶段的 EVAL UATE UNCOMMITTED，改进了这些缺点。 DB2 V8.2.2 引入了名为 DEFERISCANFETCH 的注册表变量，作为 DB2_EVALUNCOMMITTED 的新设置。启用该变量时，由该特性承担的锁将避免使用 ISCAN-FETCH 数据读取。

DB2_EVALUNCOMMITTED 注册变量影响 DB2 在游标稳定性 (CS) 和读稳定性 (RS) 隔离级别下的行锁机制。当你启用该功能时，DB2 可以对未提交的插入 (INSERT) 或者更新 (UPDATE) 数据进行谓词判断，如果未提交数据不符合这条语句的谓词判断条件，DB2 将不对未提交数据加锁。这样就避免了因为要对未提交数据加锁而引起的锁等待状态，提高了应用程序访问的并发性，同时 DB2 在无条件进行表扫描时会忽略删除的行数据 ( 不管是否提交 ) 。

这里分两种情况来看待。第一种情况：对于插入 (INSERT) 或者更新 (UPDATE)，如果未提交数据不符合这条语句的谓词判断条件，DB2 将不对未提交数据加锁。这样虽然比不上 Oracle 对于符合这条语句的谓词判断条件可以从回滚段里面读出 BEFORE IMAGE 那样做到写不阻止读，但是起码一定程度上缓解了锁的问题，不会因为插入 (INSERT) 或者更新 (UPDATE) 一条记录造成整个表都锁住。这点是个进步，我个人觉得也不会造成什么大的负面影响。

下面我们通过一个实验来说明这点，输出结果如下：

db2 create table t1(id int) 
 db2 insert into t1 values(11) 
 db2 insert into t1 values(22) 
 db2 commit 现在表中有两条记录 11 和 22

 

现在有两个 Session 发出了下面的 SQL 语句，输出结果如下：

Session 1   Session 2 
 db2 CONNECT TO SAMPLE  db2 +c delete from t1 where id=22 
 db2 CONNECT TO SAMPLE  db2 SELECT * FROM t1 WHERE id=11

 

在未设置 DB2_EVALUNCOMMITTED=ON 时，Session 2 肯定是处于锁等待 (LOCKWAIT) 状态的，现在我们设置了 DB2_EVALUNCOMMITTED=ON 后，我们来看看 Session 2 能否检索到数据，输出结果如下：

Session 1   Session 2 
 db2 CONNECT TO SAMPLE  db2 +c delete from t1 where id=22 
 db2 CONNECT TO SAMPLE  C:\>db2 select * from t1 
				 ID 
				 ----------- 
 11 
				  1 条记录已选择。

 

第二种情况是：通过上面的实验我们发现在启用 DB2_EVALUNCOMMITTED=ON 时，对于 DELETE 操作，DB2 在无条件进行表扫描时会忽略删除的行数据 ( 不管是否提交 ) 。个人觉得有很大的问题，通过上面的这个测试，一个会话删除一条记录并没有提交，另外一个会话查询的时候已经没有这条记录了，这相当于 UR 隔离级别。这样显然是不符合业务要求的。与其这样还不如锁住。所以启用 DB2_EVALUNCOMMITTED=ON 时，对于删除操作应该注意多多测试。

现在我们在 T1 上创建一个 type-2 的索引，然后再来做刚才的那个实验：

db2 create index index11 on t1(id)

 

在两个命令行窗口中分别发出下面的 SQL 语句：

Session 1    Session 2 
 db2 CONNECT TO SAMPLE   id=22  db2 CONNECT TO SAMPLE 
 db2 create index index11 on t1(id)  db2 +c delete from t1 where 
 C:\>db2 select * from t1   --lockwait 挂起

 

我们在另外一个窗口查看 Session 2 的状态，发现 Session 2 处于 LOCKWAIT 状态，输出结果如图 6-8 所示。

图 6-8  查看 Session 2 的状态

当您的 DB2 环境中启用了 EVAL UATE UNCOMMITTED 行为时，您应该清楚，谓词计算可能发生在未提交的数据上。而且，在表扫描访问中，被删除行会被无条件忽略，而对于 type-2 索引扫描，被删除的键不会被忽略 ( 除非您还设置了 DB2_SKIPDELETED 注册表变量，DB2_SKIPDELETED 变量我们稍后介绍 ) 。如果您要在 DB2 环境中单独设置 DB2_SKIPDELETED 注册表变量，DB2 将允许在表扫描访问时无条件地忽略被删除行，并忽略 type-2 索引扫描访问的伪删除索引键。

6.5.2  DB2_SKIPDELETED

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

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

db2set DB2_SKIPDELETED=ON – i 
 db2stop force 
 db2start

 

设置生效后，我们接着做刚才的实验，输出结果如下：

Session 1    Session 2 
 db2 CONNECT TO    SAMPLE db2 CONNECT TO SAMPLE 
 db2 create index index11 on t1(id)   C:\>db2 select * from t1 
 ---DB2 V8 后创建的索引默认都是 type-2  ID 
 db2 +c delete from t1 where id=22  ----------- 
    11 
						 1 条记录已选择。

 

我们可以看到在设置 DB2_SKIPDELETED=ON 后，即使 T1 表上有 type-2 索引，扫描的时候也仍然忽略这个删除的行。但是这个在用的时候一定要结合业务逻辑使用，因为这种情况下等同于“脏读”，所以一定多测试。

6.5.3  DB2_SKIPINSERTED

虽然当一个行由于一个未提交的 INSERT 而被锁的时候，这种行为是正确的，但是有些情况下应用程序的所有者希望 DB2 忽略正在等待提交的被插入的行，就好像它不存在一样 ( 由于未提交 INSERT 的提交版本现在根本没有行，所以这是可能的 ) 。例如，银行在下午 5 点左右想统计今天的业务量，这时只是想了解大概的业务量而不是精确的，这种情况下如果启用该变量，那么遗漏一两笔业务是可以接受的。

在 DB2 V8.2.2 中，DB2_SKIPINSERTED=OFF 是默认设置。这使得 DB2 的行为和预期的一样：扫描器一直等到 INSERT 事务提交或回滚，然后返回数据。是否打开该变量取决于您的应用程序以及和业务逻辑相关的数据完整性的特征，这样可能合适，也可能不合适。例如，考虑一个涉及两个应用程序的业务流程，这两个应用程序使用相同的一个表来交换业务信息，例如一个信用评级应用程序和一个信用评分引擎。应用程序 A 基于一个 Web 表单将数据插入数据库，应用程序 B 读这些数据。为了加快信用审批的速度，由于候选者通过信用评级应用程序来进行表单转移，信息块通过表单中的“ STEPS ”被发送到应用程序 B( 通过公共的表 ) 。当候选者完成信用评级应用程序流程中的每个步骤时，信息被发送。在这个环境中，数据必须由第二个应用程序按照表中给出的顺序来处理，以便当接下来要读的行要被应用程序 A 插入时，应用程序 B 必须等待，直到 INSERT 被提交。

如果设置 DB2_SKIPINSERTED=ON，DB2 将把未提交的 INSERT( 只适于 CS 和 RS 隔离级别 ) 看作它们还没有被插入。该特性增加了并发性，同时又不牺牲隔离语义。 DB2 为扫描器实现了这种能力，通过锁属性和锁请求的反馈，使其忽略未提交的插入行，而不是等待。

下面我们来看看设置 DB2_SKIPINSERTED 变量前后的对比，输出结果如下：

Session 1    Session 2 
 db2 CONNECT TO SAMPLE    db2 CONNECT TO SAMPLE 
 db2 create index index11 on t1(id)  C:\>db2 select * from t1 
 db2 reorg indexes all for table   -- 挂起，处于 lockwait 状态
 DB2ADMIN.t1 convert 
 db2 +c insert into t1 values(33)

 

通过监控发现 Session 2 处于 LOCKWAIT 状态，输出结果如图 6-9 所示。

图 6-9  查看 Session 2 的状态

如果这种情况下 Session 2 希望能够跳过未提交 INSERT 的数据而得到数据，那么可以设置 DB2_SKIPINSERTED 注册变量，输出结果如下：

db2set DB2_SKIPINSERTED=ON – i 
 db2stop force 
 db2start

 

在设置 DB2_SKIPINSERTED=ON 后，再重复刚才的实验，我们发现这个时候，Session 2 可以读到数据，输出结果如下：

Session 1    Session 2 
 db2 CONNECT TO SAMPLE   C:\>db2 select * from t1 
 db2 create index index11 on t1(id)   ID 
 db2 reorg indexes all for table   ----------- 
 DB2ADMIN.t1 convert   11 
 db2 +c insert into t1 values(33)  22 
      2 条记录已选择。

 

DB2_EVALUNCOMMITTED、DB2_SKIPDELETED 和 DB2_SKIPINSERTED 总结

总的来说这 3 个注册变量会影响到并发性。通过合理设置这些变量可以改善并发性，但是也会影响到应用程序的行为。建议在 DB2 开发设计的初期启用这些注册变量，从而在实现并发性增强后执行全面测试中的所有单元测试。

通过对这三个注册变量进行设置，可以提高并发，但是我们使用的时候一定要结合自己的业务逻辑使用。根据个人的经验，我建议使用 DB2_EVALUNCOMMITTED=ON 和 DB2_SKIPINSERTED=ON，对于 DB2_SKIPDELETED 变量，使用的时候一定要充分地测试，因为它等同于使用 UR 隔离级别 ( 注：虽然 DB2_SKIPINSERTED=ON 也等同于 UR，但是没有插入的数据反正也没有插入，读不到数据在业务上是可以接受的 ) 。

总之，有了这三个变量，我们多了一份选择总比没有强。目前这三个变量都是实例级别的变量，如果能做成 SQL 级或应用级的就更好了。期待 DB2 能够在后续的版本中继续对锁并发作出改进。

---

回页首

6.6  本章小结

数据库中锁的技术是数据库系统中的一个核心技术，它决定了数据库中访问数据的最基本的方式和手段。所以对于 DBA 来说，掌握数据库的锁技术是掌握数据库技术时必不可少的内容。

本章我们介绍了 DB2 中关于锁的一些高级内容。锁对数据库的并发影响很大，掌握锁的这些相关的技术对于我们合理地设计数据库和应用程序以保证提高数据库的并发性能具有重大意义。锁同样对数据的一致性也有很大的影响，使用不同的隔离级别策略来保证应用程序在访问 DB2 中数据的过程中所必须的数据一致性需求也显得很重要。虽然这两者都很重要，但遗憾的是数据库中数据的并发性和一致性始终是一对矛盾，如何调和这两者的关系在应用设计和数据库设计中就显得极为重要。首先考虑的因素是应用的需求，然后要考虑的就是数据库能够提供的技术手段。 DB2 的锁技术现在越来越灵活、越来越强大，通过使用本章介绍的这些高级技术或者新的锁功能，基本上都能够实现我们各种各样的数据访问中的锁需求。那么相对来说的更灵活且不易确定的就是应用中对锁的需求到底是怎样的？这就需要系统设计人员能够洞悉应用的需求，从需求定义中释出合理的锁需求，这同样也是我们要掌握的技术。本章还详细介绍了 DB2 提供的实现上述两种需求的各种高级技术和新功能，而且通过大量的例子为我们演示了这些技术的使用方法和技巧。这都为更好地掌握 DB2 的锁技术铺平了道路。

转：http://www.ibm.com/developerworks/cn/data/books/db2advan/6/