InnoDB的锁机制深入理解

共计 17910 个字符，预计需要花费 45 分钟才能阅读完成。

1. 前言

数据事务设计遵循 ACID 的原则。

MySQL 数据库提供了四种默认的隔离级别，读未提交(read-uncommitted)、读已提交(或不可重复读)(read-committed)、可重复读(repeatable-read)、串行化(serializable)。

MySQL 的默认隔离级别是 RR。

2. 锁基本概念

2.1 共享锁和排它锁

InnoDB 实现了两种标准行级锁，一种是共享锁(shared locks，S 锁)，另一种是独占锁，或者叫排它锁(exclusive locks，X 锁)。

S 锁允许当前持有该锁的事务读取行。
X 锁允许当前持有该锁的事务更新或删除行。

S 锁

如果事务 T1 持有了行 r 上的 S 锁，则其他事务可以同时持有行 r 的 S 锁，但是不能对行 r 加 X 锁。

X 锁

如果事务 T1 持有了行 r 上的 X 锁，则其他任何事务不能持有行 r 的 X 锁，必须等待 T1 在行 r 上的 X 锁 释放。

如果事务 T1 在行 r 上保持 S 锁，则另一个事务 T2 对行 r 的锁的请求按如下方式处理：

• T2 可以同时持有 S 锁
• T2 如果想在行 r 上获取 X 锁，必须等待其他事务对该行添加的 S 锁 或 X 锁 的释放。

2.2 意向锁 -Intention Locks

InnoDB 支持多种粒度的锁，允许行级锁和表级锁的共存。例如 LOCK TABLES ... WRITE 等语句可以在指定的表上加上独占锁。
InnoBD 使用意向锁来实现多个粒度级别的锁定。意向锁是表级锁，表示 table 中的 row 所需要的锁 (S 锁或 X 锁) 的类型。

意向锁分为意向共享锁 (IS 锁) 和意向排它锁(IX 锁)。
IS 锁表示当前事务意图在表中的行上设置共享锁，下面语句执行时会首先获取 IS 锁，因为这个操作在获取 S 锁：

SELECT ... LOCK IN SHARE MODE

IX 锁表示当前事务意图在表中的行上设置排它锁。下面语句执行时会首先获取 IX 锁，因为这个操作在获取 X 锁：

SELECT ... FOR UPDATE

事务要获取某个表上的 S 锁和 X 锁之前，必须先分别获取对应的 IS 锁和 IX 锁。

2.3 锁的兼容性

锁的兼容矩阵如下：

—	排它锁(X)	意向排它锁(IX)	共享锁(S)	意向共享锁(IS)
排它锁(X)	N	N	N	N
意向排它锁(IX)	N	OK	N	OK
共享锁(S)	N	N	OK	OK
意向共享锁(IS)	N	OK	OK	OK

按照上面的兼容性，如果不同事务之间的锁兼容，则当前加锁事务可以持有锁，如果有冲突则会等待其他事务的锁释放。

如果一个事务请求锁时，请求的锁与已经持有的锁冲突而无法获取时，互相等待就可能会产生死锁。

意向锁不会阻止除了全表锁定请求之外的任何锁请求。
意向锁的主要目的是显示事务正在锁定某行或者正意图锁定某行。

3. InnoDB 中的锁

常见的锁有 Record 锁、gap 锁、next-key 锁、插入意向锁、自增锁等。
下面会对每一种锁给出一个查看锁的示例。

3.1 准备工作

3.1.1 测试用表结构

示例的基础是一个只有两列的数据库表。

