MySQL(InnoDB剖析):43---事务之（事务隔离级别：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE）
原创
©著作权归作者所有：来自51CTO博客作者董哥的黑板报的原创作品，请联系作者获取转载授权，否则将追究法律责任
- ISO和ANIS SQL标准指定了4种事务隔离级别的标准，但是很少有数据库厂商循环这些标准。例如Oracle数据库就不支持READ UNCOMMITTED和REPEATABLE READ
- SQL标准定义的四个隔离级别为：
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
一、总体概述
- READ UNCOMMITTED称为浏览访问，仅仅针对事务而言的。READ COMMITTED称为游标问题。REPEATABLE READ是的隔离，没有幻读的保护。SERIALIZABLE称为隔离，或的隔离
默认隔离级别
- SQL和SQL2标准的默认事务隔离级别是SERIALIZABLE
- InnoDB的默认隔离级别是REPEATABLE READ，但是与标准SQL不同的是，InnoDB存储引擎在REPEATABLE READ隔离级别下，使用Next-Key Lock锁的算法，因此避免幻读的产生。这与其他数据库系统（例如SQL Server）不同
- Next-Key Lock锁的算法
- 所以说，InnoDB在默认的REPEATABLE READ隔离级别下已经能完全保证事务的隔离性要求，即达到SQL标准的SERIALIZABLE隔离级别
- 隔离级别越低，事务请求的锁越少或保持锁的时间就越短。这也是为什么大多数数据库默认的事务隔离级别是READ COMMITTED
隔离级别与性能
- 据了解。大部分的用户质疑SERIALIZABLE隔离级别带来的性能问题，但是根据Jim Gray在《Transaction Processing》一书中指出，两者的开销是一样的，甚至SERIALIZABLE可能更优！！！因此在InnoBD中选择REPEATABLE READ的事务隔离级别并不会有任何性能的损失
- 同样的，即使使用READ COMMITTED的隔离级别，用户也不会得到性能的大幅度提升
二、事务隔离级别语法格式
- 使用下面的命令来设置当前会话或全局的事务隔离级别：
- 设置事务的默认隔离级别，可以在MySQL的配置文件中添加如下的内容
select @@global.tx_isolation\G
三、SERIALIZABLE
- 在SERIALIZABLE隔离级别下，InnoDB会对每个SELECT语句后自动加上LOCK IN SHARE MODE，即为每个读取操作加一个共享锁
- 因此在这个事务隔离级别下，读占用了锁，对一致性的非锁定读不再支持
- 一致性的非锁定读
- 这是，事务隔离级别SERIALIZABLE符合数据库理论上的要求，即事务是well-formed的，并且是two-phrased的
- 因为InnoDB在REPEATABLE READ隔离级别下就可以达到的隔离，因此一般不在本地事务中使用SERIALIZABLE隔离级别。SERIALIZABLE的事务隔离级别主要用于InnoDB存储引擎的分布式事务
四、READ COMMITTED
- 在READ COMMITTED的事务隔离级别下，除了唯一性的约束检查以及外键约束的检查需要gap lock，InnoDB不会使用gap lock的所算法
-
使用这个事务隔离级别的注意事项
- 首先，在MySQL 5.1中，READ COMMITTED事务隔离级别默认只能工作在replication（复制）二进制日志为ROW的格式下。如果二进制日志工作在默认的STATEMENT下，则会出现下面的错误：
主从数据不一致
- 在MySQL 5.0版本之前，不支持ROW格式的二进制日志时，也许有人知道通过将参数innodb_locks_unsafe_for_binlog设置为1可以在二进制日志为STATEMENT下使用READ COMMITTED的事务隔离级别：
- 接着在master上开启一个会话A执行如下事务，并且不需要提交
- 在master上开启另一个会话B，执行下面的事务并且提交：
- 可以看到，数据产生了不一致。导致两个问题发生的原因有两点：
- 在READ COMMITTED事务隔离级别下，事务没有使用gap lock进行锁定，因此用户在会话B可以在小于等于5的范围内插入一条记录
- STATEMENT格式记录的是master上产生的SQL语句，因此在master服务器上执行的顺序为先删后插，但是在STATEMENT格式中记录的确实先插后删，逻辑顺序上产生了不一致
- 要避免主从不一致的问题，只需解决上述问题中的一个就能保证数据的同步了。如使用READ REPEATABLE的事务隔离级别可以避免上述第一种情况的发生，也就避免了master和slave数据不一致的问题
