数据库的事务存在着 ACID 四个属性，而在实际应用场景中，当面临着性能需求时，隔离性往往成为最容易妥协的一个，其实际上就是指数据库在并发事务下的表现。

在本文章中，详细介绍下 InnoDB 中关于隔离级别的实现。

简介

事务是具有 ACID 特性的一系列操作集合，其中原子性表示某个事务中的所有操作，要么全部成功，要么全部失败，但无法保证事务的过程也是原子的，约束事务过程的是隔离性，而隔离级别定义了事务间的隔离程度。

在 ANSI SQL-92 中定义的隔离级别定义是当前使用最广的，包括了读未提交 (Read Uncommitted)、读提交 (Read Committed)、可重复读 (Repeatable Read) 和可序列化 (Serializable) 四种。

异常场景

不同隔离级别会影响到数据库的资源开销，最常见的就是锁，从而会影响系统并发，除此之外，还会需要特别注意可能产生的影响，在 SQL-92 中对其中的三种也进行了说明。

Lost Update 丢失更新

当两个事务读取相同数据，然后都尝试更新原来的数据成新的值，此时，第二个事务可能完全覆盖掉第一个所完成的更新，这也是唯一一个用户可能在所有情况下都想避免的行为，在 SQL 92 中甚至没有提及。

Dirty Read 脏读

严格脏读是指已经提交事物读取到一个不会提交事物写入内容，不过也可以扩展为一个事务中读取到另一个事务未提交的数据。例如，事务 T1 读取到另一个事务 T2 未提交的数据，如果 T2 回滚，则 T1 相当于读取到了一个被认为不可能出现的值。

Non-Repeatable Read 不可重复读

在一个事务中，当重复读取 同一条记录 时，发现该记录的结果不同或者已经被删除了；如在事务 T1 中读取了一行，接着 T2 修改或者删除了该行并提交，那么当 T1 尝试读取新的值时，就会发现改行的值已经修改或者被删除。

Phantom Read 幻读

通常是指在一个事务中，当重复查询 一个结果集 时，返回的两个不同的结果集，可能是由于另一个事务插入或者删除了一些记录。例如，事务 T1 读取一个结果集，T2 修改了该结果集中的部分记录 (例如插入一条记录)，T1 再次读取时发现与之前的结果不同 (多出来一条记录)，就像产生幻觉一样。

总结

隔离级别与读场景。

Isolation Level	Dirty Read	Non-repeatable Read	Phantom Read
Read Uncommitted	Possible	Possible	Possible
Read Committed	Not Possible	Possible	Possible
Repeatable Read	Not Possible	Not Possible	Possible
Serializable	Not Possible	Not Possible	Not Possible

MySQL 配置

InnoDB 默认是可重复读的 (REPEATABLE READ)，提供 SQL-92 标准所描述的所有四个事务隔离级别，可以在启动时用 --transaction-isolation 选项设置，也可以配置文件中设置。

$ cat /etc/my.cnf
[mysqld]
transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}

用户可以用 SET TRANSACTION 语句改变单个会话或者所有新进连接的隔离级别，语法如下：

mysql> SET autocommit=0;
mysql> SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
       {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

如果使用 GLOBAL 关键字，当然需要 SUPER 权限，则从设置时间点开始创建的所有新连接均采用该默认事务级别，不过原有链接事务隔离级别不变。

可以用下列语句查询全局和会话事务隔离级别。

mysql> SHOW VARIABLES LIKE 'tx_isolation';
mysql> SELECT @@global.tx_isolation;
mysql> SELECT @@session.tx_isolation;
mysql> SELECT @@tx_isolation;

事务超时

与事务超时相关的变量可以参考。

----- 设置锁超时时间，单位为秒，默认50s
mysql> SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.00 sec)

----- 超时后的行为，默认OFF，详见如下介绍
mysql> SHOW VARIABLES LIKE 'innodb_rollback_on_timeout';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | OFF   |
+----------------------------+-------+
1 row in set (0.02 sec)

innodb_rollback_on_timeout 变量默认值为 OFF，如果事务因为加锁超时，会回滚上一条语句执行的操作；如果设置 ON，则整个事务都会回滚。

