DB2隔离级别和锁 - 图文(3)

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

应用程序类型 读写事务 只读事务

需要高数据稳定性 RS RR 或 RS

不需要高数据稳定性 CS 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 32-bit 64-bit 64-bit HP_UX

LRB Size 48 bytes 64 bytes 80 bytes

First Transaction to Lock 96 bytes 128 bytes 160 bytes

Subsequent Locks 48 bytes 64 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 释放掉该锁。

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说教育文库DB2隔离级别和锁 - 图文(3)在线全文阅读。