mysql> CREATE TABLE test (id int(11) NOT NULL,
code int(11) NOT NULL, 
PRIMARY KEY(id), 
KEY (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 

mysql> INSERT INTO test(id,code) values(1,1),(10,10);

数据表 test 只有两列，id是主键索引，code是普通的索引 (注意，一定不要是唯一索引)，并初始化了两条记录，分别是(1,1),(10,10)。
这样，我们验证唯一键索引就可以使用 id 列，验证普通索引 (非唯一键二级索引) 时就使用 code 列。

3.1.2 查看锁状态的方式

要看到锁的情况，必须手动开启多个事务，其中一些锁的状态的查看则必须使锁处于 waiting 状态，这样才能在 mysql 的引擎状态日志中看到。

命令：

mysql> show engine innodb status;

这条命令能显示最近几个事务的状态、查询和写入情况等信息。当出现死锁时，命令能给出最近的死锁明细。

3.2 记录锁 Record Locks

Record 锁

Record Lock是对 索引记录 的锁定。记录锁有两种模式，S 模式和 X 模式。
例如 SELECT id FROM test WHERE id = 10 FOR UPDATE; 表示防止任何其他事务插入、更新或者删除 id =10 的行。

记录锁始终只锁定索引。即使表没有建立索引，InnoDB 也会创建一个隐藏的聚簇索引(隐藏的递增主键索引)，并使用此索引进行记录锁定。

查看记录锁

开启第一个事务，不提交，测试完之后回滚。

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

mysql> update test set id=2 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

事务加锁情况

mysql> show engine innodb status\G;
... 
------------
TRANSACTIONS
------------
---TRANSACTION 366811, ACTIVE 690 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
MySQL thread id 785, OS thread handle 123145432457216, query id 729076 localhost 127.0.0.1 root
...

可以看到有一行被加了锁。由之前对锁的描述可以推测出，update 语句给 id=1 这一行上加了一个 X 锁。

注意：X 锁广义上是一种抽象意义的排它锁，即锁一般分为 X 模式 和 S 模式 ，狭义上指 row 或者 index 上的锁，而 Record 锁是索引上的锁。
为了不修改数据，可以用 select ... for update 语句，加锁行为和 update、delete 是一样的，insert加锁机制较为复杂，后面的章节会提到。

第一个事务保持原状，不要提交或者回滚，现在开启第二个事务。

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

mysql> update test set id=3 where id=1;

执行 update 时，sql 语句的执行被阻塞了。查看下事务状态:

mysql> show engine innodb status\G;
...
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 62 page no 3 n bits 72 index PRIMARY of table `test`.`test` trx id 366820 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex 0000000000000001; asc         ;;
 1: len 6; hex 0000000598e3; asc       ;;
 2: len 7; hex 7e000001a80896; asc ~      ;;

------------------
...

喜闻乐见，我们看到了这个锁的状态。状态标题是 ’ 事务正在等待获取锁 ’，描述中的 lock_mode X locks rec but not gap 就是本章节中的 record 记录锁，直译一下 ’X 锁模式锁住了记录 ’。后面还有一句 but not gap 意思是只对 record 本身加锁，并不对间隙加锁，间隙锁的叙述见下一个章节。

3.3 间隙锁 Gap Locks

间隙锁

间隙锁作用在索引记录之间的间隔，又或者作用在第一个索引之前，最后一个索引之后的间隙。不包括索引本身。
例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;这条语句阻止其他事务插入 10 和 20 之间的数字，无论这个数字是否存在。

间隙可以跨越 0 个，单个或多个索引值。

间隙锁是性能和并发权衡的产物，只存在于部分事务隔离级别。

select * from table where id=1;
唯一索引可以锁定一行，所以不需要间隙锁锁定。
如果列没有索引或者具有非唯一索引，该语句会锁定当前索引前的间隙。

在同一个间隙上，不同的事务可以持有上述兼容 / 冲突表中冲突的两个锁。例如，事务 T1 现在持有一个间隙 S 锁，T2 可以同时在同一个间隙上持有间隙 X 锁。
允许冲突的锁在间隙上锁定的原因是，如果从索引中清除一条记录，则由不同事务在这条索引记录上的加间隙锁的动作必须被合并。

InnoDB 中的间隙锁的唯一目的是防止其他事务插入间隙。
间隙锁是可以共存的，一个事务占用的间隙锁不会阻止另一个事务获取同一个间隙上的间隙锁。

如果事务隔离级别改为 RC，则间隙锁会被禁用。

查看间隙锁

按照官方文档，where子句查询条件是唯一键且指定了值时，只有 record 锁，没有 gap 锁。
如果 where 语句指定了范围，gap 锁是存在的。
这里只测试验证一下当指定非唯一键索引的时候，gap 锁的位置，按照文档的说法，会锁定当前索引及索引之前的间隙。(指定了非唯一键索引, 例��code=10，间隙锁仍然存在)

开启第一个事务，锁定一条非唯一的普通索引记录

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

mysql> select * from test where code = 10 for update;
+----+------+
| id | code |
+----+------+
| 10 |   10 |
+----+------+
1 row in set (0.00 sec)

由于预存了两条数据，row(1,1)和 row(10,10)，此时这个间隙应该是 1<gap<10。我们先插入 row(2,2) 来验证下 gap 锁的存在，再插入 row(0,0)来验证 gap 的边界。

按照间隙锁的官方文档定义，select * from test where code = 10 for update;会锁定 code=10 这个索引，并且会锁定 code<10 的间隙。

开启第二个事务，在 code=10 之前的间隙中插入一条数据，看下这条数据是否能够插入。

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

mysql> insert into test values(2,2);

插入的时候，执行被阻塞，查看引擎状态：

mysql> show engine innodb status\G;
...
---TRANSACTION 366864, ACTIVE 5 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 793, OS thread handle 123145434963968, query id 730065 localhost 127.0.0.1 root update
insert into test values(2,2)
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 63 page no 4 n bits 72 index code of table `test`.`test` trx id 366864 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 8; hex 000000000000000a; asc         ;;

------------------
...

插入语句被阻塞了，lock_mode X locks gap before rec，由于第一个事务锁住了 1 到 10 之间的 gap，需要等待获取锁之后才能插入。

如果再开启一个事务，插入(0,0)

mysql> start transaction;
mysql> insert into test values(0,0);
Query OK, 1 row affected (0.00 sec)

可以看到：指定的非唯一建索引的 gap 锁的边界是当前索引到上一个索引之间的 gap。

最后给出锁定区间的示例, 首先插入一条记录(5,5)

mysql> insert into test values(5,5);
Query OK, 1 row affected (0.00 sec)

开启第一个事务:

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

mysql> select * from test where code between 1 and 10 for update;
+----+------+
| id | code |
+----+------+
|  1 |    1 |
|  5 |    5 |
| 10 |   10 |
+----+------+
3 rows in set (0.00 sec)

第二个事务，试图去更新 code= 5 的行：

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

mysql> update test set code=4 where code=5;

执行到这里，如果第一个事务不提交或者回滚的话，第二个事务一直等待直至 mysql 中设定的超时时间。

3.4 Next-key Locks

Next-key 锁

Next-key 锁实际上是 Record 锁和 gap 锁的组合。Next-key 锁是在下一个索引记录本身和索引之前的 gap 加上 S 锁或是 X 锁 (如果是读就加上 S 锁，如果是写就加 X 锁)。
默认情况下，InnoDB 的事务隔离级别为 RR，系统参数 innodb_locks_unsafe_for_binlog 的值为 false。InnoDB 使用 next-key 锁对索引进行扫描和搜索，这样就读取不到幻象行，避免了 幻读 的发生。

幻读是指在同一事务下，连续执行两次同样的 SQL 语句，第二次的 SQL 语句可能会返回之前不存在的行。

当查询的索引是唯一索引时，Next-key lock 会进行优化，降级为 Record Lock，此时 Next-key lock 仅仅作用在索引本身，而不会作用于 gap 和下一个索引上。

查看 Next-key 锁

Next-key 锁的作用范围

如上述例子，数据表 test 初始化了 row(1,1),row(10,10)，然后插入了 row(5,5)。数据表如下：

mysql> select * from test;
+----+------+
| id | code |
+----+------+
|  1 |    1 |
|  5 |    5 |
| 10 |   10 |
+----+------+
3 rows in set (0.00 sec)

由于 id 是主键、唯一索引，mysql 会做优化，因此使用 code 这个非唯一键的二级索引来举例说明。

对于code，可能的 next-key 锁的范围是：

(-∞,1]
(1,5]
(5,10]
(10,+∞)

开启第一个事务，在 code=5 的索引上请求更新:

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

mysql> select * from test where code=5 for update;
+----+------+
| id | code |
+----+------+
|  5 |    5 |
+----+------+
1 row in set (8.81 sec)

之前在 gap 锁的章节中介绍了，code=5 for update会在 code=5 的索引上加一个 record 锁，还会在 1 <gap<5 的间隙上加 gap 锁。现在不再验证，直接插入一条(8,8)：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values(8);

insert处于等待执行的状态，这就是 next-key 锁 生效而导致的结果。第一个事务，锁定了区间 (1,5]，由于 RR 的隔离级别下next-key 锁 处于开启生效状态，又锁定了 (5,10] 区间。所以插入 SQL 语句的执行被阻塞。

解释：在这种情况下，被锁定的区域是 code=5 前一个索引到它的间隙，以及 next-key 的区域。code=5 for update对索引的锁定用区间表示，gap 锁锁定了 (1,5)，record 锁锁定了{5} 索引记录，next-key 锁锁住了 (5,10]，也就是说整个(1,10] 的区间被锁定了。由于是for update，所以这里的锁都是 X 锁，因此阻止了其他事务中带有冲突锁定的操作执行。

如果我们在第一个事务中，执行了 code>8 for update，在扫描过程中，找到了code=10，此时就会锁住 10 之前的间隙(5 到 10 之间的 gap)，10 本身(record)，和 10 之后的间隙(next-key)。此时另一个事务插入(6,6),(9,9) 和(11,11)都是不被允许的，只有在前一个索引 5 及 5 之前的索引和间隙才能执行插入(更新和删除也会被阻塞)。

3.5 插入意向锁 Insert Intention Locks

插入意向锁在行插入之前由 INSERT 设置一种间隙锁，是意向排它锁的一种。
在多事务同时写入不同数据至同一索引间隙的时，不会发生锁等待，事务之间互相不影响其他事务的完成，这和间隙锁的定义是一致的。

假设一个记录索引包含 4 和 7，其他不同的事务分别插入 5 和 6，此时只要行不冲突，插入意向锁不会互相等待，可以直接获取。参照锁兼容 / 冲突矩阵。
插入意向锁的例子不再列举，可以查看 gap 锁的第一个例子。

3.6 自增锁

自增锁 (AUTO-INC Locks) 是事务插入时自增列上特殊的 表级别 的锁。最简单的一种情况：如果一个事务正在向表中插入值，则任何其他事务必须等待，以便第一个事务插入的行接收连续的主键值。

我们一般把主键设置为 AUTO_INCREMENT 的列，默认情况下这个字段的值为 0，InnoDB 会在 AUTO_INCREMENT 修饰下的数据列所关联的索引末尾设置独占锁。在访问自增计数器时，InnoDB 使用自增锁，但是锁定仅仅持续到当前 SQL 语句的末尾，而不是整个事务的结束，毕竟自增锁是表级别的锁，如果长期锁定会大大降低数据库的性能。由于是表锁，在使用期间，其他会话无法插入表中。

4 幻读

这一章节，我们通过幻读，逐步展开对 InnoDB 锁的探究。

4.1 幻读概念

解释了不同概念的锁的作用域，我们来看一下幻读到底是什么。幻读在 RR 条件下是不会出现的。因为 RR 是 Repeatable Read，它是一种事务的隔离级别，直译过来也就是“在同一个事务中，同样的查询语句的读取是可重复”，也就是说他不会读到”幻影行”(其他事务已经提交的变更)，它读到的只能是重复的(无论在第一次查询之后其他事务做了什么操作，第二次查询结果与第一次相同)。

上面的例子都是使用 for update，这种读取操作叫做当前读，对于普通的select 语句均为快照读。

当前读，又叫加锁读，或者 阻塞读。这种读取操作不再是读取快照，而是读取最新版本并且加锁。
快照读不会添加任何锁。

官方文档对于幻读的定义是这样的：

原文：The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a“phantom”row.
手动无脑翻译：所谓的幻影行问题是指，在同一个事务中，同样的查询语句执行多次，得到了不同的结果，这就是幻读。例如，如果同一个 SELECT 语句执行了两次，第二次执行的时候比第一次执行时多出一行，则该行就是所谓的幻影行。

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times.，这句话看起来应该是不可重复读的定义，同样的查询得到了不同的结果 (两次结果不是重复的)，但是后面的举例给出了幻读真正的定义，第二次比第一次多出了一行。也就是说，幻读的出现有这样一个前提，第二次查询前其他事务提交了一个INSERT 插入语句。而不可重复读出现的前提是第二次查询前其他事务提交了 UPDATE 或者 DELETE 操作。

mysql 的快照读，使得在 RR 的隔离级别上在 next-Key 的作用区间内，制造了一个快照副本，这个副本是隔离的，无论副本对应的区间里的数据被其他事务如何修改，在当前事务中，取到的数据永远是副本中的数据。
RR 级别下之所以可以读到之前版本的数据，是由于数据库的 MVCC(Multi-Version Concurrency Control，多版本并发控制)。参见 InnoDB Multi-Versioning

有些文章中提到“RR 也不能完全避免幻读”，实际上官方文档实际要表达的意义是“在同一个事务内，多次连续查询 的结果是一样的，不会因其他事务的修改而导致不同的查询结果”，这里先给出实验结论：

1. 当前事务如果未发生更新操作(增删改)，快照版本会保持不变，多次查询读取的副本是同一个。
2. 当前事务如果发生更新(增删改)，再次查询时，会刷新快照版本。

4.2 RC 级别下的幻读

RC 情况下会出现幻读。
首先设置隔离级别为 RC,SET SESSION tx_isolation='READ-COMMITTED';

事务一	事务二
mysql> SET SESSION tx_isolation=’READ-COMMITTED’; mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from test where code > 8; +—-+——+ | id | code | +—-+——+ | 10 | 10 | +—-+——+ 1 row in set (0.01 sec)
	mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values(9,9); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec)
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from test where code > 8; +—-+——+ | id | code | +—-+——+ | 9 | 9 | +—-+——+ | 10 | 10 | +—-+——+ 1 row in set (0.01 sec)

RC(Read Commit)隔离级别可以避免脏读，事务内无法获取其他事务未提交的变更，但是由于能够读到已经提交的事务，因此会出现幻读和不重复读。
也就是说，RC 的快照读是读取最新版本数据，而 RR 的快照读是读取被 next-key 锁作用区域的副本

4.3 RR 级别下能否避免幻读？

我们先来模拟一下 RR 隔离级别下没有出现幻读的情况：

开启第一个事务并执行一次快照查询。

事务一	事务二
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from test where code > 8; +—-+——+ | id | code | +—-+——+ | 10 | 10 | +—-+——+ 1 row in set (0.01 sec)
	mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values(9,9); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec)
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from test where code > 8; +—-+——+ | id | code | +—-+——+ | 10 | 10 | +—-+——+ 1 row in set (0.01 sec)

