锁

文章目录

• 锁
• • 全局锁
  • 表级锁
  • • 表锁
    • 元数据锁（MDL）
    • 意向锁
    • AUTO-INC锁
  • 行级锁
  • • Record Lock
    • Gap Lock
    • Next-Key Lock
    • 插入意向锁
  • 行级锁是怎么加的
  • • 唯一索引等值查询
    • 唯一索引范围查询
    • 非唯一索引等值查询
    • 非唯一索引范围查询
    • 不加索引的查询
  • 死锁
  • • insert加行级锁
    • • 记录之间有间隙锁
      • 唯一键冲突

这篇文章来聊聊MySQL中的各种锁，以及这些锁的使用场景

全局锁

当开启全局锁之后，整个数据库会进入只读状态，这时候如果有其他线程（包括当前线程）执行以下操作，都会被阻塞。

• 执行insert，update，delete等修改数据的操作
• 执行alter table ，drop table等修改表结构的操作

开启全局锁命令：flush tables with read lock

关闭全局锁命令：unlock tables

全局锁的应用场景:全局锁主要应用于全库数据备份。

假如全库数据备份前不加全局锁，会出现什么情况呢?

• 假如现在有用户购买商品这样一个场景，不加全局锁进行数据备份，就可能会出现以下这种情况：

• 1.先备份了用户表，

• 2.用户购买了商品，

• 3.又备份了商品表。

• 也就是用户购买商品在备份用户表和备份商品表之间发生了。那备份的结果就是用户表中用户余额并没有减少，而商品表中的商品数量减少了。如果后续用备份结果恢复数据库，那就等于用户白嫖了一件商品。所以如果加上全局锁，再进行备份，因为不能写数据，只能读数据。就不会出现上面的情况

使用全局锁的问题：

但是全局锁也有一个大问题，就是如果数据库的数据量比较大，就会造成备份的时间比较长，那也就会造成业务的停滞时间比较长。因为全局锁期间只能读数据，不能写数据。

对于InnoDB存储引擎有一个比较好的解决办法，就是在可重复读隔离级别下，开启事务，进行全库数据备份。开启事务会创建read view，那么整个事务执行期间，也就是数据备份期间，看到的数据和事务启动时看到的数据是一致的。这样并不影响其他线程写数据。但是对于MyISAM存储引擎，它不支持事务，所以就没办法用这种方式数据备份

表级锁

MySQL中表级别的锁有：表锁，元数据锁（MDL），意向锁，AUTO-INC锁

表锁

对student表加表锁的命令：

//共享表锁，也就是读锁
lock tables student read;
//独占表锁，也就是写锁
lock tables student write;

释放当前会话的所有表锁：

unlock tables;

使用命令会释放当前会话的所有表锁，此外如果会话退出，也会释放所有表锁。

当对student加上共享表锁后，当前线程以及其他线程都只能读，不能写，当前线程尝试写会直接报错，其他线程尝试写会阻塞，等当前线程释放锁之后，才能写成功。

当对student加上独占表锁后，当前线程可以读，也可以写，其他线程不能读，不能写。其他线程尝试读或者写都会阻塞，直到当前线程释放独占表锁

元数据锁（MDL）

MDL不需要显示使用，因为当对表进行操作的时候会自动加MDL锁

• 对一张表进行CRUD操作，加的是MDL读锁，执行完CRUD自动释放锁
• 对一张表做结构变更操作的时候，加的是MDL写锁，执行完语句自动释放锁

当前线程如果在执行CRUD操作（加MDL读锁）期间，如果其他线程尝试更改表结构（申请MDL写锁）会被阻塞，直到执行完CRUD操作（释放MDL读锁），如果尝试执行CRUD操作，则不会被阻塞，读-读不冲突。

当前线程如果在执行更改表结构操作（加MDL写锁）期间，如果其他线程尝试执行CRUD操作（申请加MDL读锁）则会被阻塞，直到当前线程执行完更改表结构操作。

如果在事务中执行CRUD，或者做更改表结构操作，那MDL的释放是在事务提交后自动释放的。

接下来看一个问题：

1. 线程A开启了事务（但是一直不提交），然后执行select语句，此时就会对表加上MDL读锁
2. 线程B尝试更改表结构，会发生读-写冲突，会被阻塞
3. 线程C尝试select，但是会被阻塞

