MySQL锁读这篇就够

MySQL语句加锁分析 2. 锁实现方式： 3. RR隔离级别下： Read vs Read4. 加锁过程分析

前言：锁是为了解决并发带来问题而设计的, 阅读本文需要知道数据页 和 B+tree的知识

可以参考这篇文章：Mysql索引调优

1. 锁的基本模式 1.1 共享锁S和排他锁X

  兼容性:是指事务A获得一个某行某种锁之后，事务B同样的在这个行上尝试获取某种锁，如果能立即获取，则称锁兼容，反之叫冲突。

锁模式的兼容性汇总在以下矩阵中：

XS

冲突

冲突

冲突

兼容

1.2 意向锁(表锁)

意向锁是表级锁，指事务稍后对表中的行需要加哪种类型的锁（共享锁或排他锁）

有两种类型的意向锁：

支持多种粒度锁，允许行锁和表锁并存。为了使多个粒度级别上的锁变得切实可行，使用了意向锁。

加入意向锁的目的：意向锁仅仅用于表锁和行锁的共存使用，意向锁是为了提高锁的兼容性判断效率。如果我们的操作仅仅涉及行锁，那么意向锁不会对我们的操作产生任何影响。在任一操作给表A的一行记录加锁前，首先要给该表加意向锁，如果获得了意向锁，然后才会加行锁，并在加行锁时判断是否冲突。如果现在有一个操作要获得表A的表锁，由于意向锁的存在，表锁获取会失败（如果没有意向锁的存在，加表锁之前可能要遍历整个聚簇索引，判断是否有行锁存在，如果没有行锁才能加表锁）。

同理，如果某一操作已经获得了表A的表锁，那么另一操作获得行锁之前，首先会检查是否可以获得意向锁，并在获得意向锁失败后，等待表锁操作的完成。也就是说：

意向锁是表级锁，但是却表示事务正在读或写某一行记录；意向锁之间不会冲突, 因为意向锁仅仅代表要对某行记录进行操作，在加行锁时，会判断是否冲突；意向锁是自动加的，不需用户干预。

锁模式的兼容性汇总在以下矩阵中，请注意这里的 X、S 是表锁（共享表锁、排他表锁）

冲突

冲突

冲突

冲突

IX

冲突

兼容

冲突

兼容

冲突

冲突

兼容

兼容

IS

冲突

兼容

兼容

兼容

2. 锁实现方式： 2.1 记录锁（ Lock）

执行行级锁定的方式是，当它搜索或扫描表索引时，会在遇到的索引记录上加共享锁或排他锁。因此，行级锁实际上是索引记录锁。

例如， id FROM t WHERE id = 10 FOR ; 就是对id = 10的记录加上X锁，可以防止其它事务对id = 10 这条记录进行插入，更新或删除行

记录锁始终锁定索引记录，即使没有定义索引的表也是如此。在这种情况下，请 创建一个隐藏的聚集索引，并将该索引用于记录锁定。（每张表，会默认建立主键索引）

2.2 间隙锁(Gap Lock)

间隙锁是对索引记录之间的锁定。例如， id FROM t WHERE id 10 and 20 FOR ;阻止其他事务将id=15记录插表中，无论该表中是否已经存在这样的值，因为该范围中所有现有索引记录之间的间隙都被锁定。

2.3下一键锁（Next Key Lock）

Next Key Lock本质是 Lock + Gap Lock的组合。即，Next Key Lock锁定是索引记录+索引记录之前的间隙。

假定索引记录包含的Key值10、11、13和20。此索引的可能的Next Key Lock涵盖以下间隔

(negative infinity, 10]  
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

最后一个间隔(20, )，是靠伪记录来实现的。

2.4 插入意向锁（ Lock）

插入意向锁是执行语句的时候产生的，如果插入的记录在间隙锁范围内，插入意向锁会被阻塞。

2.5 锁类型底层实现。

