Chinaunix

标题: 针对数据库事务隔离级别的讨论，期待你的参与！ [打印本页]

作者: lyhabc 时间: 2015-08-30 18:29
标题: 针对数据库事务隔离级别的讨论，期待你的参与！
获奖名单已公布：http://bbs.chinaunix.net/thread-4189360-1-1.html

话题背景
当前主流数据库都会有事务隔离级别，保证事务的隔离性（isolation），隔离性是数据库事务ACID四个属性之一
而ISO和ANIS SQL标准制订了四种事务隔离级别标准
SQL-92标准定义的四个隔离级别：
READ UNCOMMITTED （缩写RU）
READ COMMITTED （缩写RC）
REPEATABLE READ （缩写RR）
SERIALIZABLE （缩写S）

关于四个隔离级别的说明，摘录自《百度百科》
未授权读取
也称为读未提交（Read Uncommitted）：允许脏读取，但不允许更新丢失。如果一个事务已经开始写数据，则另外一个事务则不允许同时进行写操作，但允许其他事务读此行数据。

授权读取
也称为读提交（Read Committed）：允许不可重复读取，但不允许脏读取。这可以通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。

可重复读取
可重复读取（Repeatable Read）：禁止不可重复读取和脏读取，但是有时可能出现幻影数据（幻读）。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。

序列化（Serializable）
序列化：也叫串行化，提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

SQL和SQL92 标准的默认事务隔离级别是SERIALIZABLE ，也是最高的事务隔离级别，可以保证事务间完全隔离。

我这里针对当前主流的三大数据库进行讨论：Oracle，MySQL，SQL Server
1、Oracle不支持READ UNCOMMITTED和REPEATABLE READ ，大家觉得有道理吗？
个人觉得，因为Oracle不支持READ UNCOMMITTED和REPEATABLE READ事务隔离级别，所以不能拿Oracle做大学的数据库教材。本人当年读大学的时候，老师就以Oracle来做教材，这样的话，学生根本无法知道READ UNCOMMITTED和REPEATABLE READ事务隔离级别有什么优缺点（例如不知道有不可重复读取/幻读这回事）。而且我觉得因为Oracle不支持READ UNCOMMITTED和REPEATABLE READ事务隔离级别，所以我觉得Oracle是一个不完整的数据库

SQL Server和MySQL都实现了四个隔离级别，而拿SQL Server做大学教材是比较好的，因为SQL Server的功能是完整的，SQL Server2000及之前版本都已经实现了四个隔离级别，SQL语句和SQL语法都相当完整。而在MySQL5.1中，RC隔离级别只能用在row格式的binlog的复制环境下，非复制环境不能用，而binlog要row格式，MySQL5.0版本之前不支持row格式的binlog

还有一点，SQL Server的开发团队是由图书《Transaction Processing》事务处理一书作者 Jim Gray 带头开发的，大家可以百度一下Jim Gray 这个老头的来历。所以功能和完整性上面SQL Server还是有保障的

2、默认事务隔离级别应该使用哪一个才是标准
读过一些资料，发现SQL和SQL92 标准的默认事务隔离级别是SERIALIZABLE ，也就是说，你做出一个数据库出来，应该用SERIALIZABLE 作为默认的事务隔离级别。但是某些数据库厂商为了并发度，而不遵循SQL和SQL92 标准

有些人评击Oracle和SQL Server不应该使用READ COMMITTED隔离级别，因为会产生幻读现象，但是个人觉得影响不大，因为用户可以随时修改数据库的隔离级别。就像你买了一部手机，手机该有的功能都有，一个功能才有的设置参数都有，而厂家一般都使用了认为是最优的参数来设置手机完成出厂设置，当然用户也可以在以后修改这些手机参数，这是出厂设置，但是人家普遍一个功能有三个参数让用户选择，而你的手机只有两个参数给用户选择，那算不算一部好手机？

3、事务隔离级别越高，性能越差
这个在MySQL使用RR作为默认隔离级别而引起的，SQL和SQL92 标准的默认事务隔离级别是SERIALIZABLE，在Jim Gray 写的《Transaction Processing》一书中也指出，RC隔离级别和S隔离级别两者的开销几乎是一样的，甚至S会更好一些。所以MySQL使用RR作为默认隔离级别，性能不会有太大损耗，同样选择RC作为默认隔离级别，性能也不会有非常非常大的提升