当线程B被阻塞后，后续如果有其他线程尝试执行CRUD都会被阻塞，按理来说，当前表加的是MDL读锁，如果有其他线程尝试读不应该会阻塞，因为读-读不冲突，但是实际情况是会阻塞，原因是：

事情MDL锁的操作会形成一个优先级队列，在这个队列中，写锁优先级的获取高于读锁，所以必须得等写锁被获取后，然后释放了写锁，线程才能获取读锁。所以，由于线程A中的事务一直不提交，导致线程B写锁一直获取不到，进而导致其他线程的CRUD被阻塞。

所以在变更表结构的时候，可以提前看看数据库中的长事务（长时间未提交的事务）是否对表加了MDL读锁

意向锁

在InnoDB引擎中，在对表中记录加锁（也就是加行级锁）之前，要先对表加意向锁：

• 在对表中记录加共享锁之前，要对表先加上意向共享锁
• 在对表中记录加独占锁之前，要对表先加上意向独占锁

意向共享锁和意向独占锁是表级锁，不会和行级的独占锁和共享锁发生冲突的。

AUTO-INC锁

表中的主键通常会设置成自增的，那如何保证在多线程环境下，主键自增不会发生线程安全问题呢？

如果两个线程同时插入一条语句，同时判断当前自增主键的最大值是5，然后就会插入两条主键是6的记录，这就发生了线程安全问题，为了解决这种问题，就需要用到AUTO-INC锁。

AUTO-INC锁是一种特殊的表锁机制，在事务中用到时，会等插入语句执行完就立即释放锁，而不是等事务提交后才释放锁。

当一个事务持有AUTO-INC锁时，其他事务如果想要插入语句，就会被阻塞，因此解决了上面提到的线程安全问题。

行级锁

InnoDB引擎是支持行级锁的，而MyISAM不支持行级锁

共享锁（S锁）满足读读共享，读写互斥。独占锁满足读写互斥，写写互斥。

Record Lock

Record Lock称为记录锁，锁住的是一条记录。记录锁有S型记录锁和X型记录锁之分。

当一个事务对一条记录加了S型记录锁，其他事务也可以继续对该纪录加 S 型记录锁，因为读读不互斥。但是不能对该记录加X型锁。因为读写互斥

当一个事务对一条记录加了X型记录锁，其他事务不可以继续对该纪录加 S 型记录锁，因为读写互斥。也不能对该记录加X型锁。因为写写互斥

Gap Lock

Gap Lock称为间隙锁，只存在于可重复读隔离级别下，目的是解决可重复读隔离级别下的幻读现象。
如果表中有一个id范围是（3，5）的间隙锁，那么其他事务就没法插入id=4的这条记录了，这样就解决了幻读。

间隙锁存在X型间隙锁和S型间隙锁，但是间隙锁之间是兼容的，并不会存在互斥关系，两个事务可以同时持有包含共同间隙范围的间隙锁。

如果一个事务A加了（15,20]范围的X型的next-key lock，则事务B也是可以在(15,20)的范围内加X型的间隙锁的(因为相当于在(15,20)范围内加了两个间隙锁，间隙锁是可兼容的），但是事务B不能加（15,20]范围内的X型的next-key lock，X型的临键锁和X型的临键锁是不兼容的。

Next-Key Lock

Next-Key Lock称为临键锁，是Record Lock+Gap Lock的组合，锁定一个范围并且锁定记录本身。如果表中有一个范围ID为(3,5]的临键锁，那么其他事务既不能在id(3,5)的范围内插入记录，也不能修改id为5的这条记录。

Next-Key Lock可以看成记录锁和间隙锁的组合，比如上面的(3,5]之间的临键锁，在（3,4）之间可以看成间隙锁（在这个范围内插入记录会被阻塞），对于id==5这条记录可以看成加了记录锁（既不能修改也不能删除）

插入意向锁

在可重复度隔离级别下，一个事务要插入一条记录时，需要先判断该位置是否已经被其他事务加了间隙锁（next-key lock也包含间隙锁），如果有的话，则插入会被阻塞。除了会被阻塞之外，该事务还会生成一个插入意向锁，表明想在此区间插入记录。

假如事务A已经对范围id为（3,5）之间加了间隙锁，那当事务B要插入id为4的记录时，就会被阻塞，而且事务B还会生成一个插入意向锁，然后将锁的状态设置为等待状态（MySQL加锁时是先生成锁结构，然后设置锁的状态，如果锁的状态是等待状态，表明该事务并没有获取到锁，而是等待获取锁，只要当锁的状态是正常状态，才表明成功获取到锁）。

