自斟自饮——7. 浅谈隔离级别

（由于这个问题不太好提问，我就改一下风格，直接陈述观点算了）

由于这篇文章只是作比较，所以读者最好对隔离级别已经有一定的了解。

另外不得不提一下题外话，在写这篇文章前曾见在javaeye上search了一下，发现很多日志要么都在大抛书包，要么就是转别人的文章，要么就根本没有实地测试过。其实最实在的方法是看官方文档和自己亲自尝试一下。

InnoDB、Oracle、pgsql都支持“轻量级”的行级锁（row level lock），都支持MVCC（Multiple Version Concurrency Control），都支持读写（reader & writer）不互相堵塞（block），但在隔离级别（isolation level）上却有不少相异的地方。

隔离级别其实是一门很重要的话题，情况给多线程并发非常类似。如果处理不好，在并发不高、处理过程不复杂的过程，出错几率不大；但并发上来了、处理也复杂之后，就非常容易出错。而且这种错误都很难复现、很难查，往往茫茫没头绪。隔离级别涉及并发性和完整性，简单来说就是两个字：重要。目前之所以得不到应有的重视，个人觉得跟我们现在大多数的数据库应用都是一些非常简单的CUID有关，有些甚至连Transaction都懒得打开，隔离级别自然就显得多余。（笔者目前所在的团队正在维护一个连Transaction都没有购买的Informix数据库应用，杯具啊）

我们回忆一下SQL标准里的隔离级别，一共有四个等级：read uncommitted、read committed、repeatable read、serializable。按照传统的数据库理论，这四个等级的隔离程度是越来越高，相应地，并发行也越来越低。

但请注意，我这里特意写上“传统”这两个字。因为对于具备MVCC的数据库，并发性往往不会因为隔离级别的提高而相应降低（当然也会有其它的代价）。

每一个数据库对于这四种隔离级别都有不同程度的支持，请看下表：



注意：

• Oracle另外有一种read only的隔离级别，但这里不作说明，因为read only相当于serializable，区别只是不能更新数据而已；
• pgsql对于read only的支持更加夸张，对于每一个隔离级别都有两种选项，read write和read only，默认是read write，这里我对read only也不再作说明；
• 在pgsql你会发现我注明了“语法支持”，意思是说仅仅在语法上支持，但实际上是另外一回事。官方文档这样写：The SQL standard defines two additional levels, READ UNCOMMITTED and REPEATABLE READ. In PostgreSQL READ UNCOMMITTED is treated as READ COMMITTED, while REPEATABLE READ is treated as SERIALIZABLE. 之所以有这种古怪的事情，因为pgsql要兼顾两样事情：MVCC和功能全面。MVCC的特性注定了read uncommitted已经没有任何意义，而repeatable read也没有太大必要，但pgsql号称功能全面，所以语法上还是要support一下的。所以后面对pgsql的测试我会忽略这两种级别。
• 从上表你会发现Oracle和pgsql在隔离级别非常类似。其实这也是pgsql的一个重要特征：锁&隔离级别跟oracle基本一样。配合PL/pgSQL，所以oracle移植到pgsql相对来说比较容易。

下面我们对每一类隔离级别作相应的说明：

read uncommitted

我们会发现InnoDB竟然会支持read uncommitted，注意，这里不是语法支持，而是事实上支持。官方文档是这样写：SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a “dirty read.” Otherwise, this isolation level works like READ COMMITTED.

我们可以看到InnoDB这是为了对早期版本的兼容。其实也可以理解，毕竟InnoDB只是MySQL的一个数据引擎，其余的引擎例如MyISAM并不支持MVCC，要达到select不被block，那就需要支持read uncommitted。但我们一定要有一个清晰的认识：在MVCC下，read uncommitted已经没有任何意义。

这里我有一个疑问，究竟这个read uncommitted在innodb是不是语句级别的读一致性？换句话说，是不是在查询语句启动的时候，搜索结果已经确定了，类似read committed的语句级别的读一致性？还是实时性的？这里我做一个简单的测试。