4、说到事务隔离级别一定要提到MVCC（Multi-Version Concurrency Control 多版本并发控制，也有人叫一致性非锁定读）
MVCC其实是数据库对于事务的一种读写分离的方案，目的是提高数据库的并发。MVCC虽然是好，但是也带来了一定的副作用，例如，事务更新丢失，在MySQL里面将事务隔离级别设置为SERIALIZABLE 之后，MVCC就不再起作用，而SQL Server的做法是增加MVCC数据库事务隔离级别来支持MVCC，默认并不会开启MVCC，除非人为设置整个数据库的隔离级别为MVCC隔离级别，也就是说人为接受MVCC的副作用。

上面就是本人对于事务隔离级别的见解，期待你的想法，大家共同探讨。

讨论话题
1、Oracle不支持READ UNCOMMITTED和REPEATABLE READ ，大家觉得有道理吗？
2、默认事务隔离级别，大家认为使用四种隔离级别哪一种隔离级别作为默认事务隔离级别是最好的，好在哪，其他的隔离级别差在哪？
3、事务隔离级别越高，性能越差这个观点虽然在《Transaction Processing》书中说性能相差不大，但是实际上在数据库实现上持有锁的时间是增加了的，持有锁的时间增加意味着并发的降低，大家是认同《Transaction Processing》书中的观点吗，请说出认同或者不认同的理由。
4、数据库是否需要学SQL Server提供一个打开和关闭MVCC的开关，让用户自己决定是否使用MVCC，进而让用户来承担MVCC所带来的副作用的风险，而不是默认只能用MVCC。

讨论时间
2015-08-31至2015-09-30

活动奖励
活动结束后，将选取2名讨论最精彩的童鞋，每人赠送便携背包一个+互动出版网购书券一张，面值30元，以作为奖励。

评选3名最佳参与奖，每人赠送互动出版网购书券一张，面值20元，以作为奖励。

购书券使用说明：购书券为互动出版网的直减通用券，使用无限制，在支付的时候选择代金券支付即可。有效期为一年。
购书券使用链接：http://www.china-pub.com/

作者: Shell_HAT 时间: 2015-08-30 20:54
MVCC，那么？

作者: lyhabc 时间: 2015-08-30 21:30
@Shell_HAT
已经重新编辑了，准备叫萌妹纸发上讨论帖

作者: chinafenghao 时间: 2015-08-31 15:22
板凳,好活动,要支持。

作者: nail78 时间: 2015-09-01 13:19
1、Oracle不支持READ UNCOMMITTED和REPEATABLE READ ，大家觉得有道理吗？
oracle 默认的隔离级别应该是Read Committed，不可重复读和幻读这样的并发问题，可以在需要的场合可以由应用程序加悲观锁或乐观锁来控制。

2、默认事务隔离级别，大家认为使用四种隔离级别哪一种隔离级别作为默认事务隔离级别是最好的，好在哪，其他的隔离级别差在哪？
把Read Committed作为默认隔离级别，既能避免脏读取，并发性能也还不错。尽管可能会导致不可重复读和幻读这样的并发问题，在需要的场合可以由应用程序加悲观锁或乐观锁来控制。

3、事务隔离级别越高，性能越差这个观点虽然在《Transaction Processing》书中说性能相差不大，但是实际上在数据库实现上持有锁的时间是增加了的，持有锁的时间增加意味着并发的降低，大家是认同《Transaction Processing》书中的观点吗，请说出认同或者不认同的理由。
不认同这本书的观点，隔离级别越高，越能保证数据的完整性和一致性，开销也越大，对并发性能影响也越大。

作者: lyhabc 时间: 2015-09-01 14:03
@nail78
oracle 默认的隔离级别是Read Committed
谢谢回复

作者: will009 时间: 2015-09-01 14:43
本帖最后由 will009 于 2015-09-01 14:47 编辑