间隙锁往往锁住的是一个区间，而插入意向锁锁住的是一个点。

行级锁是怎么加的

行级锁是怎么加的，这是一个很大的话题，因为行级锁是怎么加的和隔离级别有关，和索引有关，和等值查询还是范围查询也有关。不同的隔离级别下支持的锁不同，加锁的方式也不同

在读已提交隔离级别下，行级锁仅有记录锁

而在可重复读隔离级别下，行级锁有记录锁，间隙锁，还有临键锁。

X型锁和X型锁不兼容，X型锁和S型锁不兼容（不兼容就是不能同时加S型锁和X型锁），S型锁和S型锁兼容（兼容就是可以同时加两个S型锁）

两个锁定读语句:select for update(对读取的记录加X型行级锁)，select lock in share mode(对读取的记录加S型行级锁)

在可重复读隔离级别下，insert,update,delete，以及两个锁定读语句都会加行级锁 。但是普通的select语句不会加锁，因为使用MVCC解决了读-写冲突。在加行级锁的情况下加锁的对象是索引，加锁的基本单位是next-key lock，也就是正常情况下，每次加行级锁加的都是next-key lock，但是有些情况下next-key lock会退化成记录锁或者间隙锁。那具体什么情况下会发生退化，总结一句话就是:如果使用记录锁或者间隙锁就足以解决幻读，那么就会退化。

接下来的讨论都是基于MySQL的InnoDB引擎，可重复读隔离级别下，MySQL的版本是8.0

• 首先创建一张测试用表并插入几条记录：id（主键），age（加了非唯一索引），name（没有加索引）

开头就提到了，加了什么行级锁，和有没有用到索引 有关，和用到什么类型索引有关，和等值查询还是范围查询有关，和要查询的记录是否存在也有关，所以接下来分情况讨论：

唯一索引等值查询

在这种情况下，要查询的记录是否存在，加锁的规则是不同的:

• 如果查询的记录是存在的，则next-key lock会退化成记录锁，也就是会用记录锁来锁住查询的记录。

• 如果查询的记录是不存在的，则next-key lock会退化成间隙锁，即会在索引树中找到第一条大于这条不存在记录的记录，给这条记录的索引上加一个间隙锁。

既然，记录是否存在加锁规则有差别，那接下来分开讨论：

⭕️查询的记录是存在的：

首先在事务A中查询id为1的这条记录，读取方式是X型锁定读：

• 那么，当在事务A中执行该查询语句后，会对id为1的这条记录加上X型的记录锁，后续如果有其他事务对这条记录执行update，delete等操作都会阻塞（因为X型锁和X型锁不兼容，也就是不能同时加两个锁）

• 除了update，delete涉及到id=1的这条记录会阻塞外，如果锁定读涉及到id=1的这条记录同样也会阻塞
• 如果执行锁定读并且读取的范围中包含了id为1的这条记录也会阻塞（因为X型锁和S,X型锁都不兼容），如果执行锁定锁但是读取的范围不包含id为1的这条记录则不会阻塞。

使用select * from performance_schema.data_locks\G这条sql语句可以查看什么表中加了什么锁

下面，看一下事务A加了什么锁：

• 从结果中可以看到共加了两个锁：一个是表级别的X类型的意向锁，一个是行级别的X类型的记录锁

LOCK_TYPE: TABLE代表的是表级锁，RECORD代表的是行级锁

对于行级锁：

LOCK_MODE字段可以分辨出是记录锁，间隙锁，还是临键锁：

• 如果LOCK_MODE为：X ：说明是X型的next-key lock
• 如果LOCK_MODE为：X,REC_NOT_GAP : 说明是X型的记录锁
• 如果LOCK_MODE为：X,GAP ：说明是X型的间隙锁

🎃唯一索引等值查询，如果查询的记录存在且使用的是for update类型的锁定读，则会给该记录加上X型的记录锁，则其他事务对该记录的更新（会加X型记录锁），删除（会加X型记录锁），以及锁定读（加X类型记录锁或者S型记录锁）都会阻塞。对于记录锁来说，X和X不兼容，X和S不兼容，仅有S和S兼容。

为什么唯一索引等值查询且查询的记录存在的情况下，next-key lock会退化成记录锁？

• 原因是在这种场景下，仅凭记录锁就能解决幻读问题：
• 因为id是主键，所以无法再插入一条id=1的记录，而且对id为1的记录加了间隙锁，其他事务无法对该记录更新或者删除，主键+间隙锁，就足以解决幻读，所以next-key lock退化成记录锁

