MySQL的事务隔离级别

一、数据库的隔离级别概述

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

二、未提交读（Read uncommited） -- （脏读）

会出现脏读，也就是可能读取到其他会话中未提交事务修改的数据，数据库一般都不会用，而且任何操作都不会加锁

分别在A、B两个客户端执行：

A：
root@(none) 10:54>SET GLOBAL tx_isolation='READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)

root@(none) 10:54>SELECT @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)
//开启事务
root@test 10:55>begin;
Query OK, 0 rows affected (0.00 sec)

root@test 10:55>select * from test1;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)

B上：
root@test 10:58>select @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

root@test 10:58>
root@test 10:58>begin;
Query OK, 0 rows affected (0.00 sec)

root@test 10:58>insert into test.test1 values (999);
Query OK, 1 row affected (0.00 sec)

root@test 10:58>select * from test.test1;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 999 |
+------+
5 rows in set (0.00 sec)
此处B客户端并未commit；

再查看A客户端：
root@test 10:58>select * from test1;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 999 |
+------+
5 rows in set (0.00 sec)

此处A可以看到新的记录了。

可见，客户端B中新增了记录，未commit，此时客户端A却读出了新增的数据，如果此时客户端B取消掉当前的记录，那么数据库中就不存在该记录，然而客户端A却拥有了该数据，这就是 脏读！

三、已提交读（Read commited） -- （不可重复读）

这是大多数数据库的默认隔离级别（除了MySQL），简单的理解就是，只能读取到最新的数据

在A客户端：
root@(none) 11:10>SET GLOBAL tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

root@(none) 11:10>SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

root@(none) 11:10>
root@(none) 11:10>begin;
Query OK, 0 rows affected (0.00 sec)

root@(none) 11:10>select * from test.test1;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+

在B客户端执行：
root@test 11:11>begin;
Query OK, 0 rows affected (0.00 sec)

root@test 11:11>select * from test.test1;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)

root@test 11:11>
root@test 11:11>delete from test.test1 where a=1;
Query OK, 1 row affected (0.00 sec)

root@test 11:12>select * from test.test1;
+------+
| a |
+------+
| 2 |
| 3 |
| 4 |
+------+

此时查询A客户端：

root@(none) 11:12>select * from test.test1;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
此处看出A客户端无变化，在B客户端执行commit后再查看A客户端：

root@(none) 11:13>select * from test.test1;
+------+
| a |
+------+
| 2 |
| 3 |
| 4 |
+------+

可以看到A客户端的数据已经变了。已提交读只允许读取已提交的记录，但不要求可重复读。
用MVCC来说就是读取当前行的最新版本。

可以看到，已提交读，是读到只能读取到最新的数据；

当客户端B删除了记录时未commit时，此时数据库中最新的数据还是原来的数据，所以客户端A读取到的还是原来的数据；

当客户单B commit之后，此时数据库中最新的数据已经是删除后的数据了，所以客户端A读取到的是删除后的数据；

四、可重复读（Repeatable read ）

这是MySQL Innodb的默认隔离级别，在同一个事务内的查询都是事务开始时刻一致的。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读，然而，在MySQL中通过InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

在A客户端上：
root@(none) 11:17>SET GLOBAL tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

root@(none) 11:17>
root@(none) 11:17>
root@(none) 11:17>SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

root@(none) 11:17>BEGIN;
Query OK, 0 rows affected (0.00 sec)

在B客户端上：
root@test 11:20>select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

root@test 11:20>insert into test.test1 values (555);
Query OK, 1 row affected (0.00 sec)

root@test 11:20>commit;
Query OK, 0 rows affected (0.00 sec)

root@test 11:21>
root@test 11:21>select * from test.test1;
+------+
| a |
+------+
| 2 |
| 3 |
| 4 |
| 555 |
+------+
4 rows in set (0.00 sec)
此处在B客户端上已经commit.

然后查看A客户端：

root@(none) 11:22>SELECT * FROM test.test1;
+------+
| a |
+------+
| 2 |
| 3 |
| 4 |
+------+
3 rows in set (0.00 sec)

root@(none) 11:22>commit;
Query OK, 0 rows affected (0.00 sec)


root@(none) 11:22>SELECT * FROM test.test1;
+------+
| a |
+------+
| 2 |
| 3 |
| 4 |
| 555 |
+------+
4 rows in set (0.00 sec)

在A客户端上提交后可以看到新数据。
也就是说在可重复读隔离级别只能读取已经提交的数据，并且在一个事务内，读取的数据就是事务开始时的数据。

可以看到，事务A一开始读的数据，接着事务B对数据进行修改，但是并没有影响到事务A的读的数据，也就是说，在Repeatable read的隔离级别上，在一个事务内，

五、Serializable（可串行化）  不使用

是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

该类型在A客户端操作test.test1表时会锁定该数据，如果B客户端想要操作test.test1就需要等待A客户端释放。

六、什么是幻读？

幻读只要为insert造成的，当事务不是独立执行时，发生的现象。

例如，事务A修改员工表的所有员工工资为1000，修改行数为10，然后事务B增加一个1000的员工，事务A读取员工表时，发现，工资1000的员工数时11，造成了幻读

七、已提交读和可重复读的原理

通过锁机制来实现？

如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别 ，读用读锁，写用写锁，读锁和写锁互斥（当数据处于被读状态，不能被写；或者处于被写状态，不能被读），这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。

MySQL、Oracle、PostgreSQL等成熟的数据库，出于性能考虑，都是使用了以乐观锁为理论基础的MVCC（多版本并发控制）来避免这两种问题。

八、多版本并发控制  MVCC

mvcc的实现，是通过保存数据在某个时间点的快照来实现的。（快照读）

也就是说

1. 无论事务执行多长时间，每一个事务看到的数据都是一样的

2. 根据事务开始的时间不同，不同的事务对同一张表，同一时刻看到的数据可能不一样

九、MVCC在MySQL InnoDB的使用（通过乐观锁）

在InnoDB中，会在每行数据后添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。 在实际操作中，存储的并不是时间，而是事务的版本号，每开启一个新事务，事务的版本号就会递增。 在可重读Repeatable reads事务隔离级别下：

• SELECT时，读取创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号。
• INSERT时，保存当前事务版本号为行的创建版本号
• DELETE时，保存当前事务版本号为行的删除版本号
• UPDATE时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行

通过MVCC，虽然每行记录都需要额外的存储空间，更多的行检查工作以及一些额外的维护工作，但可以减少锁的使用，大多数读操作都不用加锁，读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行，也只锁住必要行。

我们不管从数据库方面的教课书中学到，还是从网络上看到，大都是上文中事务的四种隔离级别这一模块列出的意思，RR级别是可重复读的，但无法解决幻读，而只有在Serializable级别才能解决幻读。于是我就加了一个事务C来展示效果。在事务C中添加了一条teacher_id=1的数据commit，RR级别中应该会有幻读现象，事务A在查询teacher_id=1的数据时会读到事务C新加的数据。但是测试后发现，在MySQL中是不存在这种情况的，在事务C提交后，事务A还是不会读到这条数据。可见在MySQL的RR级别中，是解决了幻读的读问题的。参见下图

读问题解决了，根据MVCC的定义，并发提交数据时会出现冲突，那么冲突时如何解决呢？我们再来看看InnoDB中RR级别对于写数据的处理。

####“读”与“读”的区别
可能有读者会疑惑，事务的隔离级别其实都是对于读数据的定义，但到了这里，就被拆成了读和写两个模块来讲解。这主要是因为MySQL中的读，和事务隔离级别中的读，是不一样的。

我们且看，在RR级别中，通过MVCC机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，是不及时的数据，不是数据库当前的数据！这在一些对于数据的时效特别敏感的业务中，就很可能出问题。

对于这种读取历史数据的方式，我们叫它快照读 (snapshot read)，而读取数据库当前版本数据的方式，叫当前读 (current read)。很显然，在MVCC中：

• 快照读：就是select
  • select * from table ....;
• 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁。
  • select * from table where ? lock in share mode;
  • select * from table where ? for update;
  • insert;
  • update ;
  • delete;

事务的隔离级别实际上都是定义了当前读的级别，MySQL为了减少锁处理（包括等待其它锁）的时间，提升并发能力，引入了快照读的概念，使得select不用加锁。而update、insert这些“当前读”，就需要另外的模块来解决了。

