数据库隔离级别的讨论

yxh81823

 
<div class="Section0">事务隔离级别（Transaction Isolation Level）

丁凯

2010年8月30日

在数据库的事务并发过程中，如果不加以控制，会出现数据不一致的现象，比如典型的脏读（dirty read）问题。DBMS使用“锁”机制来对事务做并发控制，如S锁，X锁等。在更高层面上，DBMS中有四种隔离级别（Isolation Level）。随着隔离级别的升高，并发控制愈发严格，当然副作用就是并发度会愈发下降。因此，我们要选择适当的锁/隔离级别，即在能满足需求的前提下，尽量提高并发度。
下表列出这四种隔离级别，以及DB2对这四种隔离级别的叫法，：

ANSI SQL	DB2
读未提交（READUNCOMMITTED）	UR（Uncommitted Read）
读提交（READCOMMITED）	CS（Cursor Stability）
可重复读（REPEATABLEREAD）	RS（Read Stability）
序列化（SERIALIZABLE）	RR（Repeatable Read）

 
下表列出了事务并发时经常遇到的问题。那么在各种隔离级别下，会出现哪些问题，又能防止哪些问题呢？我们将会通过实验来得出结论，填充这张表格。

事务并发时常见问题 隔离级别 (ISOLATION LEVEL)	丢失更新	读脏数据	不可重复读	幻象记录(phantom)
读未提交（READUNCOMMITTED）
读提交（READCOMMITED）
可重复读（REPEATABLEREAD）
序列化（SERIALIZABLE）

 
下面使用DB2 CLP（Command Line Processor）来做实验。
准备工作

打开Command Window（开始 -> 所有程序 -> IBM DB2 -> DB2COPY1 (default)-> Command Line Tools -> Command Window）。
<img alt="" width="553" height="196">
<!--EndFragment--> 
注意，Auto Commit缺省是ON，即自动提交。使用下面的命令来查看设置：
DB2 LIST COMMAND OPTIONS
 
<img alt="" width="553" height="475">
 
打开CLP console，并把Auto Commit设置为OFF：
DB2 +C
 
<img alt="" width="553" height="316">
 
这时可以看到Auto Commit 已经变为OFF了：
LIST COMMAND OPTIONS
 
<img alt="" width="553" height="462">
 
DB2使用一个叫做“CURRENT ISOLATION”的special register来记录session的隔离级别。查看当前隔离级别有两种方法：
VALUES CURRENT ISOLATION
 
或
SELECT CURRENT ISOLATION FROM SYSIBM.SYSDUMMY1
 
<img alt="" width="553" height="143">
 
奇怪的是结果是空的（以空格填充）。我们知道缺省隔离级别是CS，这里就不验证了。
设置隔离级别命令如下（此命令不需要显式COMMIT）：
SET CURRENT ISOLATION TO UR/CS/RS/RR
 
由于该测试需要并发事务，所以我们要用同样方法，再打开一个CLP窗口。以下称为session1
和session2。
把两个session都连接到DINGTEST数据库，并建一张表TA如下：

AC1	AC2
1	1
2
3
4
5

 
至此准备工作完成，我们开始实验。
注：每次实验完以后别忘记清理环境，即把session commit/rollback。
实验1

测试在“UR”隔离级别下是否会产生“丢失更新”的问题。
方法：两个session并发更新同一条记录。
 
STEP 0：把session1和session2都设置为UR隔离级别：
SET CURRENT ISOLATION TO UR
 
STEP 1：在session1中做更新操作，但不提交：
UPDATE TA SET AC2 = 2 WHERE AC1 = 1
 
STEP 2：在session2中对同一条记录做更新操作：
UPDATE TA SET AC2 = 3 WHERE AC1 = 1
 
Session2被block住……
 
STEP 3：在session1中做commit/rollback操作，则session2会立刻继续执行。
 
结论：在“UR”隔离级别下不会产生“丢失更新”的问题。
实验2

测试在“UR”隔离级别下是否会产生“读脏数据”的问题。
方法：在session2中查询session1中更改但未提交的数据。
 
STEP 0：把两个session都设置为UR隔离级别：
SET CURRENT ISOLATION TO UR
 
STEP 1：在session1中做更新操作，但不提交：
UPDATE TA SET AC2 = 4 WHERE AC1 = 1
 
STEP 2：在session2中查询该记录：
SELECT * FROM TA WHERE AC1 = 1
 
Session2中立即返回查询结果，该记录值为session1更新后的值，这实际是一个脏数据，session1有可能最终会rollback。
 
STEP 3：在session1中做rollback操作。
 
STEP 4：在session2中再次查询该记录。
SELECT * FROM TA WHERE AC1 = 1
 
该记录值为session1更新前的值，因为session1已经rollback了，第一次读到的是一个脏数据。
 
结论：在“UR”隔离级别下会产生“读脏数据”的问题
实验3

测试在“CS”隔离级别下是否会产生“读脏数据”的问题。
方法：在session2中查询session1中更改但未提交的数据。
 
STEP 0：把两个session都设置为CS隔离级别：
SET CURRENT ISOLATION TO CS
 