⭕️查询的记录不存在：

SQL ：select * from user where id=2 for update;

查看一下加的锁：

加了两个锁：

• X类型的表级锁
• X类型的间隙锁

那这个间隙锁的范围是什么呢？而且上面提到了锁是加到索引上的，而id==2的这条记录根本就不存在，那怎么加到索引上的呢？

上面我们也提到了会找到第一条大于id==2的记录，也就是找到id == 5的这条记录，给这条记录的索引上加一个间隙锁。

一般而言，如果是间隙锁或者临键锁，LOCK_DATA就代表的是锁的范围的右边界。左边界是id为5的记录的上一条记录的id值，也就是1。因此间隙锁的法范围是（1,5），间隙锁加在了id为5这条记录的索引上。

那如果插入的记录的id超过表中最大id了呢，比如插入id为50的这条记录，那间隙锁该加到哪条记录的索引上呢？

做个测验：select * from user where id=50 for update;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wfXuxR5h-1679531801784)(C:\Users\86178\AppData\Roaming\Typora\typora-user-images\image-20221226161640127.png)]

• 可以看到加的是个临键锁，而锁的边界是一条伪记录。

当间隙锁的范围id是(1,5)时，其他事务都不能在id为(1,5)之间插入记录了，如果尝试插入不仅会阻塞，还会生成一个插入意向锁：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5zFe5JRC-1679531801784)(C:\Users\86178\AppData\Roaming\Typora\typora-user-images\image-20221226162700650.png)]

当其他事务尝试在(1,5)的范围内插入记录时，从LOCK_MODE中可以看出，生成的是X类型的间隙锁和插入意向锁，而且锁的状态是等待状态，说明该锁还没有获取到。所以如果事务A先加了一个间隙锁，然后事务B如果要在这个范围内插入记录则需要生成一个插入意向锁，不过并没有真的生成，因为锁的状态是等待转态。

间隙锁只会阻塞其他事务的插入，但不会阻塞其他事务的查找，删除，更新。、

为啥记录不存在的情况下，next-key lock会退化成间隙锁，然后加在大于不存在记录的第一条记录的索引上？

• 因为仅有一个间隙锁就足以解决幻读问题，首先：查询的记录不存在，是没法加记录锁的，其次，如果要加next-key lock在id==5的这条记录上，锁的范围是(1,5]，则id=5的这条记录是没法被其他事务修改了，而我们的查询语句的where条件是id == 2，那么id == 5的这条记录是否存在并不会影响id ==2查询结果集，我们不能多此一举给id == 5的这条记录锁上，所以最合适的是间隙锁。

🐢总结：唯一索引等值查询加锁分记录存在和记录不存在两种情况，对于for update类型的锁定读来说，记录存在加X类型的记录锁，记录不存在加X类型的间隙锁（加到第一个大于不存在记录的索引上）

唯一索引范围查询

范围查询的规则和等值查询时不太一样的，而且分的情况比较多，接下来我们一个情况一个情况来讨论

1️⃣针对大于的范围查询：

SQL语句：select * from user where id>15 for update;

从查询结果中，可以看到加了三个锁：

• 一个是X类型的表锁
• 一个是范围是(15,20]的next-key lock
• 一个是范围是 (20, +∞]的next-key lock

为啥会有个范围是(20, +∞]的next-key lock呢？

• 我们看到的表中最后一个记录是id==20的记录，但是实际上在InnoDB引擎中，会用一个特殊的记录作为最后一条记录（并不是id == 20的这条记录），该记录的名字叫做supermum pseudo-record（上确界伪记录），所以当LOCK_DATA为supermum pseudo-record时，说明该锁的上边界是+∞，而下边界是表中真实的最后一条记录

• id范围是(15,20]的next-key lock保证了（15,20）范围内不能插入记录相当于范围在（15,20）的间隙锁，而且id==20的这条记录不可以更新或者删除。相当于对id == 20的这条记录加了一个记录锁。
• 然后 id范围是(20, +∞]的next-key lock保证了在(20, +∞]这个范围内不能插入任何记录。

通过上面的两个next-key lock，当保证查询条件为id>15时，既可以保证可重复读，还可以保证幻读。

那如果查询条件是id>7时，会加什么锁呢？

