事务隔离级别(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)。
注意,Auto Commit缺省是ON,即自动提交。使用下面的命令来查看设置:
DB2 LIST COMMAND OPTIONS
打开CLP console,并把Auto Commit设置为OFF:
DB2 +C
这时可以看到Auto Commit 已经变为OFF了:
LIST COMMAND OPTIONS
DB2使用一个叫做“CURRENT ISOLATION”的special register来记录session的隔离级别。查看当前隔离级别有两种方法:
VALUES CURRENT ISOLATION
或
SELECT CURRENT ISOLATION FROM SYSIBM.SYSDUMMY1
奇怪的是结果是空的(以空格填充)。我们知道缺省隔离级别是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设置: