MySQL InnoDB中的四种隔离级别

MySQL InnoDB中实现了四种隔离级别
-read uncommitted 应用可以读取到其他应用未提交的数据
-read committed 应用只能读取其他应用已经提交的数据
-repeatable read 应用保证同一事务中，每次读取的数据都是一样的
-serializable 强制序列化读写操作，即不允许读写操作同时进行

文章主要使用例子来说明四种隔离级别，假设有以下表的定义和数据：
mysql> create table test1(id int primary key, year int);

mysql> insert into test1 values(100,2000),(200,2010),(14,2007),(4, 2008);

1. read uncommitted

Session 1:mysql> set session transaction isolation level read uncommitted;mysql> select @@tx_isolation;+------------------+| @@tx_isolation   |+------------------+| READ-UNCOMMITTED |+------------------+1 row in set (0.00 sec)mysql> start transaction;mysql> select * from test1 where id=14;+----+------+| id | year |+----+------+| 14 | 2007 |+----+------+1 row in set (0.04 sec)Session 2:mysql> set session transaction isolation level read uncommitted;mysql> start transaction;mysql> update test1 set year = 2088 where id=14;Session 1:mysql> select * from test1 where id=14;+----+------+| id | year |+----+------+| 14 | 2088 |+----+------+1 row in set (0.00 sec)

示例图如下：

2. Read Committed

Session 1:mysql> set session transaction isolation level read committed;mysql> select @@tx_isolation;+----------------+| @@tx_isolation |+----------------+| READ-COMMITTED |+----------------+1 row in set (0.00 sec)mysql> start transaction;mysql> select * from test1 where id=14;+----+------+| id | year |+----+------+| 14 | 2007 |+----+------+1 row in set (0.04 sec)Session 2:mysql> set session transaction isolation level read committed;mysql> start transaction;mysql> update test1 set year = 2088 where id=14;Session 1:mysql> select * from test1 where id=14;+----+------+| id | year |+----+------+| 14 | 2007 |+----+------+1 row in set (0.00 sec)Session 2:mysql> commit;Session 1:mysql> select * from test1 where id=14;+----+------+| id | year |+----+------+| 14 | 2088 |+----+------+1 row in set (0.00 sec)

示意图如下：

对于locking reads(SELECT ... FOR UPDATE 或者 SELECT ... LOCK IN SHARE MODE)、UPDATE和DELETE而言，在这种隔离级别下，InnoDB只锁index records，不锁gap。Gap locking 只用来做 foreign-key 约束检查和duplicate-key检查。因为不锁gap，不能防止幻像读。

另外，对于UPDATE或者DELETE而言，InnoDB只锁符合条件的记录，对于不符合条件的记录，在完成where条件的评估之后，就会把锁释放掉。（这一点和Repeatable Read不同，在RR隔离级别下，如果不走索引，那么会把所有的record和gap都加上锁）

3. Repeatable Read:

Session 1:mysql> set session transaction isolation level repeatable read;mysql> select @@tx_isolation;+-----------------+| @@tx_isolation  |+-----------------+| REPEATABLE-READ |+-----------------+1 row in set (0.00 sec)mysql> start transaction;mysql> select * from test1 where id=14;+----+------+| id | year |+----+------+| 14 | 2007 |+----+------+1 row in set (0.04 sec)Session 2:mysql> set session transaction isolation level repeatable read;mysql> start transaction;mysql> update test1 set year = 2088 where id=14;Session 1:mysql> select * from test1 where id=14;+----+------+| id | year |+----+------+| 14 | 2007 |+----+------+1 row in set (0.00 sec)Session 2:mysql> commit;Session 1:mysql> select * from test1 where id=14;+----+------+| id | year |+----+------+| 14 | 2007 |+----+------+1 row in set (0.00 sec)mysql> commit;mysql> select * from test1 where id=14;+----+------+| id | year |+----+------+| 14 | 2088 |+----+------+1 row in set (0.00 sec)

示意图如下：

RR这种情况下的Lock Read加锁情况如下：

A. 如果是unique index上的 unique search condition，只锁那一条记录，不加gap锁。
B. 其他情况下，都需要加gap锁。

4. serializable

Session 1:mysql> set session transaction isolation level serializable;mysql> select @@tx_isolation;+----------------+| @@tx_isolation |+----------------+| SERIALIZABLE   |+----------------+1 row in set (0.00 sec)mysql> start transaction;mysql> select * from test1 where id=14;+----+------+| id | year |+----+------+| 14 | 2007 |+----+------+1 row in set (0.04 sec)Session 2:mysql> set session transaction isolation level serializable;mysql> start transaction;mysql> update test1 set year = 2088 where id=14;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

示意图如下：

https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html