1、Oracle不支持READ UNCOMMITTED和REPEATABLE READ ，大家觉得有道理吗？
我觉得是有道理的。
READ UNCOMMITTED
这种隔离级别允许脏读（也就是可以读取到用户未提交的数据），支持这种隔离级别的数据库主要是为了支持非阻塞读，但是oracle默认支持非阻塞读，所以oracle里面不支持这种隔离级别。下面举一个例子：
行       帐号       帐户金额（元）
1       123       500.00
2       456       240.25
..       ...       ......
342023 987       100.00
假设某一家银行要统计所有账号总共有多少金额。事务A负责统计，事务A从第一行开始读取。假设读取到100行的时候，事务B从账号123转了400元到账户987（事务B还未提交），支持脏读的数据库当事务A读取到342023行的时候，就会得到500元，从而多加了400元。

REPEATABLE READ
这种隔离级别不支持脏读，不支持可重复读，支持幻想读。主要是为了得到一致性的答案与防止丢失更新。
1）得到一致性答案
在oracle里面这个通过多版本机制得到了实现，但是在其他的数据库需要通过加锁机制进行控制，就以上一个例子为例，怎样才能统计出正确的总金额呢，事务A在读取每一行的时候，给每一行加上共享读锁，这样当事务B执行从账号123转400元到账户987的时候。先是操作第一行将账户123的金额由500修改成100，但是第一行已经被事务A锁定，于是等待，这样事务A能够读取到正确的数据。但是如果事务B执行的操作是从账户987转50元到账户123的时候，事务B先操作第342023行，发现没有被锁定，于是锁定将金额由100修改成50，然后操作第一行，发现锁定了于是等待。而事务A读取到342023行的时候，发现这一行已经被事务B锁定于是等待，这样就陷入了死锁。
2）丢失更新
在采用共享读锁的数据库中，这种隔离级别可以防止丢失更新，比如事务1先读取了第A行然后修改了这一行的C列（其他列也修改了只是值还和以前一样，因为程序员都是整行的更新）。这个时候事务2想也想修改A行的时候会被阻塞，防止事务1的更新被覆盖。

2、默认事务隔离级别，大家认为使用四种隔离级别哪一种隔离级别作为默认事务隔离级别是最好的，好在哪，其他的隔离级别差在哪？
默认事务隔离级别，我觉得Read Commited作为默认事务隔离级别是最好的。事务管理是数据库处理的核心，数据库既要保证用户能并发地执行事务，还要保证数据库的一致性。事务隔离程度越高，并发性越差、性能越低；事务隔离程度越低，并发性越强、性能越高。
Read Commited :读已提交数据。这是Oracle的默认隔离级别，查询语句只能看到已提交的数据。隔离的已提交读能保证在访问特定的行时，该行的数据保持不变。所以，这个级别可以防止脏读和更新丢失。
数据库并发的事务会带来以下四个问题：脏读，更新丢失，不可重复读，幻读。
Read Uncommited （读未提交数据）虽然并发性最高，但会出现脏读,不可重复读,幻读，更新丢失。
Repeatable Read （可重复读）会出现幻读。
Serializable （可串行化）隔离级别最高，但数据库的并发性受到了很大的限制。对DML操作的数据会放置一个写锁，别的涉及DML的操作不得不等待锁解除。可以避免上面的四个问题，但在现实中不可用。

3、事务隔离级别越高，性能越差这个观点虽然在《Transaction Processing》书中说性能相差不大，但是实际上在数据库实现上持有锁的时间是增加了的，持有锁的时间增加意味着并发的降低，大家是认同《Transaction Processing》书中的观点吗，请说出认同或者不认同的理由。
不认同。事务隔离级别越高，越能保证数据的一致性，但对并发性能影响越大，一致性和高性能必须有所取舍或折中。一般情况下，多数应用程序可以选择将数据库的隔离级别设置为读已提交，这样可以避免脏读，也可以得到不错的并发性能。尽管这个隔离级别会导致不可重复读、幻读，但这种个别场合应用程序可以通过主动加锁进行并发控制。
事务隔离级别是并发控制的整体解决方案，其实际上是综合利用各种类型的锁和行版本控制，来解决并发问题。锁是数据库并发控制的内部机制，是基础。当然，数据库同时还会利用行版本控制（SQL Server 2005 及以上）来进行并发控制；在数据库内部还使用闩（latch），互斥（mutex）等机制处理内部资源（如，缓存）的并发访问。
对用户来说，只有当事务隔离级别无法解决一些并发问题和需求时，才有必要在语句中手动设置锁。不适当的设置锁，可能会导致严重的阻塞和死锁。建议，只有在完全了解锁机制的情况下，才可以在语句中手动设置锁，否则应该使用事务隔离级别。