当上述变量为 OFF 时，也就是事务会回滚到上一个保存点，这是因为 InnoDB 在执行每条 SQL 语句之前，都会创建一个保存点，可以参见 row_insert_for_mysql() 函数中的代码。

其它

如何判断当前会话已经开启了一个事务？

----- 可以直接使用在事务中会报错的语句。
mysql> SELECT @@TX_ISOLATION;
mysql> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

----- 或者通过如下SQL查看，如果在事务中则会返回当前的事务ID，否则返回为空。
mysql> SELECT trx_id FROM information_schema.innodb_trx WHERE trx_mysql_thread_id = connection_id();

示例详解

先准备下环境，隔离级别在测试时，会针对不同的场景分别进行设置；另外，将 autocommit 设置为 0，此时 commit/rollback 后的一条语句会自动开启一个新事务。

----- 新建表并写入数据
mysql> SET GLOBAL autocommit=0;
mysql> CREATE TABLE user (id INT PRIMARY KEY, name VARCHAR(20), age INT UNSIGNED) engine=InnoDB;
mysql> INSERT INTO user VALUES (1, 'andy', 28);

----- 设置隔离级别
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

一般来说，也显然，高一级的级别可以提供更强的隔离性。

READ UNCOMMITTED

在读未提交隔离级别下可能出现 脏读，也就是某个事务可以看到其它事务尚未提交的数据改动，这是最低的隔离等级。

### 准备数据
DELETE FROM user;
INSERT INTO user VALUES (1, 'andy', 28);
### 同时设置两个事务的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

----- TRANS A ---------------------------------------------+----- TRANS B -------------------------
### 读到的age为28
SELECT * FROM user WHERE id=1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | andy |   28 |
+----+------+------+
1 row in set (0.00 sec)
                                                             ### 开启一个事务，隔离级别任意，更新age
                                                             START TRANSACTION;
                                                             UPDATE user SET age=30 WHERE id=1;
### 读到未提交数据，age为30
SELECT * FROM user WHERE id=1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | andy |   30 |
+----+------+------+
1 row in set (0.00 sec)
                                                             ### 回滚事务
                                                             ROLLBACK;
### 读到的age为28
SELECT * FROM user WHERE id=1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | andy |   28 |
+----+------+------+
1 row in set (0.00 sec)

如上所示，在事务 A 中，会读取到不同的 age 值，而且该值最终已经回退，相当于一个不存在的值。

READ COMMITTED

在读已提交隔离级别下不会出现上述的脏读，但 可能出现不可重复读，也指一个事务内的两次读同一行看到的数据不一样。

### 准备数据
DELETE FROM user;
INSERT INTO user VALUES (1, 'andy', 28);
### 同时设置两个事务的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

----- TRANS A ---------------------------------------------+----- TRANS B -------------------------
START TRANSACTION;
SELECT * FROM user WHERE id = 1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | andy |   28 |
+----+------+------+
1 row in set (0.00 sec)
                                                             START TRANSACTION;
                                                             UPDATE user SET age=30 WHERE id=1;
                                                             SELECT * FROM user;
                                                             +----+------+------+
                                                             | id | name | age  |
                                                             +----+------+------+
                                                             |  1 | andy |   30 |
                                                             +----+------+------+
                                                             1 row in set (0.00 sec)
### 读到的age仍然为28
SELECT * FROM user WHERE id = 1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | andy |   28 |
+----+------+------+
1 row in set (0.00 sec)
                                                             COMMIT;
### 此时在事务B提交之后，读到的age为30，不可重复读
SELECT * FROM user WHERE id = 1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | andy |   30 |
+----+------+------+
1 row in set (0.00 sec)
COMMIT;

如上，事务 A 中同一条 SQL 会读取到不同的数值，即使事务 B 中是删除操作。

REPEATABLE READ

可重复读是 InnoDB 默认的隔离级别，严格来说 MySQL 是不会出现幻读的，但是在某些场景下会降级到 RC 级别，但是允许通过显示加锁规避。

### 准备数据
DELETE FROM user;
INSERT INTO user VALUES (1, 'andy', 28),(2, 'cassie', 25);
INSERT INTO user VALUES (1, 'andy', 28);
### 同时设置两个事务的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

