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

创建一个存储过程，保存为 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 \ 再打开一个新的窗口，得到在 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 \ C:\\> db2 \ 我们使用 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 \ 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 \ for read only with rr\ 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 提供了简单的试探方法，以帮助您为应用程序选择初始隔离级别。首先考虑表中列示的方法，并参阅先前对影响各隔离级别因素的讨论，可能会找到另一个更适合的隔离级别。

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