MySQL InnoDB事务隔离级别笔记

TL;DR

MySQL InnoDB可以设定ANSI SQL-92中规定的四个事务隔离级别,事务并发度和事务隔离级别成反比,事务隔离级别越高,并发度越低。

是否有锁操作,取决于当前的读取是快照读,还是当前读,快照读读取的是可见的历史版本,无需上锁,简单的读操作是快照读(SELECT无附加语句),其他都是当前读,需要加锁。

对无索引的数据,InnoDB会锁定全表的记录,但是会在扫描过程中释放不符合筛选规则的记录的锁定。

对于有索引的数据,需要具体分析,在RR这一级别,InnoDB通过GAP锁机制避免了幻读问题。

ANSI SQL-92标准

事务隔离级别在ANSI SQL-92标准中有了明确的定义,即我们熟知的READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ/SERIALIZABLE,级别由低到高。

在大量博文中经常提到的比如脏读(Dirty read)不可重复读(Non-repeatable read)幻读(Phantom),标准也有提及。

脏读(Dirty read)

1
2
3
4
5
1) P1 ("Dirty read"): SQL-transaction T1 modifies a row. SQL-
transaction T2 then reads that row before T1 performs a COMMIT.
If T1 then performs a ROLLBACK, T2 will have read a row that was
never committed and that may thus be considered to have never
existed.

简而言之,即两个或多个事务中有一个事务更改了数据,无论提交与否,都会读取到数据的改动。

在ANSI SQL-92标准中,READ UNCOMMITTED会可能出现脏读。

不可重复读(Non-repeatable read)

1
2
3
4
2) P2 ("Non-repeatable read"): SQL-transaction T1 reads a row. SQL-
transaction T2 then modifies or deletes that row and performs
a COMMIT. If T1 then attempts to reread the row, it may receive
the modified value or discover that the row has been deleted.

两个或多个事务中有一个事务更改了数据,只有提交之后,才会读取到数据的改动。

在ANSI SQL-92标准中,READ COMMITTED不会出现脏读,但是会可能出现不可重复读。

幻读(Phantom)

1
2
3
4
5
6
3)  P3 ("Phantom"): SQL-transaction T1 reads the set of rows N
that satisfy some <search condition>. SQL-transaction T2 then
executes SQL-statements that generate one or more rows that
satisfy the <search condition> used by SQL-transaction T1. If
SQL-transaction T1 then repeats the initial read with the same
<search condition>, it obtains a different collection of rows.

幻读发生的前提是一个事务改动(新增)并提交处于其他某一事务查询条件范围内,并且其他事务重复使用同一条件进行查询。

在ANSI SQL-92标准中,REPEATABLE READ不会出现脏读,不会出现不可重复读,但是会可能出现幻读。

在最高的事务隔离级别SERIALIZABLE中,以上问题都不会出现。

MySQL InnoDB的RR事务隔离级别

InnoDB已经是MySQL首选存储引擎,内容会针对默认的隔离级别展开。

MySQL InnoDB实现了标准中的4个事务隔离级别,默认的事务隔离级别是REPEATABLE READ

1
2
3
4
5
6
mysql>  SELECT @@tx_isolation;
+-----------------+

| @@tx_isolation |
+-----------------+

| REPEATABLE-READ |
+-----------------+

和标准不同的是,MySQL在这一级别通过GAP锁消除了幻读问题。

MySQL的并发度和事务隔离级别成反比,事务隔离级别越高,并发度越低。

幻读的解决方案:GAP锁

GAP锁用于锁定记录的间隙,防止事务并发时同一搜索条件下,出现事务中前后两次读取结果不同的情况。

来看测试表test:

1
2
3
4
5
6
7
8
9
mysql> desc test;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| v1 | int(11) | NO | UNI | 0 | |
| v2 | int(11) | NO | MUL | 0 | |
| v3 | int(11) | NO | | 0 | |
+-------+------------------+------+-----+---------+----------------+

现有数据:

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT * FROM test;
+----+-----+-----+-----+
| id | v1 | v2 | v3 |
+----+-----+-----+-----+
| 1 | 1 | 1 | 1 |
| 3 | 2 | 2 | 2 |
| 4 | 3 | 3 | 4 |
| 5 | 4 | 4 | 5 |
| 8 | 100 | 100 | 101 |
| 12 | 200 | 200 | 200 |
+----+-----+-----+-----+

事务A(ID 381797)尝试读取v1=10的记录:

1
2
mysql> SELECT * FROM test WHERE v1=10 FOR UPDATE;
Empty set (0.01 sec)

这是一个当前读,由于v1是一个唯一索引,v1=10这一个条件处于id=5,v1=4与id=8,v1=100的记录之间的间隙,所以为了防止两次读取数据不一样,需要在这之间增加GAP锁。

同时事务B(ID 381798)尝试写入一条记录:

1
2
mysql> INSERT INTO test(v1,v2,v3) VALUES(6,6,6);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

v1=6处于间隙之中,通过查询当前锁情况:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> SELECT * FROM information_schema.INNODB_LOCKS\G
*************************** 1. row ***************************
lock_id: 381798:1437:4:6
lock_trx_id: 381798
lock_mode: X,GAP
lock_type: RECORD
lock_table: `learn`.`test`
lock_index: v1
lock_space: 1437
lock_page: 4
lock_rec: 6
lock_data: 100
*************************** 2. row ***************************
lock_id: 381797:1437:4:6
lock_trx_id: 381797
lock_mode: X,GAP
lock_type: RECORD
lock_table: `learn`.`test`
lock_index: v1
lock_space: 1437
lock_page: 4
lock_rec: 6
lock_data: 100

后来事务B需要等待先来事务A释放这一区间上的GAP锁,最终锁等待超时。

快照读

通过锁定,是可以实现RR事务隔离级别的。关键在于,对于需要改动的数据或者数据范围,不要过早的释放锁,在事务完成之后才进行释放。

简单读取操作是S锁,其他操作可以是X锁,S锁和X锁不能同时存在。

《事务处理原理》一书中提到两阶段锁定(2PL,two-phase locking),即一个事务必须在释放它的任何锁之前获得它所有的锁。如果用锁定的方式实现RR,在整个事务中,其他事务的简单读操作也会被阻塞。

InnoDB实现了MVCC的方式,可以实现事务中简单读不需锁定的效果,即实现了快照读。

参考

ANSI SQL-92
Innodb中的事务隔离级别和锁的关系
MySQL 加锁处理分析
InnoDB’s gap locks
深入分析事务的隔离级别
MySQL 5.7 Reference Manual / 14.5.1 InnoDB Locking