#define LOCK_WAIT   256 /*!< Waiting lock flag; when set, it  //锁等待means that the lock has not yet beengranted, it is just waiting for itsturn in the wait queue */
/* Precise modes */
#define LOCK_ORDINARY   0   /*!< this flag denotes an ordinarynext-key lock in contrast to LOCK_GAPor LOCK_REC_NOT_GAP */
#define LOCK_GAP    512 /*!< when this bit is set, it means that thelock holds only on the gap before the record;for instance, an x-lock on the gap does notgive permission to modify the record on whichthe bit is set; locks of this type are createdwhen records are removed from the index chainof records */
#define LOCK_REC_NOT_GAP 1024   /*!< this bit means that the lock is only on the index record and does NOT block insertsto the gap before the index record; this isused in the case when we retrieve a recordwith a unique key, and is also used inlocking plain SELECTs (not part of UPDATEor DELETE) when the user has set the READCOMMITTED isolation level */
#define LOCK_INSERT_INTENTION 2048 /*!< this bit is set when we place a waiting gap type record lock request in order to letan insert of an index record to wait untilthere are no conflicting locks by othertransactions on the gap; note that this flagremains set when the waiting lock is granted,or if the lock is inherited to a neighboringrecord */
#define LOCK_PREDICATE  8192    /*!< Predicate lock */
#define LOCK_PRDT_PAGE  16384   /*!< Page lock */

参考：

PS：锁是为了解决并发带来问题而设计的

3. RR隔离级别下： Read vs Read

简单的操作，属于快照读，不加锁

例子：

 select * from table where ?

特殊的读操作，插入、更新、删除操作，属于当前读，需要加锁

例子：

select * from table where ? lock in share mode;   /** S锁(共享锁) */select * from table where ? for update; 		/** X锁(排他锁) */	insert into table values (…);					/** X锁(排他锁) */	update table set ? where ?;						/** X锁(排他锁) */	delete from table where ?;						/** X锁(排他锁) */		

所有以上语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其它并发事务不能修改当前记录，需要对读取记录加锁。(PS：如果当前读没找到数据，不会对记录加锁，因为记录不存在)

为什么 //都归为当前读。

例子：在数据库中的执行流程

注意：根据上图的交互，针对一条当前读的SQL语句， 与 MySQL 的交互，是一条一条进行的，因此，加锁也是一条一条进行的。先对一条满足条件的记录加锁，返回给 MySQL ，做一些 DML 操作；然后在读取下一条加锁，直至读取完毕。

附加：这里你会疑惑，一条当前读的 SQL 语句，为啥会有多条交互？

答：其实 mysql 层和存储引擎层的交互是以记录为单位的

拿下面这个 sql 举例子：

delete * from lock_test where v1 >= 6

首先，mysql 请求 层 v1 建立的二级索引，把 v1 >= 6 作为检索条件交给存储引擎，获取到 v1 >= 6 的第一条记录(假设第一条记录 v1 = 6, 在 层 v1 = 6 的记录时候，对它加了 X 锁)，然后 mysql 进行回表操作，拿着 v1 = 6 记录中的主键 ID(假设ID=6) 请求 层主键索引，获取到 id = 6 的记录（在 层 id = 6 的记录时候，对它加了 X 锁），至此一条记录加锁完成；mysql 还将继续请求 获取 v1 = 6 记录的下一条记录，直至读取完毕；读取完毕后，如果还有 where 条件，则进行 where 条件过滤；

4. 加锁过程分析 4.0 一条简单SQL的加锁实现分析

建表语句

