本文主要介绍InnoDB的事务隔离级别
关于Next-Key Lock的内容，请参照「InnoDB备忘录 - Next-Key Lock」，这里不再赘述

脏读、不可重复读、幻读

脏读

在不同的事务下，当前事务可以读到其它事务中尚未提交的数据，即可以读到脏数据

不可重复读

在同一个事务中，同一个查询在T1时间读取某一行，在T2时间重新读取这一行时候，这一行的数据已经发生修改，不可重复读的重点是修改（Update）

幻读

在同一事务中，同一查询多次进行，由于包含插入或删除操作的其他事务提交，导致每次返回不同的结果集，幻读的重点在于插入（Insert）或者删除(Delete)

两类读操作

一致性非锁定读

  1. InnoDB通过行多版本控制的方式来读取当前执行时间数据中行的数据，如果读取的行正在执行DELETE或UPDATE操作，这时读操作不会等待行上锁的释放，而是读取行的一个快照数据
  2. 非锁定读机制极大地提高了数据库的并发性，这是InnoDB默认的读取方式
  3. READ COMMITED和REPEATABLE READ支持一致性非锁定读：在READ COMMITED下，总是读取被锁定行的最新的快照数据，在REPEATABLE READ下，总是读取事务开始时的快照数据

一致性锁定读

对数据库读操作进行显式加锁以保证数据逻辑的一致性，有两种方式：SELECT…FOR UPDATE对读取的行记录加一个X Lock；SELECT…LOCK IN SHARE MODE对读取的行记录加一个S Lock

隔离级别

级别与问题

✓：可能出现 ✗：不会出现

隔离级别 脏读 不可重复读 幻读
READ UNCOMMITTED ✓ ✓ ✓
READ COMMITTED ✗ ✓ ✓
REPEATABLE READ ✗ ✗ ✓
SERIALIZABLE ✗ ✗ ✗

实例

RUC与脏读

READ-UNCOMMITTED存在脏读问题

Session A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> CREATE TABLE t (
    -> a INT NOT NULL PRIMARY KEY,
    -> b INT NOT NULL
    -> ) ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO t SELECT 1,1;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SET SESSION TX_ISOLATION='READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql>BEGIN;
Query OK, 0 rows affected (0.20 sec)

mysql> SELECT * FROM t;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
+---+---+
1 row in set (0.00 sec)

Session B

1
2
3
4
5
6
7
8
9
mysql> SET SESSION TX_ISOLATION='READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE t SET b=2 WHERE a=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Session B将记录从(1,1)更新为(1,2)，因此持有了该记录的X Lock

Session A

1
2
3
4
5
6
7
8
9
10
mysql> SELECT * FROM t;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
+---+---+
1 row in set (0.00 sec)

mysql> SELECT * FROM t WHERE a=1 FOR UPDATE; # 一致性锁定，阻塞一段时间后超时
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Session A读到了Session B尚未提交的数据，属于脏读
Session A尝试持有记录的X Lock，此时该X Lock的持有者为Session B，Session A被阻塞

Session B

事务和锁的详细信息如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> SELECT trx_id,trx_state,trx_requested_lock_id,trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX;
+---------+-----------+-----------------------+---------------------+
| trx_id  | trx_state | trx_requested_lock_id | trx_isolation_level |
+---------+-----------+-----------------------+---------------------+
| 1328837 | LOCK WAIT | 1328837:463:3:2       | READ UNCOMMITTED    |
| 1328836 | RUNNING   | NULL                  | READ UNCOMMITTED    |
+---------+-----------+-----------------------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 1328837:463:3:2 | 1328837     | X         | RECORD    | `test`.`t` | PRIMARY    |        463 |         3 |        2 | 1         |
| 1328836:463:3:2 | 1328836     | X         | RECORD    | `test`.`t` | PRIMARY    |        463 |         3 |        2 | 1         |
+-----------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

mysql> SELECT * FROM information_schema.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 1328837           | 1328837:463:3:2   | 1328836         | 1328836:463:3:2  |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

RC与不可重复读

READ-COMMITTED解决了READ-UNCOMMITTED存在的脏读问题，解决方法是采用一致性非锁定读，读取最新的快照版本，但仍然存在不可重复读问题

