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