MySQL的隔离级别

数据库操作中可能会存在的问题

脏读：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两 次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不 可重复读。(例如，一个编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始读取不可重复。如果 只有在作者全部完成编写后编辑人员才可以读取文档，则可以避免该问题。)

幻读：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。 同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象 发生了幻觉一样。

 

MySQL的隔离级别：

   
   

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

从上到下的级别越来越高，数据越安全，但是性能越来越低。

可重复读(Repeatable read)是MySql默认的隔离级别。但是互联网项目中，一般会把MySQL的隔离级别改成已提交读(Read committed)。

 

首先，在Oracle，SqlServer中都是选择读已提交(Read Commited)作为默认的隔离级别，为什么Mysql不选择读已提交(Read Commited)作为默认隔离级别，而选择可重复读(Repeatable Read)作为默认的隔离级别呢？

这个是有历史原因的，在MySQL进行主从复制的时候，是通过binlog文件进行主从复制的，binlog有三种格式：

statement:记录的是修改SQL语句；

row：记录的是每行实际数据的变更；

mixed：statement和row模式的混合

 

MySQL在5.0的版本之前，只支持使用statement的格式进行主从复制，而这种格式在选择读已提交(Read Commited)作为默认隔离级别是有Bug的，因此MySQL默认将读已提交(Read Commited)作为默认的隔离级别。

 

为什么项目中不能使用读未提交(Read uncommitted)和可串行化(Serializable)这两个隔离级别：

采用读未提交(Read uncommitted)会导致脏读的情况产生，正常的情况应该要等待别的事务提交后再能读取；

采用可串行化(Serializable)，每次读操作都会加锁，快照读失效，一般是使用mysql自带分布式事务功能时才使用该隔离级别。

 

可重复读和已提交读的比较，可重复读(Repeatable read)，简称为RR,已提交读(Read committed)称为RC;

 

假设表结构如下

 CREATE TABLE `test` (
`id` int(11) NOT NULL,
`color` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB

数据如下

+----+-------+
| id | color |
+----+-------+
|  1 |  red  |
|  2 | white |
|  5 |  red  |
|  7 | white |
+----+-------+

 

 

一，在RR隔离级别下，存在间隙锁，导致出现死锁的几率比RC大的多

 

执行以下语句：

select * from test where id <3 for update;

在RR隔离级别下，存在间隙锁，可以锁住(2,5)这个间隙，防止其他事务插入数据；在RC隔离级别下，不存在间隙锁，其他事务是可以插入数据。

ps:在RC隔离级别下并不是不会出现死锁，只是出现几率比RR低而已。

 

二，在RR隔离级别下，条件列未命中索引会锁表，而在RC隔离级别下，只锁行

执行以下语句：

update test set color = 'blue' where color = 'red';

在RC隔离级别下，其先走聚簇索引，进行全部扫描。加锁如下：

 

但在实际中，MySQL做了优化，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录放锁。
实际加锁如下：

 

然而，在RR隔离级别下，走聚簇索引，进行全部扫描，最后会将整个表锁上，如下所示：

 

三，在RC隔离级别下，半一致性读(semi-consistent)特性增加了update操作的并发性

在5.1.15的时候，innodb引入了一个概念叫做“semi-consistent”，减少了更新同一行记录时的冲突，减少锁等待。
所谓半一致性读就是，一个update语句，如果读到一行已经加锁的记录，此时InnoDB返回记录最近提交的版本，由MySQL上层判断此版本是否满足update的where条件。若满足(需要更新)，则MySQL会重新发起一次读操作，此时会读取行的最新版本(并加锁)。
具体表现如下:
此时有两个Session，Session1和Session2(navicat上连接的同一用户开启的不同窗口是不同的session)
Session1执行

update test set color = 'blue' where color = 'red'; 

先不Commit事务；
与此同时Ssession2执行

update test set color = 'blue' where color = 'white'; 

session 2尝试加锁的时候，发现行上已经存在锁，InnoDB会开启semi-consistent read，返回最新的committed版本(1,red),(2，white),(5,red),(7,white)。MySQL会重新发起一次读操作，此时会读取行的最新版本(并加锁)。
而在RR隔离级别下，Session2只能等待。

 

明显用RC级别可以提高数据库的性能，还有一个问题，不可重复读的问题不用解决吗？是不用的，读取前后不一致的问题是可以接受的，毕竟你的数据都已经提交了。

 

综上，互联网项目请用读已提交(Read Commited)这个隔离级别。