InnoDB的锁机制浅析(All in One)
InnoDB的锁机制浅析
本文是大而全版（五合一）：InnoDB的锁机制浅析(All in One)
如果想分开看可以点击下面链接：
文章总共分为五个部分：
- InnoDB的锁机制浅析(一)—基本概念/兼容矩阵
- InnoDB的锁机制浅析(二)—探索InnoDB中的锁(Record锁/Gap锁/Next-key锁/插入意向锁)
- InnoDB的锁机制浅析(三)—幻读
- InnoDB的锁机制浅析(四)—不同SQL的加锁状况
- InnoDB的锁机制浅析(五)—死锁场景(Insert死锁)
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,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 code=5 for update; | select * from test where code=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隔离级别下出现死锁之外还会导致其他两个问题：
- 第一个是可重复读可能会因为这次的当前读操作而中断，(同样，幻读可能也会因此产生)；
- 第二个是其他事务的更新可能会丢失(解决方式:悲观锁、乐观锁)。