STEP 1：在session1中做更新操作，但不提交：
UPDATE TA SET AC2 = 5 WHERE AC1 = 1
 
STEP 2：在session2中查询该记录：
SELECT * FROM TA WHERE AC1 = 1
 
Session2被block住……
 
STEP 3：在session1中做commit/rollback操作，则session2会立刻继续执行。
 
结论：在“CS”隔离级别下不会产生“读脏数据”的问题。
实验4

测试在“CS”隔离级别下会是否会产生“不可重复读”的问题。
方法：尝试在session1的两次查询之间修改其查询的记录。
 
STEP 0：把两个session都设置为CS隔离级别：
SET CURRENT ISOLATION TO CS
 
STEP 1：在session1中做查询操作，但不提交：
SELECT * FROM TA WHERE AC1 = 1
 
假定查询结果中AC2为“4”。
 
STEP 2：在session2中更新此记录，并提交（不提交的话，session1就无法再查询该记录了）：
UPDATE TA SET AC2 = 5 WHERE AC1 = 1
 
COMMIT
 
STEP 3：在session1中再次查询该记录：
SELECT * FROM TA WHERE AC1 = 1
 
这次查询结果中，AC2为“5”。这就产生了在同一个事务中，两次查询的数据不一致的问题。
 
结论：在“CS”隔离级别下会产生“不可重复读”的问题
实验5

测试在“RS”隔离级别下是否会产生“不可重复读”的问题。
方法：尝试在session1的两次查询之间修改其查询的记录。
 
STEP 0：把两个session都设置为RS隔离级别：
SET CURRENT ISOLATION TO RS
 
STEP 1：在session1中做查询操作，但不提交：
SELECT * FROM TA WHERE AC1 = 1
 
假定查询结果中AC2为5
 
STEP 2：在session2中更新此记录：
UPDATE TA SET AC2 = 6 WHERE AC1 = 1
 
Session2被block住……
 
STEP 3：在session1中再次查询该记录：
SELECT * FROM TA WHERE AC1 = 1
 
这次查询结果中，AC2仍然为5。这表明在RS隔离级别下，在事务中，查询的记录都被lock住了，以保证同一事务中，多次查询的数据都一致。
 
STEP 4：在session1中做commit/rollback操作，则session2立即继续执行。
 
结论：在“RS”隔离级别下不会产生“不可重复读”的问题。
实验6

测试在“RS”隔离级别下是否会产生“幻象记录”的问题。
方法：尝试在session1的两次查询之间添加符合其查询条件的记录。
 
STEP 0：把两个session都设置为RS隔离级别：
SET CURRENT ISOLATION TO RS
 
STEP 1：在session1中做查询操作，但不提交：
SELECT * FROM TA WHERE AC1 > 0 AND AC1 < 10
 
假定查询结果有5条记录。
 
STEP 2：在session2中INSERT一条记录，并提交：
INSERT INTO TA (AC1, AC2) VALUES (6, 1)
 
COMMIT
 
STEP 3：在session1中做同样的查询操作：
SELECT * FROM TA WHERE AC1 > 0 AND AC1 < 10
 
这次查询结果中，变成了6条记录。这就产生了在同一个事务中，两次查询的数据不一致的问题。虽然第一次查到的记录都被lock住了不会被改变，但若在两次查询之间，其它session若新插入了记录，则在第二次查询中也会被查到。该记录被称为幻象（phantom）记录。
 
结论：在“RS”隔离级别下会产生“幻象记录”的问题
实验7

测试在“RR”隔离级别下是否会产生“幻象记录”的问题。
方法：尝试在session1的两次查询之间添加符合其查询条件的记录。
 
STEP 0：把两个session都设置为RR隔离级别：
SET CURRENT ISOLATION TO RR
 
STEP 1：在session1中做查询操作，但不提交：
SELECT * FROM TA WHERE AC1 > 0 AND AC1 < 10
 
假定查询结果有6条记录。
 
STEP 2：在session2中INSERT一条记录：
INSERT INTO TA (AC1, AC2) VALUES (7, 1)
 
Session2被block住了……
 
STEP 3：在session1中做同样的查询操作：
SELECT * FROM TA WHERE AC1 > 0 AND AC1 < 10
 
查询结果仍为6条记录，且数据和第一次查询保持一致。
 
STEP 4：在session1中做commit/rollback操作
 
则session2立即继续执行。
 
结论：在“RR”隔离级别下不会产生“幻象记录”的问题。
 
结论

基于以上实验，我们可以完成下表的填充，即在每种隔离级别下，对事务并发时常见问题的控制。NO表示会出现该问题，YES表示不会出现该问题。

事务并发时常见问题 隔离级别 (ISOLATION LEVEL)	丢失更新	读脏数据	不可重复读	幻象记录(phantom)
读未提交（READUNCOMMITTED）	NO	YES	YES	YES
读提交（READCOMMITED）	NO	NO	YES	YES
可重复读（REPEATABLEREAD）	NO	NO	NO	YES
序列化（SERIALIZABLE）	NO	NO	NO	NO

 
 
 
BTW：在Command Editor下修改auto commit设置：
<img alt="" width="554" height="393">