这两个事务的执行，有两个问题：

1. 为什么之前的例子中，在第二个事务的 INSERT 被阻塞了，而这次却执行成功了。
这是因为原来的语句中带有 for update，这种读取是当前读，会加锁。而本次第一个事务中的SELECT 仅仅是快照读，没有加任何锁。所以不会阻塞其他的插入。

2. 数据库中的数据已经改变，为什么会读不到？
这个就是之前提到的 next-key lock 锁定的副本。RC 及以下级别才会读到已经提交的事务。更多的业务逻辑是希望在某段时间内或者某个特定的逻辑区间中，前后查询到的数据是一致的，当前事务是和其他事务隔离的。这也是数据库在设计实现时遵循的 ACID 原则。

再给出 RR 条件下出现幻读的情形，这种情形不需要两个事务，一个事务就已经可以说明，

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

mysql> select * from test where id>8;
+----+------+
| id | code |
+----+------+
| 10 |   10 |
+----+------+
1 row in set (0.00 sec)

mysql> update test set code=9 where id=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test where id>8;
+----+------+
| id | code |
+----+------+
| 10 |    9 |
+----+------+
1 row in set (0.00 sec)

至于 RR 隔离级别下到底会不会出现幻读，就需要看幻读的定义中的查询到底是连续的查询还是不连续的查询。如果认为 RR 级别下可能会出现幻读，那该级别下也会出现不重复读。