4、数据库是否需要学SQL Server提供一个打开和关闭MVCC的开关，让用户自己决定是否使用MVCC，进而让用户来承担MVCC所带来的副作用的风险，而不是默认只能用MVCC。
可以参考吧
Oracle、MySQL和SQL Server都有MVCC，只是实现方式不大相同，用户可以综合考虑整体性能，折衷取舍。关系数据库管理系统使用MVCC(Multiversion Concurrency Control多版本并发控制)来避免写操作堵塞读操作的并发问题，MVCC也就是通过使用数据的多个版本保证并发读写不冲突的一种机制，不同的数据库有不同的实现，这也是数据库系统让人头疼的地方，关系数据库表面看上去很简单方便，使用标准的SQL语句操作让人很放心，但是随着系统规模增加，并发用户增加，数据库会出现性能降低的现象，这时我们可能需要从外
部的微调进入到内部原理的深入研究，而每个数据库内部实现并发的原理都是不同的，如果我们拥有多个不同的数据库，那么需要不同的调校方法。
MVCC的两种不同实现方式
　　第一种实现方式是将数据记录的多个版本保存在数据库中，当这些不同版本数据不再需要时，垃圾收集器回收这些记录。这个方式被PostgreSQL和Firebird/Interbase采用，SQL Server使用的类似机制，所不同的是旧版本数据不是保存在数据库中，而保存在不同于主数据库的另外一个数据库tempdb中。
　　第二种实现方式只在数据库保存最新版本的数据，但是会在使用undo时动态重构旧版本数据，这种方式被Oracle和MySQL/InnoDB使用。

作者: lyhabc 时间: 2015-09-01 20:40
本帖最后由 lyhabc 于 2015-09-01 20:54 编辑

@will009

oracle默认支持MVCC
得到一致性答案，不是有MVCC，怎麽会死锁呢，读取snapshot就可以啦

Read Commited :读已提交数据。这是Oracle的默认隔离级别，查询语句只能看到已提交的数据。隔离的已提交读能保证在访问特定的行时，该行的数据保持不变。所以，这个级别可以防止脏读和更新丢失

可重复读：共享锁一直保持到事务被终止，更新丢失不会在可重复读级别上发生，但是容易造成死锁
可重复读：还是会发生幻读

可重复读隔离级别才可以解决更新丢失和可重复读问题，但是解决不了幻读
RC隔离级别只是在读取到一行数据之后在上面锁一下，读取完毕之后马上解锁，一解锁，其他事务依然有机会可以读取到原先那个事务的那行数据，所以更新丢失依然存在
可重复读隔离级别会在事务读取的行数据上一直上锁到整个事务结束，例如一个事务锁定十行数据，这十行数据不会被其他事务读取到，这样不会让其他事务有机可乘，这样可以解决更新丢失
和重复读问题，因为其他事务不可能有机可乘

幻读，只有串行隔离级别才能解决
同一个事务内select 和修改语句的where条件范围相同，那么有可能delete了的数据select依然可以读取到（前提同一个事务内）
delete tb1 where xx=xx
select tb1 where xx=xx

where 筛选器是相同的时候，就有可能产生幻读
-------------------------------------------------------------------
sqlserver里的更新丢失
举个例子描述这个场景：

T1事务发起一个修改，读取原库存是10，需求修改库存减1，原库存应该变成9，因为是READ_COMMITTED_SNAPSHOT隔离级别，所以数据库会在tempdb里生成一个快照，但是事务未提交，在这时发起了第二事务T2，也来修改库存，因为看到事务T1未提交，所以他不能获取未提交事务修改的值9（如果获取9就是脏读了），而是他获取的是最后提交版本的库存为10，而正巧T2未提交前，T1先提交了，实际库存应该变9而不是10，但T2事务获取库存值是10，假设T2的需求是减库2，那么最后T2提交后，会覆盖T1事务所做的修改，库存变成了8（我们实际期望的是10-1-2=7），这样就造成了逻辑混乱。

对于开启了MVCC的sqlserver来说，更新丢失是不会存在的，就像上面的例子，这样就会带来很大的副作用，数据库里的数据其实已经不一致了

