转载：http://liangguanhui.iteye.com/blog/853885
（由于这个问题不太好提问，我就改一下风格，直接陈述观点算了）
由于这篇文章只是作比较，所以读者最好对隔离级别已经有一定的了解。
另外不得不提一下题外话，在写这篇文章前曾见在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，一个没留意可能就出问题了。这未必就是一个问题，但应该是一个隐患。
扫一扫
177
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