----- TRANS A ---------------------------------------------+----- TRANS B -------------------------
START TRANSACTION;
SELECT SUM(age) FROM user WHERE ID IN(1, 2);
+----------+
| SUM(age) |
+----------+
|       53 |
+----------+
1 row in set (0.01 sec)
                                                             UPDATE user SET age=30 WHERE id=1;
                                                             SELECT * FROM user;
                                                             +----+--------+------+
                                                             | id | name   | age  |
                                                             +----+--------+------+
                                                             |  1 | andy   |   30 |
                                                             |  2 | cassie |   25 |
                                                             +----+--------+------+
                                                             2 row in set (0.00 sec)

### 默认是快照读，此时和保持不变，没有幻读
SELECT SUM(age) FROM user WHERE ID IN(1, 2);
+----------+
| SUM(age) |
+----------+
|       53 |
+----------+
1 row in set (0.01 sec)

### 更新之后会导致幻读
UPDATE user SET name="Hello" WHERE id=1;
SELECT SUM(age) FROM user WHERE ID IN(1, 2);
+----------+
| SUM(age) |
+----------+
|       55 |
+----------+
1 row in set (0.01 sec)

这里是因为 MySQL 将快照读和当前读混合使用导致，也可以理解为降级到 Read Committed 隔离级别，严格来说这是不满足重复读的，不只是出现幻读。

如下的场景也是类似的。

### 准备数据
DELETE FROM user;
### 同时设置两个事务的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

----- TRANS A ---------------------------------------------+----- TRANS B -------------------------
START TRANSACTION;
SELECT * FROM user;
Empty set (0.00 sec)
                                                             START TRANSACTION;
                                                             INSERT INTO user VALUES (1, 'andy', 28);
                                                             COMMIT;
### 此时查询仍然为空
SELECT * FROM user;
Empty set (0.00 sec)
### 尝试插入数据时报错，TMD明明说没有这条记录的
INSERT INTO user VALUES (1, 'andy', 28);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
COMMIT;


START TRANSACTION;
SELECT * FROM user;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | andy |   28 |
+----+------+------+
1 row in set (0.00 sec)
                                                             START TRANSACTION;
                                                             INSERT INTO user VALUES (2, 'cassie', 25);
                                                             COMMIT;
SELECT * FROM user;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | andy |   28 |
+----+------+------+
1 row in set (0.00 sec)
### 不是说有一条记录吗，怎么多出来一条啊！！！
UPDATE user SET age=10;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

注意，还有一个相关的场景。

----- TRANS A ---------------------------------------------+----- TRANS B -------------------------
START TRANSACTION;
select * from user where id > 0 and id < 5;
                                                              START TRANSACTION;
                                                              INSERT INTO user VALUES(3, 'bob', 22);
### 可以正常查询
SELECT * FROM USER WHERE ID > 0 AND ID < 5;
### 如下查询，会导致锁超时
SELECT * FROM USER WHERE ID > 0 AND ID < 5 LOCK IN SHARE MODE;
SELECT * FROM USER WHERE ID > 0 AND ID < 5 FOR UPDATE;
UPDATE USER SET AGE = 50 WHERE ID = 3;
COMMIT;

InnoDB 提供了这样的机制，在可重复读的隔离级别里，会同时使用快照和加锁，包括间隙锁，也可以使用加锁读去查询当前最新的数据。

Write Skew

在 Repeatable Read 可能出现 Write Skew，该异常主要是针对多行写事务。

一个简单的示例，事务 A 读取了行 X，并以此将 Y 修改为 X，然后提交；同时事务 B 读取了行 Y，并将 X 修改为 Y 然后提交；在每个事物里都是期望两者相同的，但最终结果两者的值互换了。

### 准备数据
DELETE FROM user;
INSERT INTO user VALUES (1, 'andy', 28),(2, 'cassie', 25);
### 同时设置两个事务的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

----- TRANS A ---------------------------------------------+----- TRANS B -------------------------
START TRANSACTION;
SELECT * FROM user WHERE id=1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | andy |   28 |
+----+------+------+
1 row in set (0.00 sec)
                                                             START TRANSACTION;
                                                             SELECT * FROM user WHERE id=2;
                                                             +----+--------+------+
                                                             | id | name   | age  |
                                                             +----+--------+------+
                                                             |  2 | cassie |   25 |
                                                             +----+--------+------+
                                                             1 row in set (0.00 sec)