---

RR 隔离级别下，虽然不会出现幻读，但是会因此产生其他的问题。
前提：当前数据表中只存在 (1,1),(5,5),(10,10) 三组数据。

如果数据库隔离级别不是默认，可以执行 SET SESSION tx_isolation='REPEATABLE-READ';(该语句不是全局设置) 更新为 RR。

然后执行下列操作:

事务一	事务二	备注
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from test where code > 8; +—-+——+ | id | code | +—-+——+ | 10 | 10 | +—-+——+ 1 row in set (0.01 sec)		开启事务一，并查询 code>8 的记录，只有一条(10,10)
	mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values(11,11); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec)	开启第二个事务，插入 (11,11) 并提交
mysql> select * from test where code > 8; +—-+——+ | id | code | +—-+——+ | 10 | 10 | +—-+——+ 1 row in set (0.01 sec)		事务一再查询一次，由于 RR 级别并没有读到更新
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values(11,11); ERROR 1062 (23000): Duplicate entry ’11’ for key ‘PRIMARY’		事务一明明没有查到，却插入不了

4.5 更新丢失(Lost Update)

4.5.1 更新丢失

除了上述这类问题外，RR 还会有丢失更新的问题。
如下表给出的操作：

事务一	事务二	备注
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from test where code > 8; +—-+——+ | id | code | +—-+——+ | 10 | 10 | +—-+——+ 1 row in set (0.01 sec)		开启事务一，并查询 code>8 的记录，只有一条(10,10)
	mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update test set id=12,code=12 where id=10; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.00 sec)	开启第二个事务，将 (10,10) 改为 (12,12) 并提交，注意这里 matched 是 1，changed 也是 1