解决方法是
1、使用具有原子操作性的merge语句
2、不使用READ_COMMITTED_SNAPSHOT隔离级别这种MVCC隔离级别

作者: lyhabc 时间: 2015-09-01 20:53
MVCC最可怕的就是发生举例描述的这个场景，更新丢失不可怕，只是第二个线程更新失败，但是MVCC。。。

作者: lyhabc 时间: 2015-09-02 00:10
更正一下
二种实现方式只在数据库保存最新版本的数据，但是会在使用undo时动态重构旧版本数据，这种方式被MySQL/InnoDB使用。
如果mysql使用共享表空间，那么我不觉得属于主数据库内

作者: will009 时间: 2015-09-02 09:37
@lyhabc
学习了，我理解得还不够深刻

作者: 第一最寂寞i 时间: 2015-09-09 10:40
安全与性能都是相对的，你的安全性做的越好，性能就会变差，高效的性能，方便数据的读取，那么你得安全设置就不是那么好，Oracle不支持READ UNCOMMITTED和REPEATABLE READ，肯定出于的目的就是secure，毕竟是专业的DB公司，对安全性的要求肯定要达到专业水平，说白了，就是宁愿牺牲时间或者效率也要保证安全。我还是比较支付ORACLE的观念

作者: lyhabc 时间: 2015-09-09 10:51
@第一最寂寞i
支持和不支持有什么所谓呢，让用户选择就可以了

作者: 第一最寂寞i 时间: 2015-09-09 14:07

回复 13# lyhabc
涉及到大库的安全用户的选择性基本没有

作者: liguangyi 时间: 2015-09-10 14:32
讨论话题
1、Oracle不支持READ UNCOMMITTED和REPEATABLE READ ，大家觉得有道理吗？

觉得可以接受。
READ UNCOMMITTED 会出现脏读，日常应用中极少使用这种方式。
REPEATABLE READ 每次读都是该事务起始时的数据状态快照，虽然可以重复读但数据可能已不是系统中最新的，实际也是脏读的一种，如果要作以此更新，仍然会导致其它事务的更新丢失。

2、默认事务隔离级别，大家认为使用四种隔离级别哪一种隔离级别作为默认事务隔离级别是最好的，好在哪，其他的隔离级别差在哪？

通常情况下，READ COMMITTED 就很好了。REPEATABLE READ 只有在长时间只读事务中，例如数据分析OLAP之类有优势，但通常很少在线上直接作这个吧。其它的或者脏读，或者性能不可接受。

3、事务隔离级别越高，性能越差这个观点虽然在《Transaction Processing》书中说性能相差不大，但是实际上在数据库实现上持有锁的时间是增加了的，持有锁的时间增加意味着并发的降低，大家是认同《Transaction Processing》书中的观点吗，请说出认同或者不认同的理由。

隔离级别越高，性能必然越差。如果只采用SERIALIZABLE 级别，对于大型数据库就是作死。慢事务会拖死其它事务的。数据库本来就是高cpu负载高IO的应用，现在通常的4CPU32核心都有时cpu满载，如果顺序依次执行事务，不能充分利用多核优势，其性能肯定无法接受。

4、数据库是否需要学SQL Server提供一个打开和关闭MVCC的开关，让用户自己决定是否使用MVCC，进而让用户来承担MVCC所带来的副作用的风险，而不是默认只能用MVCC。

没必要。SQL Server新版本没用过，但以前的版本对sql标准的兼容很差。如果学习数据库，可以考虑postgresql。MVCC本身并没有什么副作用，是用户使用的方法不对。
防止更新覆盖有多种方法。比如1、最原始的锁表锁库，2、记录上加排他写锁 select for update，3、记录中设置timestamp字段，update时检查时间戳是否变化，变化了则重新读，4、应用本身将有冲突的更新串行化处理。

作者: lyhabc 时间: 2015-09-10 21:05
@liguangyi
请问pgsql您用得很熟吗

作者: gta 时间: 2015-09-10 22:43
markmarkmarkmarkmark

作者: 西道古边 时间: 2015-09-20 09:55
路过了～～～～～～～～～～～··

作者: lyhabc 时间: 2015-09-22 21:53
@柴银健
谢谢！

欢迎光临 Chinaunix (http://bbs.chinaunix.net/)