UPDATE user SET age=28 WHERE id=2;
SELECT * FROM user;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | andy   |   28 |
|  2 | cassie |   28 |
+----+--------+------+
2 rows in set (0.00 sec)
                                                             UPDATE user SET age=25 WHERE id=1;
                                                             SELECT * FROM user;
                                                             +----+--------+------+
                                                             | id | name   | age  |
                                                             +----+--------+------+
                                                             |  1 | andy   |   25 |
                                                             |  2 | cassie |   25 |
                                                             +----+--------+------+
                                                             2 rows in set (0.00 sec)
                                                             COMMIT;
COMMIT;
SELECT * FROM user;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | andy   |   25 |
|  2 | cassie |   28 |
+----+--------+------+
2 rows in set (0.00 sec)

SERIALIZABLE

也就是串行化不允许上述的异常情况，包括 Phantom 和 Write Skew 以及任何不可串行化的反常情况。

### 准备数据
DELETE FROM user;
INSERT INTO user VALUES (1, 'andy', 28),(2, 'cassie', 25);
### 同时设置两个事务的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

----- TRANS A ---------------------------------------------+----- TRANS B -------------------------
START TRANSACTION;
SELECT * FROM user;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | andy   |   28 |
|  2 | cassie |   25 |
+----+--------+------+
2 rows in set (0.00 sec)
                                                             START TRANSACTION;
                                                             SELECT * FROM user;
                                                             +----+--------+------+
                                                             | id | name   | age  |
                                                             +----+--------+------+
                                                             |  1 | andy   |   28 |
                                                             |  2 | cassie |   25 |
                                                             +----+--------+------+
                                                             2 rows in set (0.00 sec)
SELECT * FROM user WHERE id=2;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  2 | cassie |   25 |
+----+--------+------+
1 row in set (0.00 sec)
                                                             ### 此时尝试更新时会等待直到锁超时
                                                             UPDATE user SET age=15 WHERE id=2;
COMMIT;
                                                             ### 重新尝试提交
                                                             UPDATE user SET age=15 WHERE id=2;
                                                             SELECT * FROM user;
                                                             +----+--------+------+
                                                             | id | name   | age  |
                                                             +----+--------+------+
                                                             |  1 | andy   |   28 |
                                                             |  2 | cassie |   15 |
                                                             +----+--------+------+
                                                             2 rows in set (0.00 sec)
                                                             COMMIT;

接下来，再看个示例。

### 准备数据
DELETE FROM user;
INSERT INTO user VALUES (1, 'andy', 28);
### 同时设置两个事务的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

----- TRANS A ---------------------------------------------+----- TRANS B -------------------------
START TRANSACTION;
SELECT * FROM user;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | andy   |   28 |
+----+--------+------+
1 rows in set (0.00 sec)
                                                             START TRANSACTION;
                                                             SELECT * FROM user;
                                                             +----+--------+------+
                                                             | id | name   | age  |
                                                             +----+--------+------+
                                                             |  1 | andy   |   28 |
                                                             |  2 | cassie |   25 |
                                                             +----+--------+------+
                                                             2 rows in set (0.00 sec)
                                                             ### 此时尝试更新时会等待直到锁超时
                                                             UPDATE user SET age=15 WHERE id=2;
                                                             ### 同上，仍然锁等待超时
                                                             INSERT INTO user VALUES (2, 'cassie', 25);

参考

A Critique of ANSI SQL Isolation Levels
关于数据库的事务隔离级别可以参考 WikiPedia - Isolation (database systems) 中的介绍。

InnoDB 隔离级别使用实例

简介 #

异常场景 #

Lost Update 丢失更新 #

Dirty Read 脏读 #

Non-Repeatable Read 不可重复读 #

Phantom Read 幻读 #

总结 #

MySQL 配置 #

事务超时 #

其它 #

示例详解 #

READ UNCOMMITTED #

READ COMMITTED #

REPEATABLE READ #

Write Skew #

SERIALIZABLE #

参考 #

简介