因为更新数据、插入数据是针对当前数据的，所以不能以快照的历史数据为参考，此处就是这个意思。

###写（"当前读"）
事务的隔离级别中虽然只定义了读数据的要求，实际上这也可以说是写数据的要求。上文的“读”，实际是讲的快照读；而这里说的“写”就是当前读了。
为了解决当前读中的幻读问题，MySQL事务使用了Next-Key锁。

####Next-Key锁
Next-Key锁是行锁和GAP（间隙锁）的合并，行锁上文已经介绍了，接下来说下GAP间隙锁。

行锁可以防止不同事务版本的数据修改提交时造成数据冲突的情况。但如何避免别的事务插入数据就成了问题。我们可以看看RR级别和RC级别的对比

RC级别：

事务A	事务B
begin;	begin;
select id,class_name,teacher_id from class_teacher where teacher_id=30; id class_name teacher_id 2 初三二班 30
update class_teacher set class_name='初三四班' where teacher_id=30;
	insert into class_teacher values (null,'初三二班',30); commit;
select id,class_name,teacher_id from class_teacher where teacher_id=30; id class_name teacher_id 2 初三四班 30 10 初三二班 30

RR级别：

事务A	事务B
begin;	begin;
select id,class_name,teacher_id from class_teacher where teacher_id=30; id class_name teacher_id 2 初三二班 30
update class_teacher set class_name='初三四班' where teacher_id=30;
	insert into class_teacher values (null,'初三二班',30); waiting....
select id,class_name,teacher_id from class_teacher where teacher_id=30; id class_name teacher_id 2 初三四班 30
commit;	事务Acommit后，事务B的insert执行。

通过对比我们可以发现，在RC级别中，事务A修改了所有teacher_id=30的数据，但是当事务Binsert进新数据后，事务A发现莫名其妙多了一行teacher_id=30的数据，而且没有被之前的update语句所修改，这就是“当前读”的幻读。

RR级别中，事务A在update后加锁，事务B无法插入新数据，这样事务A在update前后读的数据保持一致，避免了幻读。这个锁，就是Gap锁。

MySQL是这么实现的：

在class_teacher这张表中，teacher_id是个索引，那么它就会维护一套B+树的数据关系，为了简化，我们用链表结构来表达（实际上是个树形结构，但原理相同）

如图所示，InnoDB使用的是聚集索引，teacher_id身为二级索引，就要维护一个索引字段和主键id的树状结构（这里用链表形式表现），并保持顺序排列。

Innodb将这段数据分成几个个区间

• (negative infinity, 5],
• (5,30],
• (30,positive infinity)；

update class_teacher set class_name='初三四班' where teacher_id=30;不仅用行锁，锁住了相应的数据行；同时也在两边的区间，（5,30]和（30，positive infinity），都加入了gap锁。这样事务B就无法在这个两个区间insert进新数据。

受限于这种实现方式，Innodb很多时候会锁住不需要锁的区间。如下所示：

事务A	事务B	事务C
begin;	begin;	begin;
select id,class_name,teacher_id from class_teacher; id class_name teacher_id 1 初三一班 5 2 初三二班 30
update class_teacher set class_name='初一一班' where teacher_id=20;
	insert into class_teacher values (null,'初三五班',10); waiting .....	insert into class_teacher values (null,'初三五班',40);
commit;	事务A commit之后，这条语句才插入成功	commit;
	commit;

update的teacher_id=20是在(5，30]区间，即使没有修改任何数据，Innodb也会在这个区间加gap锁，而其它区间不会影响，事务C正常插入。

如果使用的是没有索引的字段，比如update class_teacher set teacher_id=7 where class_name='初三八班（即使没有匹配到任何数据）',那么会给全表加入gap锁。同时，它不能像上文中行锁一样经过MySQL Server过滤自动解除不满足条件的锁，因为没有索引，则这些字段也就没有排序，也就没有区间。除非该事务提交，否则其它事务无法插入任何数据。

行锁防止别的事务修改或删除，GAP锁防止别的事务新增，行锁和GAP锁结合形成的的Next-Key锁共同解决了RR级别在写数据时的幻读问题。

参考：http://blog.csdn.net/matt8/article/details/53096405

InnoDB加锁分析