Session A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> CREATE TABLE t (
    -> a INT NOT NULL PRIMARY KEY,
    -> b INT NOT NULL
    -> ) ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO t SELECT 1,1;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SET SESSION TX_ISOLATION='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql>BEGIN;
Query OK, 0 rows affected (0.20 sec)

mysql> SELECT * FROM t;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
+---+---+
1 row in set (0.00 sec)

Session B

1
2
3
4
5
6
7
8
9
mysql> SET SESSION TX_ISOLATION='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE t SET b=2 WHERE a=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Session B将记录从(1,1)更新为(1,2)，因此持有了该记录的X Lock

Session A

1
2
3
4
5
6
7
8
9
10
mysql> SELECT * FROM t; # 一致性非锁定读
+---+---+
| a | b |
+---+---+
| 1 | 1 |
+---+---+
1 row in set (0.00 sec)

mysql> SELECT * FROM t FOR UPDATE; # 一致性锁定，阻塞一段时间后超时
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Session A采用一致性非锁定读，而Session B尚未提交，因此Session A能读取到的最新快照版本依然为(1,1)，解决了READ-UNCOMMITTED的脏读问题

Session B

1
2
mysql> COMMIT; # 提交事务
Query OK, 0 rows affected (0.01 sec)

Session A

1
2
3
4
5
6
7
mysql> SELECT * FROM t;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
+---+---+
1 row in set (0.00 sec)

Session B提交事务后，Session A能读取到最新的快照版本(1,2)，而Session A尚未提交事务，初始快照版本为(1,1)，属于不可重复读

RR与幻读

  1. REPEATABLE-READ解决了READ-COMMITTED存在的不可重复读问题，解决方法是采用一致性非锁定读，读取事务初始时的快照版本，但这样仍然存在幻读问题
  2. REPEATABLE-READ结合Next-Key Lock，可以解决幻读问题。幻读问题关注的是Insert和Delete，而Next-Key Locking = Record Lock + Gap Lock，Gap Lock可以防止Insert，Record Lock可以防止Delete。（关于Next-Key Locking的详细内容，请参照博文「InnoDB备忘录 - Next-Key Lock」）
  3. 在REPEATABLE-READ下，MVCC可以这样理解：**MV(Multi Version)用于解决脏读和不可重复读，而CC(Concurrency Control)则是利用Next-Key Lock解决幻读问题**
  4. REPEATABLE READ为InnoDB的默认事务隔离级别，REPEATABLE READ已经完全保证事务的隔离性要求，即达到SERIALIZABLE隔离级别
  5. 隔离级别越低，事务请求的锁越少或保持锁的时间就越短，因此大多数数据库系统（Oracle、SQL Server）的默认事务隔离级别是READ COMMITTED
  6. InnoDB中选择REPEATABLE READ的事务隔离级别不会有任何性能的损失，同样地，即使使用READ COMMITTED的隔离级别，用户也不会得到性能上的大幅度提升

Session A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> CREATE TABLE t (
    -> a INT NOT NULL PRIMARY KEY,
    -> b INT NOT NULL
    -> ) ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO t VALUES (10,10),(20,20),(30,30);
Query OK, 3 row affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SET SESSION TX_ISOLATION='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

mysql>BEGIN;
Query OK, 0 rows affected (0.20 sec)

mysql> SELECT * FROM t WHERE a < 25; # 一致性非锁定读
+----+----+
| a  | b  |
+----+----+
| 10 | 10 |
| 20 | 20 |
+----+----+
2 rows in set (0.00 sec)

Session B

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SET SESSION TX_ISOLATION='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

mysql>BEGIN;
Query OK, 0 rows affected (0.20 sec)

mysql> UPDATE t SET b=200 WHERE a=20;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

Session A

1
2
3
4
5
6
7
8
mysql> SELECT * FROM t WHERE a < 25; # 可重复读
+----+----+
| a  | b  |
+----+----+
| 10 | 10 |
| 20 | 20 |
+----+----+
2 rows in set (0.00 sec)

Session A采用的是一致性非锁定读，读取事务初始时的快照版本，解决了READ-COMMITTED的不可重复读问题