mysql> select * from test where code > 8; +—-+——+ | id | code | +—-+——+ | 10 | 10 | +—-+——+ 1 row in set (0.01 sec)		事务一再次查询 code>8 的记录，仍然只有一条(10,10)
mysql> update test set id=9,code=9 where id=10; Query OK, 0 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0		这里查询到 0 条，更新了 0 条

这个例子里，事务一的更新是无效的，尽管在这个事务里程序认为还存在 (10,10) 记录。
事务一中更新之前的 SELECT 操作是快照读，所以读到了快照里的 (10,10)，而UPDATE 中的 WHERE 子句是当前读，取得是最新版本的数据，所以matched: 0 Changed: 0。

如果上述例子中的操作是对同一条记录做修改，就会引起更新丢失。例如，事务一和二同时开启，事务一先执行update test set code=100 where id=10;，事务二再执行update test set code=200 where id=10;，事务一的更新就会被覆盖。

这就是经典的丢失更新问题，英文叫Lost Update，又叫提交覆盖，因为是最后执行更新的事务提交导致的覆盖。还有一种更新丢失叫做回滚覆盖，即一个事务的回滚把另一个事务提交的数据给回滚覆盖了，但是目前市面上所有的数据库都不支持这种 stupid 的操作，因此不再详述。