• 我们想象一下，如果要保证可重复读，还要保证不发生幻读，就需要在id=10的记录，和id=15的记录，以及id=20的记录，以及supermum pseudo-record记录的索引上各自加一个next-key lock。

查询结果：只截取了行级锁。

也就是范围是（5,10]和（10,15]以及(15,20]以及(20, +∞]的next-key lock，其实可以理解为在(5,+∞]的范围区间内不能插入记录，不能修改记录，不能删除记录。这样既解决了不可重复读的问题，还可以解决幻读的问题。

2️⃣针对大于等于的范围查询：

SQL：select * from user where id>=15 for update;

根据上面的范围查询的经验，我们来思考一下查询条件是id>=15的条件下如何保证可重复读和保证不幻读。在id>=15的范围内，不能再插入记录，不能修改或者删除已有的记录。

首先对于id=15的记录应该加上记录锁，保证id ==15的记录不可以被更新或者删除，然后在id>15的范围内，应该给id=20的记录加上一个临键锁，范围是(15,20]，可以保证在（15,20）的区间内不被插入记录，还可以保证id == 20的这条记录不被更新或者删除，然后再来一个id范围是(20, +∞]的next-key lock，保证不能插入id>20的记录。

查询结果：（只截取了行级锁）

• 查询结果和我们猜测的加锁类型是一致的。

id>=15和id>15的区别就在于多了一个id==15的记录锁，其他都一样。

4️⃣针对小于的范围查询：

针对小于的范围查询还需要分查询条件中的记录是否在表中：

首先来看查询条件中的记录不在表中：

SQL：select * from user where id<6 (id为6的记录不在表中)

首先我们来预期一下应该会加什么锁，才能在id<6的范围内保证可重复读和保证不出现幻读 。

首先， (-∞, 1] 的范围内应该加上一个next-key lock，保证id<1的范围内不可以插入记录，id==1的这条记录不能被更新或者修改。然后(1,5]的范围内应该加一个next-key lock，保证在(1,5)的范围内不可插入且id == 5的记录不能被修改或者删除。但是id == 5并没有达到终止扫描的条件，因为id为5还是小于6的，所以接着往后扫描，应该在id=10的记录的索引上加一个间隙锁，保证在（5.10）的范围内不可插入元素，这个锁用间隙锁就足够了，没有必要用next-key lock，因为id == 10的记录是否更改，是否删除都不会影响到id<6的查询条件的查询结果

查看加锁结果：（只截取了行级锁）

从查询结果中可以看到加的锁为：(-∞, 1] 范围的next-key lock，（1,5]范围的next-key lock，(5,10)范围的间隙锁。和预想的结果是一样的。

查询条件中的记录在表中：

SQL：select * from user where id<5 for update;

预期：在id<5的范围内，要保证可重复读还要保证不发生幻读，则我们预期要加的锁为：

(-∞, 1] 范围的next-key lock,（1,5)范围的间隙锁。没有必要给id == 5这条记录加一个记录锁，因为查询的条件是id<5, 即使id == 5这条记录发生了更新或者删除，也不会影响到查询结果。到这里我们就大致明白了，什么时候用哪种行级锁。假如必须得用next-key lock才够用就得用next-key lock，如果仅用记录锁或者是间隙锁就能够保证不发生不可重复读或者幻读现象，那就用没必要用next-key lock，加的锁能保证查询语句读取范围内的记录不发增删或者修改就OK了，范围外的记录没必要保证。

查询结果：（只截取了行级锁）

• 和预期是一样的。

4️⃣针对小于等于的范围查询：

查询条件中的记录存在于表中：

SQL: select * from user where id<=5 for update;

查询结果：（只截取了行级锁）

加了两个锁：

• (-∞, 1]的next-key lock
• (1,5]的next-key lock

对于查询条件中的记录不存在于表中：

SQL：select * from user where id<=6 for update;

分析：

• 首先(-∞, 1]的next-key lock和(1,5]的next-key lock肯定得有，然后因为5还是没有越过6这个边界，所以还需要向后扫描，最终应该给id == 10的这条记录的索引加一个间隙锁，范围是(5,10)

查询结果：（只截取了行级锁）

🐢总结：从范围查询中，我们了解到对于范围边界，能使用间隙锁或者记录锁来解决不可重复读或者幻读问题，就使用间隙锁或者记录锁，如果使用者两种锁不够，再考虑使用临键锁。加锁得加够，但是不能多加。

非唯一索引等值查询