在实验前先要准备数据：

create table abc (tid integer, tname char(20));
insert into abc values (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');

drop procedure if exists ray_test1;
delimiter //
CREATE PROCEDURE ray_test1(in sleep_sec integer)
BEGIN
    declare _tid    integer;
    declare _tname  char(20);
    declare x cursor for select * from abc order by tid;
    
    open x;
    fetch x into _tid, _tname;
    select _tid , _tname;
    
    select 'sleep ...';
    select sleep(sleep_sec);
    select 'end sleep';
    
    fetch x into _tid, _tname;
    select _tid , _tname; 
    fetch x into _tid, _tname;
    select _tid , _tname; 
    fetch x into _tid, _tname;
    select _tid , _tname;
    close x;
END
//
delimiter ;

这里我先新建一个表和存储过程。这个存储过程很简单，先建立一个cursor，读出第一条记录，然后sleep几秒钟，然后再读取剩余记录。在这中间sleep的几秒里，我尝试在另外一个session里修改其它记录的数值，然后看读出来的效果如何。

+------+--------+
| _tid | _tname |
+------+--------+
|    2 | x      |
+------+--------+
1 row in set (10.02 sec)

+------+--------+
| _tid | _tname |
+------+--------+
|    3 | c      |
+------+--------+
1 row in set (10.03 sec)

+------+--------+
| _tid | _tname |
+------+--------+
|    4 | d      |
+------+--------+
1 row in set (10.05 sec)

Query OK, 0 rows affected (10.05 sec)

大家可以看到，对于tid = 2，tname的值已经改变了，的确是read uncommitted的效果，但对于tid = 3 和 4，是查询启动前的值。所以我们可以得出结论：innodb对read uncommitted也是保持读一致性。

由于我手头上没有SQL Server、Sybase之类，所以不能验证这类“传统”数据库会不会对read uncommitted保证读一致性，不过我相信结果多数是否定的。

read committed

这个隔离级别我觉得没有什么好说的。相对其它三个级别，read committed在这三个数据库表现得出奇地一致。

InnoDB文档这样写：A somewhat Oracle-like isolation level with respect to consistent (nonlocking) reads: Each consistent read, even within the same transaction, sets and reads its own fresh snapshot.

很明显，和Oracle基本一样。pgsql就更加不用说了。活脱脱一个oracle翻版。

repeatable read、serializable

这里我把这两个隔离级别放在一齐，因为oracle和pgsql都不支持repeatable read，而innodb的这两个隔离级别非常类似。

注意：innodb的默认隔离级别是repeatable read，相应地，oracle和pgsql是read committed。

oracle和pgsql的serializable这里我只做一个简单的说明，更加详细的可以去看Tom的书。oracle和pgsql的serializable是一种不加锁的“伪串行”隔离方式。之所以说是“伪串行”因为它不会block其它session的操作（传统的串行会加锁，保证只有自己操作数据）。实际上serializable保证了事务级别的读一致性，也就是说，通过这种隔离级别，查询的结果已经在事务启动的时候确定。注意跟read committed区别，read committed是语句级别的读一致性。

这种“伪串行”隔离级别好处当然是保证了并发性，但坏处也很明显，如果在事务启动之后，期间有其它session修改了一些数据并且提交，本session若再去修改这些已经被修改过的数据，在oracle就会报“ORA-08177: can't serialize access for this transaction”，在pgsql就会报“ERROR:  could not serialize access due to concurrent update”。遇到这种错误，程序只能rollback并重启事务再次处理。这是鱼与熊掌不可兼得的情况。

Oracle和pgsql认为，通常你的程序运行在serializable下，并发修改的情况应该很少，所以出现这种ORA-08177的情况应该不会很多，是可以容忍的情况。另外，如果数据库这个时候有别的session提交更新比较频繁，还容易报出“ORA-01555: Snapshot is too old”这种经典错误。所以在oracle和pgsql使用serializable的时候最好不要有其他session的频繁提交更新。

innodb的这两种隔离级别跟oracle、pgsql完全不同。首先是repeatable read，跟SQL标准一样，它保证了从第一次读数据后的读一致性。但，什么事情都有“但”（拾人牙慧）。它仅仅保证了读一致性，不会保证写一致性（我不知道有没有“写一致性”这名词，如果没有就算我自创吧）。简单来说，在这种隔离级别，读（reader）和写（writer）看到的数据不尽相同，这是一种很古怪的现象，真的很古怪。

注意我这里的用词——古怪。因为我习惯的时oracle，所以看到这种隔离级别自然会觉得不正常。但正如Tom所说：“将应用从数据库A 移植到数据库B 时，我时常遇到这种问题：应用在数据库A 上原本无懈可击，到了数据库B 上却不能工作，或者表现得很离奇。看到这种情况，我们的第一个想法往往是，数据库B 是一个“不好的”数据库。而真正的原因其实是数据库B 的工作方式完全不同。没有哪个数据库是错的或“不好的”，它们只是有所不同而已。应当了解并理解它们如何工作，这对于处理这些问题有很大的帮助。将应用从Oracle 移植到SQL Server 时，也会暴露SQL Server 的阻塞读和死锁问题，换句话说，不论从哪个方向移植都可能存在问题。”（译文）

文字是枯燥的，我这里做个实验。先准备数据：

create table abc (tid integer, tname char(20));
insert into abc values (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');

这里我列出我得到的结果供参考：




从上面的结果我们可以看出，innodb在repeatable read级别下读写可以说是完全分离，你看不到我，我看不到你。俗话说得好，远在天边，近在眼前。

我们可以这样总结：innodb在repeatable read下，如果需要修改的数据没有被其它session修改，那再次查询可以得到修改后的结果；但如果需要修改的数据已经被其它session修改过，那再次查询还是只能得到修改前的数据状态。注意了，这里所说的都是commit前，如果commit了，当然都看到最后的结果。

innodb的repeatable read和oracle的serializable的区别可以归纳为：



我们可以看到，innodb在更新数据的时候是牺牲了一部分“数据完整性”来避免oracle的ORA-08177的错误。这里的“数据完整性”指的是查询跟更新时需要面对可能不一致的结果集。

innodb的serializable其实是repeatable read的加锁版本，凡是查询过的数据都加上锁，其余的session就不可以再次修改数据（但可以查询）。这一点明显跟oracle无论是读一致性时机和并发性等都完全不同。

后话

上面的比较并不是想说明哪一个对、哪一个错，只是指明了其中的不同之处，我们平时使用的时候要时刻留意这些异同，尽量避免为自己的程序留下“小尾巴”。或许有人说为了保持一致，我们在使用innodb的时候不如把默认的隔离级别设为read committed。或许这是一个不错的主意，但这里往往会带来新的沟通上的隐患，例如新来的团队成员习惯了innodb传统的repeatable read，一个没留意可能就出问题了。这未必就是一个问题，但应该是一个隐患。

评论

3 楼 liangguanhui 2011-05-11  

lijjjjil 写道

有问题吧?  read uncommitted咋可能不变呢？你的游标已将查询结果保存起来了当然不变
在session1里直接查询就看得到脏数据
set session transaction isolation level read uncommitted;
select * from abc;
select sleep(10);
select * from abc;

你要明白read uncommitted的scope，这只是语句级别，所以
select * from abc;
select sleep(10);
select * from abc;
你这里其实已经启动了两个查询，搜索出来的结果当然有可能不一样。

我之所以用游标+sleep是因为不想用大数据量，你可以用大数据量（例如1000w条）模拟一下这个过程。session1是select * from tbl，然后session2 update一下最后一条数据，看看结果如何。

2 楼 lijjjjil 2011-05-10  

有问题吧?  read uncommitted咋可能不变呢？你的游标已将查询结果保存起来了当然不变
在session1里直接查询就看得到脏数据
set session transaction isolation level read uncommitted;
select * from abc;
select sleep(10);
select * from abc;

1 楼 aabcc 2010-12-28  

引用

（封面女郎暂缺）

失落ing...