4.5.2 乐观锁与悲观锁

这种情况下，引入我们常见的两种方式来解决该问题

• 乐观锁 ：在UPDATE 的WHERE子句中加入版本号信息来确定修改是否生效
• 悲观锁 ：在UPDATE 执行前，SELECT后面加上 FOR UPDATE 来给记录加锁，保证记录在 UPDATE 前不被修改。SELECT ... FOR UPDATE是加上了 X 锁，也可以通过 SELECT ... LOCK IN SHARE MODE 加上 S 锁，来防止其他事务对该行的修改。

无论是乐观锁还是悲观锁，使用的思想都是一致的，那就是当前读。乐观锁利用 当前读 判断是否是最新版本，悲观锁利用 当前读 锁定行。
但是使用乐观锁时仍然需要非常谨慎，因为 RR 是可重复读的，一定不能在 UPDATE 之前先把版本号读取出来。

5. InnoDB 对不同语句执行时的加锁状况

如果一个 SQL 语句要对二级索引 (非主键索引) 设置 X 模式的 Record 锁，InnoDB 还会检索出相应的聚簇索引 (主键索引) 并对它们设置锁定。

5.1 SELECT … FROM… 不加锁

SELECT ... FROM是快照读取，除了 SERIALIZABLE 的事务隔离级别，该 SQL 语句执行时不会加任何锁。

SERIALIZABLE级别下，SELECT语句的执行会在遇到的索引记录上设置 S 模式的 next-key 锁。但是对于唯一索引，只锁定索引记录，而不会锁定 gap。

5.2 UPDATE 系列

S 锁读取 (SELECT ... LOCK IN SHARE MODE)，X 锁读取(SELECT ... FOR UPDATE)、更新UPDATE 和删除 DELETE 这四类语句，采用的锁取决于搜索条件中使用的索引类型。

• 如果使用唯一索引，InnoDB 仅锁定索引记录本身，不锁定间隙。
• 如果使用非唯一索引，或者未命中索引，InnoDB 使用间隙锁或者 next-key 锁来锁定索引范围，这样就可以阻止其他事务插入锁定范围。

5.2.1 UPDATE 语句

UPDATE ... WHERE ...在搜索遇到的每条记录上设置一个独占的 next-key 锁，如果是唯一索引只锁定记录。
当 UPDATE 修改聚簇索引时，将对受影响的二级索引采用隐式锁，隐式锁是在索引中对二级索引的记录逻辑加锁，实际上不产生锁对象，不占用内存空间。