当使用非唯一索引进行等值查询时，会对主键索引和二级索引都加锁，但是对主键索引进行加锁的时候，只有满足查询条件的记录，才会对他们的主键索引加锁。

1️⃣记录不存在的情况：

SQL：select * from user where age=25 for update;

在二级索引树上查找age=25的记录，但是对应的记录不存在，则会定位到第一条age>25的记录，然后在该记录的二级索引上加范围是(22，39)的间隙锁。这意味着其他事务尝试插入age为23，24，25……38的记录时会被阻塞，但是能否插入age为22以及age为39的记录还需另当别论。

查询结果：（只截取了行级锁）

• LOCK_DATA中有两个数值：39和20，39代表的是age，代表的是间隙锁的右边界，20是age=39这条记录对应的id值。
• 在age为39的记录的二级索引上加了间隙锁范围是（20,39）

正如上面这种情况，那什么时候插入age为22或者39会被阻塞，什么时候会成功插入呢？我们需要清楚，插入一条语句时，啥情况会阻塞?

• 当插入一条记录时，需要先定位该记录在B+树上的位置，如果该记录后面的记录的索引上有间隙锁，此时插入才会被阻塞。此外，还需要一个前置知识:二级索引树中，先按二级索引age排序，在age相同的情况下，再按主键id排序。

🔑插入age=22的记录成功和失败的情况:

成功:当其他事务插入一条age=22,id=3的记录的时候，在二级索引树上定位到插入位置，则该插入位置的下一条是age=22,id=10的记录，该记录的索引上并没有加间隙锁，所以可以插入成功。
失败:当其他事务插入一条age=22,id=12的记录时，会先在二级索引树上定位插入位置，该插入位置的下一条记录是age=39，id=20的记录，该记录是二级索引上有间隙锁，所以插入会被阻塞

🔑插入age=39的记录成功和失败的情况:

成功:当其他事务插入一条age=39,id=21的记录时，在二级索引树上定位到插入位置，插入位置是表中最后一条记录，下一条记录不存在，所以插入成功。
失败:当其他事务插入一条age=39,id=3的记录时，在二级索引树上确定插入位置，发现插入位置的下一条记录是age=39,id=20的记录，该记录的二级索引上加了间隙锁，所以插入会被阻塞。

🐢当一个事务持有二级索引(非唯一索引)的间隙锁，age范围是（22，39）时，其他事务尝试插入age为23,24，…38的记录时都会被阻塞，但是插入age为22或者39的记录时，有可能会被阻塞，有可能会成功插入，是成功插入还是阻塞取决于待插入记录在二级索引树中的位置，该位置后面那一条记录是否加了间隙锁，如果加了间隙锁则会阻塞，如果没有间隙锁则不会阻塞。

而确定待插入记录在二级索引树中的位置，不仅要根据二级索引age判断，还需要根据主键id来判断，整体上是按age排序，但是当age相等时，需要按id排序，

2️⃣记录存在的情况：

SQL：select * from user where age=22 for update;

查询结果：（只截取了行级锁）

在查询的记录存在的情况下，唯一索引等值查询时加的行级锁仅有一个记录锁，但是在非唯一索引查询时加的行级锁有三个：

首先对age=22的这条记录，在其二级索引上加了age范围是(21,22]的next-key lock，然后因为（age=22，id=10，name=山治）的这条记录符合查询条件，所以还要在对该记录的主键索引上加上记录锁。然后在二级索引树上加上age范围是（22,39）的间隙锁。

对主键索引（id=10的记录的索引）加了X型的记录锁，其他事务修改或者删除（id=10，age=22）的这条记录都会被阻塞。

有了上面这一个X型的记录锁之后，该记录就不能被修改或者删除了，那为啥还要有一个next-key lock和一个间隙锁呢，这其实是为了解决幻读问题，如果没有这两个锁，那插入一条（age=22，id=18）的记录是绝对可以插入成功的，然后再查询age=22的记录，就会查询出两条age=22的记录，这就出现了幻读问题。所以为了解决幻读问题，二级索引上的这两个锁是必要的。

