(由于这个问题不太好提问,我就改一下风格,直接陈述观点算了)
由于这篇文章只是作比较,所以读者最好对隔离级别已经有一定的了解。
另外不得不提一下题外话,在写这篇文章前曾见在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,一个没留意可能就出问题了。这未必就是一个问题,但应该是一个隐患。
- 大小: 2.8 KB
- 大小: 6.6 KB
- 大小: 8.6 KB
- 大小: 10.9 KB
- 大小: 13.9 KB
- 大小: 13.4 KB
- 大小: 119.6 KB
分享到:
相关推荐
操作手册(GB8567——88).doc 测试分析报告(GB8567——88).doc 测试计划(GB8567——88).doc 概要设计说明书(GB8567——88).doc 开发进度月报(GB8567——88).doc 可行性研究报告(GB8567——88).doc 模块...
读书摘要——Qt.4 读书摘要——Qt.4 读书摘要——Qt.4 读书摘要——Qt.4 读书摘要——Qt.4 (比较好!!!)
计算机————硬件.doc
人教版初一数学上册导学案3.2解一元一次方程——移项.2解一元一次方程——移项.pdf
—— ——... ——分类网络基础知识及模型优化方法 —— ——... ——检测模型基础知识 —— ——... ——分割模型基础知识 —— ——... ——深度学习案例应用 —— ——... ——模型选择和算法部署 —— ——... ...
python项目——Word助手.zip python项目——Word助手.zip python项目——Word助手.zip python项目——Word助手.zip python项目——Word助手.zip python项目——Word助手.zip python项目——Word助手.zip python项目...
C语言项目——贪吃蛇游戏.zip C语言项目——贪吃蛇游戏.zip C语言项目——贪吃蛇游戏.zip C语言项目——贪吃蛇游戏.zip C语言项目——贪吃蛇游戏.zip C语言项目——贪吃蛇游戏.zip C语言项目——贪吃蛇游戏.zip ...
C语言项目——超级万年历.zip C语言项目——超级万年历.zip C语言项目——超级万年历.zip C语言项目——超级万年历.zip C语言项目——超级万年历.zip C语言项目——超级万年历.zip C语言项目——超级万年历.zip ...
python项目——微信机器人.zip python项目——微信机器人.zip python项目——微信机器人.zip python项目——微信机器人.zip python项目——微信机器人.zip python项目——微信机器人.zip python项目——微信机器人....
python项目——玛丽冒险.zip python项目——玛丽冒险.zip python项目——玛丽冒险.zip python项目——玛丽冒险.zip python项目——玛丽冒险.zip python项目——玛丽冒险.zip python项目——玛丽冒险.zip python项目...
python项目——超级画板.zip python项目——超级画板.zip python项目——超级画板.zip python项目——超级画板.zip python项目——超级画板.zip python项目——超级画板.zip python项目——超级画板.zip python项目...
贷款股东决议——范文..doc
董事变更决议——范文..doc
工会议案范例——范文..doc
工会团员总结——推荐..doc
Javaweb实训——EasyBuy.zipJavaweb实训——EasyBuy.zipJavaweb实训——EasyBuy.zipJavaweb实训——EasyBuy.zipJavaweb实训——EasyBuy.zipJavaweb实训——EasyBuy.zipJavaweb实训——EasyBuy.zipJavaweb实训——...
中国人元旦吃什么——范文..doc
代理商协议书——范文..doc
贷款担保协议书——范文..doc