例如 update test set code=100 where id=10; 执行的时候 code=10 的索引 (code 是二级索引，见文中给出的建表语句) 会被加隐式锁，只有隐式锁产生冲突时才会变成显式锁 (如 S 锁、X 锁)。即此时另一个事务也去更新id=10 这条记录，隐式锁就会升级为显示锁。
这样做的好处是降低了锁的开销。

UPDATE可能会导致新的普通索引的插入。当新的索引插入之前，会首先执行一次 重复索引检查。在重复检查和插入时，更新操作会对受影响的二级索引记录采用共享锁定(S 锁)。

5.2.2 DELETE 语句

DELETE FROM ... WHERE ...在搜索遇到的每条记录上设置一个独占的 next-key 锁, 如果是唯一索引只锁定记录。

5.3 INSERT

INSERT区别于 UPDATE 系列单独列出，是因为它的处理方式较为特别。

插入行之前，会设置一种插入意向锁，插入意向锁表示插入的意图。如果其它事务在要插入的位置上设置了 X 锁，则无法获取插入意向锁，插入操作也因此阻塞。

INSERT在插入的行上设置 X 锁。该锁是一个 Record 锁，并不是 next-key 锁，即只锁定记录本身，不锁定间隙，因此不会阻止其他会话在这行记录前的间隙中插入新的记录。
具体的加锁过程，见 6.2。

6. 可能的死锁场景

6.1 Duplicate key error 引发的死锁

并发条件下，唯一键索引冲突可能会导致死锁，这种死锁一般分为两种，一种是 rollback 引发，另一种是 commit 引发。

6.1.1 rollback引发的 Duplicate key 死锁

我命名为 insert-insert-insert-rollback 死锁

事务一	事务二	事务三
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values (2,2); Query OK, 1 row affected (0.01 sec)
	mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values (2,2); 执行之后被阻塞，等待事务一
		mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values (2,2); 执行之后被阻塞，等待事务一
mysql>rollback; Query OK, 0 rows affected (0.00 sec)
	ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
		Query OK, 1 row affected (16.13 sec)

当事务一执行回滚时，事务二和事务三发生了死锁。InnoDB 的死锁检测一旦检测到死锁发生，会自动失败其中一个事务，因此看到的结果是一个失败另一个成功。

为什么会死锁？

死锁产生的原因是事务一插入记录时，对 (2,2) 记录加 X 锁，此时事务二和事务三插入数据时检测到了重复键错误，此时事务二和事务三要在这条索引记录上设置 S 锁，由于 X 锁的存在，S 锁的获取被阻塞。
事务一回滚，由于 S 锁和 S 锁是可以兼容的，因此事务二和事务三都获得了这条记录的 S 锁，此时其中一个事务希望插入，则该事务期望在这条记录上加上 X 锁，然而另一个事务持有 S 锁，S 锁和 X 锁互相是不兼容的，两个事务就开始互相等待对方的锁释放，造成了死锁。

事务二和事务三为什么会加 S 锁，而不是直接等待 X 锁

事务一的 insert 语句加的是隐式锁 (隐式的 Record 锁、X 锁)，但是其他事务插入同一行记录时，出现了唯一键冲突，事务一的隐式锁升级为显示锁。
事务二和事务三在插入之前判断到了唯一键冲突，是因为插入前的 重复索引检查 ，这次检查必须进行一次当前读，于是非唯一索引就会被加上 S 模式的 next-key 锁，唯一索引就被加上了 S 模式的 Record 锁。
因为插入和更新之前都要进行重复索引检查而执行当前读操作，所以 RR 隔离级别下，同一个事务内不连续的查询，可能也会出现幻读的效果(但个人并不认为 RR 级别下也会出现幻读，幻读的定义应该是连续的读取)。而连续的查询由于都是读取快照，中间没有当前读的操作，所以不会出现幻读。

6.1.2 commit引发的 Duplicate key 死锁

delete-insert-insert-commit 死锁

事务一	事务二	事务三
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> delete from test where id=2; Query OK, 1 row affected (0.01 sec)
	mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values (2,2); 执行之后被阻塞，等待事务一
		mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values (2,2); 执行之后被阻塞，等待事务一
mysql>commit; Query OK, 0 rows affected (0.00 sec)
	ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
		Query OK, 1 row affected (2.37 sec)