当有了这两个锁之后，凡是age=22的记录是一定无法插入的，如果待插入记录age=22，id<10，则该待插入记录在二级索引B+树中的下一条记录是加了next-key lock的，会发送阻塞。如果待插入记录是age=22，id=10，无法插入，因为id是主键，具有唯一性。如果待插入记录age=22，id>10，则该待插入记录的下一条记录在二级索引的B+树中的下一条记录是age=39的记录，该记录加了间隙锁，所以插入会被阻塞。所以经分析，有了一个next-key lock，和一个间隙锁，凡是age=22的记录插入一定不会成功。这就解决了幻读。此处非唯一索引和唯一索引处的等值查询的区别是：唯一索引处，有主键限制，不能插入id一样的记录，但是非唯一索引处没有这个限制，本来是可以插入age一样的记录的，但是考虑到幻读问题，使用了两个间隙锁来让age一样的记录的插入受到阻塞。

有了这两个间隙锁，（21,39）之间是无法插入记录的，那age=21的记录，和age=39的记录能否插入成功呢？分析原理为：如果待插入记录的下一条记录有间隙锁或者next-key lock，则插入会阻塞，否则插入成功。

当插入age=21的记录时，还要看该记录的id值，如果该记录的id<5，则该待插入记录在二级索引树中的下一条记录是索隆，不会发生阻塞，如果该记录的id>5，则该待插入记录在二级索引树中的下一条记录是山治，山治这条记录的索引上加了next-key lock，所以会发生阻塞。

当插入age=38的记录时，也同样要看该记录的id值，如果该记录的id>20，则该待插入记录在二级索引树中是最后一条记录，不会发生阻塞，如果该记录的id<20，则该待插入记录在二级索引树中的下一条记录是香克斯，香克斯这条记录的索引上加了间隙锁，所以会发生阻塞。

其实age=21或者age=38的记录能否插入并不会影响到查询条件是age=22的查询结果，所以能否插入并不重要，重要的是保证age=22的记录不能被插入。

非唯一索引范围查询

非唯一索引的范围查询和唯一索引的范围查询的加锁情况还有不同，接下来通过一个案例来分析一下非唯一索引的范围查询的加锁情况：

二级索引树上加的行级锁：三个锁都是next-key lock

主键索引树上加的行级锁：

• 对id=10和id=20的记录的索引加了记录锁
• 因为符合查询条件的记录有这两个记录，所以这两个记录在主键索引上加了记录锁

这五个锁的图示：

三个next-key lock：范围是(21,22]，(22,39]，（39，+∞]，这三个锁保证不会出现幻读现象：第一次查询结果中有两条记录是符合的，即age=22和age=39的记录。(21,22]，(22,39]这两个范围确保了其他事务插入age=22的记录会被阻塞，(22,39]，（39，+∞]这两个范围确保了其他事务插入age=39的记录会被阻塞。所以这三个next-key lock避免了幻读现象的发生。

两个主键索引上的X型记录锁，保证了这两条记录不会被更新或者删除。

📛总结：在非唯一索引查询过程中，对于符合查询条件的记录，在主键索引树中要对该记录的索引加上记录锁，保证记录不会被修改或删除，另外因为是非唯一索引，于是索引字段可以出现重复，但是为了保证不出现幻读，通常还需要间隙锁或者next-key lock，来保证一个或者多个区间内无法插入记录，以此来避免幻读。

不加索引的查询

前面的查询都用到了索引，先使用索引进行查询记录，再对查询到的记录加锁。

如果对于锁定读语句，update，delete如果条件中没有用到索引，则会走全表扫描，结果就是每一条记录的索引都会加上next-key lock。

SQL：select * from user where name=‘路飞’ for update;

可以看到对未使用索引进行查询，会对所有记录都加上next-key lock，想当与锁了全表，所以并发性能会大打折扣，要尽量避免这种情况。

上面演示的锁定读语句，查询时不使用索引，而是进行全表扫描，会锁全表，update和delete也是一样，如果没有用到索引，而是进行全表扫描，会锁全表。

死锁

在MySQL的并发事务中，为了解决读-写冲突，引入了行级锁，表级锁，这些锁使用不当就有可能发送死锁，下面通过一个死锁的例子来演示什么是死锁：

t_order 表：id（自增主键），order_no（非唯一索引），create_date（普通字段）

然后两个事务执行以下sql：

在可重复度隔离级别下，此时两个事务的插入都会阻塞，都在等待对方释放锁，发生了死锁。

接下来我们，我们分析一下加了什么锁导致的死锁：

1️⃣事务A先检查1007订单是否存在加的锁：(只截取了行级锁)：

• 加的是(1006, +∞]的next-key lock。

上面学习非唯一索引等值查询的时候，如果记录不存在，加的是间隙锁，但是这里为啥是next-key lock呢？

这里有两种情况：