Session B

1
2
3
4
5
6
7
8
9
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t SELECT 0,0;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

Session A

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT * FROM t WHERE a < 25; # 可重复读
+----+----+
| a  | b  |
+----+----+
| 10 | 10 |
| 20 | 20 |
+----+----+
2 rows in set (0.00 sec)

mysql> INSERT INTO t SELECT 0,0;
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'

SELECT筛选出来并没有a=0的记录，但在DELETE时却发生主键冲突，属于幻读

Session A

1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM t WHERE a < 25 FOR UPDATE; # 一致性锁定读，采用Next-Key Locking加锁
+----+-----+
| a  | b   |
+----+-----+
|  0 |   0 |
| 10 |  10 |
| 20 | 200 |
+----+-----+
3 rows in set (0.00 sec)

SELECT...FOR UPDATE属于一致性锁定读，获取**最新的快照版本，然后利用Next-Key Locking进行加锁
加锁的情况：在a = 0,10,20,30上加X Lock，在a ∈ (-∞,0)∪(0,10)∪(10,20)∪(20,30)加Gap Lock**
（关于Next-Key Locking的详细内容，请参照博文「InnoDB备忘录 - Next-Key Lock」）

Session B

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> SET SESSION innodb_lock_wait_timeout=1; # 默认超时时间为50秒
Query OK, 0 rows affected (0.01 sec)

# Session A持有X Lock，Session B无法删除，避免因DELETE而导致的幻读问题
mysql> DELETE FROM t WHERE a = 0;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> DELETE FROM t WHERE a = 10;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> DELETE FROM t WHERE a = 20;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> DELETE FROM t WHERE a = 30;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

# Session A持有Gap Lock，Session B无法插入，避免因INSERT而导致的幻读问题
mysql> INSERT INTO t SELECT -5,-5;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO t SELECT 5,5;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO t SELECT 15,15;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO t SELECT 22,22;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO t SELECT 25,25;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO t SELECT 28,28;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> INSERT INTO t SELECT 35,35;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

Session A

1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM t WHERE a < 25 FOR UPDATE; # 解决幻读问题
+----+-----+
| a  | b   |
+----+-----+
|  0 |   0 |
| 10 |  10 |
| 20 | 200 |
+----+-----+
3 rows in set (0.01 sec)

SERIALIZABLE

  1. SERIALIZABLE不存在脏读、不可重复读和幻读
  2. 在每个SELECT后自动加上LOCK IN SHARE MODE，即每个读操作加上一个S Lock，因此不支持一致性非锁定读（仅RC和RR支持）
  3. 本地事务不使用SERIALIZABLE，SERIALIZABLE主要用于InnoDB的分布式事务

Session A

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> CREATE TABLE t (
    -> a INT NOT NULL PRIMARY KEY,
    -> b INT NOT NULL
    -> ) ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t VALUES (10,10),(20,20),(30,30);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SET SESSION TX_ISOLATION='SERIALIZABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE a = 10; # 自动添加LOCK IN SHARE MODE，一致性锁定读
+----+----+
| a  | b  |
+----+----+
| 10 | 10 |
+----+----+
1 row in set (0.00 sec)

Session A持有S Lock

Session B

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> SET SESSION TX_ISOLATION='SERIALIZABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE a = 10; # S Lock与S Lock兼容
+----+----+
| a  | b  |
+----+----+
| 10 | 10 |
+----+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM t WHERE a = 10 FOR UPDATE; # S Lock与X Lock兼容，阻塞
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Session A

1
2
3
4
5
6
7
8
mysql> SELECT * FROM t WHERE a < 25;
+----+----+
| a  | b  |
+----+----+
| 10 | 10 |
| 20 | 20 |
+----+----+
2 rows in set (0.00 sec)

Session B

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 与RR类似，不存在幻读问题
mysql> DELETE FROM t WHERE a = 10;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> DELETE FROM t WHERE a = 20;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> DELETE FROM t WHERE a = 30;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO t SELECT 5,5;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO t SELECT 15,15;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO t SELECT 22,22;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO t SELECT 28,28;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO t SELECT 35,35;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0