这种情况下产生的死锁和 insert-insert-insert-rollback 死锁产生的原理一致。

6.2 数据插入的过程

经过以上分析，一条数据在插入时经过以下几个过程：
假设数据表 test.test 中存在 (1,1)、(5,5) 和(10,10)三条记录。

• 事务开启，尝试获取插入意向锁。例如，事务一执行了select * from test where id>8 for update，事务二要插入(9,9)，此时先要获取插入意向锁，由于事务一已经在对应的记录和间隙上加了 X 锁，因此事务二被阻塞，并且阻塞的原因是获取插入意向锁时被事务一的 X 锁阻塞。
• 获取意向锁之后，插入之前进行重复索引检查。重复索引检查为当前读，需要添加 S 锁。
• 如果是已经存在唯一索引，且索引未加锁。直接抛出 Duplicate key 的错误。如果存在唯一索引，且索引加锁，等待锁释放。
• 重复检查通过之后，加入 X 锁，插入记录

6.3 GAP 与 Insert Intention 冲突引发死锁

update-insert 死锁

仍然是表 test，当前表中的记录如下:

mysql> select * from test;
+----+------+
| id | code |
+----+------+
|  1 |    1 |
|  5 |    5 |
| 10 |   10 |
+----+------+
3 rows in set (0.01 sec)

事务一	事务二
begin;	begin;
select * from test where id=5 for update;	select * from test where id=10 for update;
insert into test values(7,7);
	insert into test values(7,7);
Query OK, 1 row affected (5.03 sec)
	ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

使用 show engine innodb status 查看死锁状态。先后出现 lock_mode X locks gap before rec insert intention waiting 和lock_mode X locks gap before rec字眼，是 gap 锁和插入意向锁的冲突导致的死锁。

回顾 select…for update 的加锁范围

首先回顾一下两个事务中的 select ... for update 做了哪些加锁操作。

code=5时，首先会获取 code=5 的索引记录锁 (Record 锁)，根据之前 gap 锁的介绍，会在前一个索引和当前索引之间的间隙加锁，于是区间(1,5) 之间被加上了 X 模式的 gap 锁。除此之外 RR 模式下，还会加 next-key 锁，于是区间(5,10] 被加了next-key 锁；

• 因此，code=5的加锁范围是，区间 (1,5) 的 gap 锁，{5}索引 Record 锁，(5,10]的 next-key 锁。即区间 (1,10) 上都被加上了 X 模式的锁。
• 同理，code=10的加锁范围是，区间 (5,10) 的 gap 锁,{10}索引 Record 锁，（10,+∞)的 next-key 锁。

由 gap 锁的特性，兼容矩阵中冲突的锁也可以被不同的事务同时加在一个间隙上。上述两个 select ... for update 语句出现了间隙锁的交集，code=5的 next-key 锁和 code=10 的 gap 锁有重叠的区域——(5,10)。

死锁的成因

当事务一执行插入语句时，会先加 X 模式的插入意向锁，即兼容矩阵中的 IX 锁。
但是由于插入意向锁要锁定的位置存在 X 模式的 gap 锁。兼容矩阵中 IX 和 X 锁是不兼容的，因此事务一的 IX 锁会等待事务二的 gap 锁释放。

事务二也执行插入语句，与事务一同样，事务二的插入意向锁 IX 锁会等待事务一的 gap 锁释放。

两个事务互相等待对方先释放锁，因此出现死锁。

7 总结

除了以上给出的几种死锁模式，还有很多其他死锁的场景。
无论是哪种场景，万变不离其宗，都是由于某个区间上或者某一个记录上可以同时持有锁，例如不同事务在同一个间隙 gap 上的锁不冲突；不同事务中，S 锁可以阻塞 X 锁的获取，但是不会阻塞另一个事务获取该 S 锁。这样才会出现两个事务同时持有锁，并互相等待，最终导致死锁。

其中需要注意的点是，增、删、改的操作都会进行一次当前读操作，以此获取最新版本的数据，并检测是否有重复的索引。
这个过程除了会导致 RR 隔离级别下出现死锁之外还会导致其他两个问题：

• 第一个是可重复读可能会因为这次的当前读操作而中断，(同样，幻读可能也会因此产生)；
• 第二个是其他事务的更新可能会丢失(解决方式: 悲观锁、乐观锁)。

：