• 如果表中最后一条记录是1006，那查询1007就会加next-key lock
• 如果表中最后一条记录是1010，那查询1007就会加间隙锁

🐢也就是，如果查询的记录比最后一条记录还大，那加的是next-key lock，如果查询的记录没有最后一条记录大，那加的是间隙锁。

2️⃣事务B检查1008订单是否存在加的锁：(只截取了行级锁)：

两个next-key lock，范围都是(1006, +∞]，说明事务B和事务A加的锁一样，都是(1006, +∞]的next-key lock。

3️⃣事务A和事务B都要在(1006, +∞]范围内插入记录：

事务A和事务B都持有(1006, +∞]的next-key lock，所以当事务A要插入1007的记录时，会和事务B的next-key lock发生冲突，会被阻塞，当事务B要插入1008的记录时，会和事务A的next-key lock发生冲突，会被阻塞。此时两个事务都在等待对方释放锁，就僵持住了，也就是形成了死锁。

• 事务A插入1007会因为事务B持有(1006, +∞]范围的next-key lock而阻塞，但是不会因为事务A本身持有(1006, +∞]范围的next-key lock而阻塞。如果只是事务A持有(1006, +∞]范围的next-key lock，事务B并没有持有(1006, +∞]范围的next-key lock，那事务A是可以插入1007的。

为啥间隙锁与间隙锁是可以兼容的？

• 也就是为啥两个事务可以同时持有同一个范围的间隙锁？

间隙锁的意义只在于阻止区间被插入，一个事务获取到了间隙锁不会阻止另一个事务获取同一范围的间隙锁，而且间隙锁虽然有X型和S型之分，但是功能上并没有差别，两个X型的间隙锁也是可以兼容的。

但是对于next-key lock来说，如果范围相同的两个X型的next-key lock，是不可以兼容的。比如两个X型的范围是(5,15]的next-key lock是不兼容的。

有个例外是，两个X型的范围是(1006, +∞]的next-key lock是兼容的

insert加行级锁

insert语句会加什么行级锁？

insert语句正常执行，没有遇到冲突时是不会行级锁加锁的，是不会生成锁结构的，它是靠聚簇索引记录自带的trx_id字段作为隐式锁来保护记录的，但是某些情况下隐式锁会转换为显示锁。

• 当记录之前有间隙锁，insert在这个间隙锁范围内插入记录时会生成插入意向锁
• 如果insert的记录和已经存在的记录之间存在唯一性冲突，则会给已经存在的记录加锁

记录之间有间隙锁

每次进行insert的时候，都需要看一下待插入记录的下一条记录上是否加了间隙锁，如果加了间隙锁，那此时insert是会被阻塞的。而且此时会生成一个插入意向锁，然后将锁的状态设置为等待状态。（MySQL加锁时是先生成锁结构，再设置锁状态，如果锁的状态是等待状态，表明该事务并没有真正获取到锁。只有锁的状态是正常状态时，才表明该事务获取到锁了）

表中记录：

先在事务A中执行：select * from t_order where order_no=1008 for update;

我们知道会生成(1006,1010)的间隙锁：

然后在事务B中插入1008的记录：insert into t_order values(8,1008,now());

这时候就因为插入的记录在间隙锁的范围内，就发生了冲突，就会生成插入意向锁：

可以看到锁的状态是WATING，表明并没有成功获取到锁。

插入意向锁和间隙锁是互斥关系，是不兼容的。当事务A持有某一范围的间隙锁时，事务B再获取这个范围的插入意向锁就会阻塞。

唯一键冲突

当插入记录时，如果插入的记录与表中已有记录的唯一字段的值相同的记录，则会对表中记录加上S型锁。

如果主键索引重复，则会给表中已存在的记录加S型的记录锁

如果唯一二级索引重复，则会给表中已存在的记录加S型的next-key lock

主键索引重复：

表中记录：（id是主键）

如果再插入一条id为1的记录，则会发生主键冲突： insert into t_order values(1,1001,now());

这条记录是不能成功插入的，而且会给表中已有的id为1的记录加S型的记录锁：

🐢所以在插入记录时，如果发生主键索引重复这种冲突，则会给表中已有的记录加上S型的记录锁

唯一二级索引重复：

还是上面这张表，给其中的order_no字段添加了唯一二级索引，然后执行：

insert into t_order values(7,1002,now());

因为1002在表中已经存在，所以会报错，而且还会给表中已有记录加S型的next-key lock：