CREATE TABLE `lock_test` (`id` int(11) NOT NULL AUTO_INCREMENT,`v1` int(11) DEFAULT NULL,`v2` int(11) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_v1` (`v1`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

表中的数据如下：

下面给了一些sql + 前提条件，请分析他们加的是什么锁

4.1 RC + 主键id

id是主键，Read 隔离级别，sql 如下：

delete from lock_test where id = 10;

触发当前读，基于主键索引查找id = 10的记录并加上X锁

图示：

验证：

在 之前执行 SHOW ;

---TRANSACTION 145C40, ACTIVE 169 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 8, OS thread handle 0x5ce4, query id 730 localhost 127.0.0.1 root updating
DELETE FROM lock_test WHERE id = 10
------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:   # HTR 已经等待了7s，等待该锁被授权
RECORD LOCKS space id 0 page no 599 n bits 80 index `PRIMARY` of table `mytest`.`lock_test` trx id 145C40 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 320: len 4; hex 8000000a; asc     ;;1: len 6; hex 000000145c3f; asc     \?;;2: len 7; hex 2d0000c00201ca; asc -      ;;3: len 4; hex 80000009; asc     ;;4: len 4; hex 80000005; asc     ;;------------------
---TRANSACTION 145C3F, ACTIVE 846 sec
2 lock struct(s), heap size 320, 1 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 0x5dd0, query id 731 localhost 127.0.0.1 root

可知，事务 加了两种类型的锁( 意向表锁IX 和 的行锁)。事务 加了一个锁类型( 意向表锁IX )，而 X锁 被阻塞。

结论：此SQL会在主键索引上 id=10这条记录上加 X锁。（表级意向锁主要和表锁相关，后面分析将不会带上表级意向锁）

如果想知道，事务 是被哪条 sql 阻塞，可以使用强大的 ，执行如下：

select pse.SQL_TEXT from performance_schema.data_lock_waits psd left join 
performance_schema.events_statements_history pse 
on psd.BLOCKING_THREAD_ID = pse.THREAD_ID and psd.BLOCKING_EVENT_ID = pse.EVENT_ID + 1;

4.2 RC + 唯一索引v1

id是主键，v1是二级索引，Read 隔离级别，sql如下：

delete from lock_test where v1 = 7;

触发当前读，先从v1建立二级索引上找到v1 = 7的记录，并加 X锁，然后根据条件id=7，去主键索引上查出id=7的记录(回表)，并加 X锁。

图示：

为什么主键索引上的记录也要加锁？因为要删除的数据是主键索引上的行记录，防止其它事务来更改这天记录而造成并发问题。举例：如果并发的一个 SQL，是通过主键索引来更新： set v2 = 10 where id= 7; 此时，如果 语句没有将主键索引上的记录加锁，那么并发的 就会感知不到 语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。

验证：

结论：v1 是 二级索引列，该SQL需要加两个 X锁 ，一个[v1 = 7, id = 7]的二级索引的记录。另一个在主键索引上[id = 7, v1 = 7, v2 = 6]的记录

4.3 RC + 非唯一索引v1

id是主键，v1是非唯一二级索引，Read 隔离级别，sql如下：

delete from lock_test where v1 = 7;

​ 触发当前读，先从v1建立二级索引上找到v1 = 7的所有记录，并加 X锁，然后从每条记录上拿去id值，去主键索引上查出对应的行记录(回表)，并加 X锁。

图示：

验证：略，RC隔离级别下，唯一二级索引和非唯一二级索引加锁过程是一样的

结论：略

4.4 RC + 无索引v2

id是主键，v2没有建立二级索引，Read 隔离级别，sql如下：

delete from lock_test where v2 = 6;

触发当前读，v2 没有索引，where v2 = 6 这个条件也就没法通过二级索引来过滤，那么只能通过主键索引走全表扫描。对于这个sql会加什么锁？主键索引上所有的记录都会被加上 X锁，但是经过 的条件过滤后，不符合条件的记录会被放锁。

图示：

验证：

解析：id = 1,5,7的记录先被 1 加锁又被释放，id = 7 的记录被加 X锁

结论：v2 字段没建立索引，SQL 会走主键索引进行全表扫描，由于过滤条件是在 MySQL 层进行的。因此每条记录在 层，无论是否满足条件，都会被加上 X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁。(注意：全表扫描，MySQL 和 交互也还是一条条记录为单位的，当查询出所有记录，再执行 where 条件过滤)

4.5 RC + 不存在的记录

id是主键，v1是二级索引，Read 隔离级别，sql如下：

delete from lock_test where id = 8;or
delete from lock_test where v1 = 8;

触发当前读，但是 id = 8 和 v1 = 8的记录是不存在的，所以也没办法对记录上X锁。

图示：略

验证：

结论：对于不存在的记录，触发当前读，不会对记录加 X锁，因为记录根本不存在。（PS：记住加 X锁 并不是锁 key 值，而是锁记录）

4.6 RR + 主键id

id是主键， Read隔离级别，sql如下：

delete from lock_test where id = 7;

触发当前读，基于主键索引查找id = 7的记录并加上X锁

图示：

结论：此SQL只需要在主键索引上 id=10这条记录上加X锁（PS：跟RC一样）

3.7 RR + 唯一索引v1

id是主键，v1是唯一二级索引， Read隔离级别，sql如下：

delete from lock_test where v1 = 7;

触发当前读，先从v1建立唯一二级索引上找到v1 = 7的记录，并加X锁，然后根据条件id=7，去主键索引上查出id=7的记录(回表)，并加X锁。

图示：

验证：

结论：跟RC一样。

3.8 RR + 非唯一索引v1

id是主键，v1是非唯一二级索引， Read隔离级别，sql如下：

delete from lock_test where v1 = 7;

PS：RR隔离级别能解决部分幻读，但是在前面几个组合加锁都和RC一样，那么RR是怎么防止幻读的呢，答案就在本组合中揭晓。

​ 触发当前读，先从v1建立二级索引上找到v1 = 7的所有记录，并加 X锁，且对该记录的上区间和下区间加 Gap Lock(间隙锁)，然后从每条记录上拿去id值，去主键索引上查出对应的行记录(回表)，并加 X锁。

图示：

这个多出来的 GAP锁，就是RR隔离级别，相对于RC隔离级别，当前读不会出现幻读的关键。GAP锁锁住的位置，也不是记录本身，而是两条记录之间的 GAP。（GAP 锁：本质其实是在记录上加 标识，比如上面会在二级索引记录 [7,7]、[9,9] 上加标识，代表上一条记录到当前记录中间不允许插入数据）

如何保证两次当前读返回一致的记录，那就需要在第一次当前读与第二次当前读之间，其他的事务不会插入新的满足条件的记录并提交。为了实现这个功能，GAP 锁应运而生。

如图中所示，有哪些位置可以插入新的满足条件的项 (v1 = 7)，考虑到B+树索引的有序性，满足条件的项一定是连续存放的。记录[5,5]之前不会插入v1 = 7的记录；记录[5,5]与[7,7]之间可以插入[7,6]; 记录[7,7]与[9,10]之间可以插入[7, id > 7]的记录；而记录[9,10]之后不会插入v1 = 7的记录，MySQL选择了用GAP锁，将这两个 Gap 给锁起来。

操作，会加插入意向锁(间隙锁的一种)，如果插入成功，会对插入成功的记录加 X锁。如 [7,6]，首先会定位到[5,5]与[7,7]间，然后在插入前，会检查这个 GAP 是否已经被锁上，如果被锁上，则 不能插入记录。因此，通过第一遍的当前读，不仅将满足条件的记录锁上 (X锁)。同时还是增加2把GAP 锁，将可能插入满足条件记录的2个 GAP 给锁上，保证后续的 不能插入新的 v1=7 的记录，也就杜绝了同一事务的第二次当前读，出现幻象的情况。

有心的朋友看到这儿，可以会问：既然防止幻读，需要靠 GAP 锁的保护，为什么组合RR + 主键id、组合RR + 唯一二级索引v1，也是RR隔离级别，却不需要加 GAP 锁呢？

首先，这是一个好问题。其次，回答这个问题，也很简单。GAP 锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。而组合RR + 主键id，id是主键；组合RR + 唯一二级索引v1，v1是键，都能够保证唯一性。一个等值查询，最多只能返回一条记录，而且新的相同取值的记录，一定不会在新插入进来，因此也就避免了 GAP 锁的使用。

GAP锁 的弊端：例、记录[5,5]与[7,7]之间虽然防止了其它事务插入[7,6]，解决了幻读，但是也防止了插入[6,6]，而它和幻读无关联;

验证：

结论：RR + 非唯一索引情况下 通过 Next-Key Lock (记录锁和 Gap 锁组合起来就叫 Next-Key Lock)来解决部分幻读问题。

3.9 RR + 无索引v2

id是主键，v1是非唯一二级索引， Read隔离级别，sql如下：

DELETE FROM `lock_test` WHERE v2 = 6;

触发当前读，v2没有索引，where v2 = 6这个条件也就没法通过二级索引来过滤，那么只能通过主键索引走全表扫描。对于这个sql会加什么锁？主键索引上所有的记录都会被加上 Lock 和 Gap Lock，经过 mysql 的条件过滤后，不会释放放锁(这与RC不一样)。

图示：

疑问：这与RC组合不一样，不会释放锁，而且加了 Gap Lock,为什么？

答：首先我们明白一个问题，RR和RC在当前读的情况下，为啥需要加锁机制不一样。为了解决什么问题。幻读！然后我们再来分析这个例子。全表扫描在引擎会返回所有记录，然后 Mysql 拿取这些记录根据条件(where v2 = 6)判断。问题一：为啥不符合条件的记录不放 Lock 呢？假如放锁，比如记录[id = 1, v1 = 1, v2 = 0]可以被其它事务改成[id = 1, v1 = 1, v2 = 6], 对于先执行 FROM WHERE v2 = 6的事务再次执行这条语句，发现还有v2 = 6的记录，这样就造成幻读。问题二：为啥所有间隙都加了 Gap Lock，假设不加 Gap Lock，是不是所有记录间隙之间能被其它事件 v2 = 6 的记录，也造成了幻读。

验证：略

这里你们可以自己去验证。

结论：全表扫描，所有记录都加了Next Key Lock( Lock和Gap Lock的组合)

PS：在RR隔离级别下，尽量不要出现这种全表扫描的当前读SQL，否则MYSQL的并发性能非常差。

3.10 RR + 不存在的记录

略：可以自己去完成!

​