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