MySQL-SQL全部锁详解（下）

墨蓝 2023-10-12 21:12 5阅读 0赞

> ♥️**作者：小刘在C站**
> 
> ♥️**个人主页：[ 小刘主页 ][Link 1]**
> 
> ♥️**努力不一定有回报，但一定会有收获加油！一起努力，共赴美好人生！**
> 
> ♥️**学习两年总结出的运维经验，以及思科模拟器全套网络实验教程。专栏：**[云计算技术][Link 2]
> 
> **♥️小刘私信可以随便问，只要会绝不吝啬，感谢CSDN让你我相遇！**

**前言**

**上章讲述了MySQL-SQL语言中的全部锁上章，本章完结继续[上一张点这里][Link 3][上一张点这里][Link 3]**

**目录**

4 行级锁

4.1 介绍

编辑

4.2 行锁

1). 介绍

2). 演示

示例演示

A. 普通的select语句，执行时，不会加锁。

B. select...lock in share mode，加共享锁，共享锁与共享锁之间兼容。

C. 排它锁与排他锁之间互斥

D. 无索引行锁升级为表锁

4.3 间隙锁&临键锁

示例演示

介绍分析一下：

--------------------

### **4** **行级锁** ###

#### **4.1** **介绍** ####

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB 存储引擎中。

InnoDB 的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

行锁（ Record Lock ）：锁定单个行记录的锁，防止其他事务对此行进行 update 和 delete 。在

RC 、 RR 隔离级别下都支持。

![2cb7c3d8001d4d4bb90778b9025bf039.png][]

间隙锁（ Gap Lock ）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事

务在这个间隙进行 insert ，产生幻读。在 RR 隔离级别下都支持。

![d0e442cd78a049c993d952e0742550a2.png][]

临键锁（ Next-Key Lock ）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙 Gap 。

在 RR 隔离级别下支持。

## ![d66e633b933f4cdca10dbd1dbaee5f8b.png][] ##

## **4.2** **行锁** ##

#### 1). 介绍  ####

InnoDB 实现了以下两种类型的行锁：

共享锁（S ）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。

排他锁（ X ）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他

锁。

两种行锁的兼容情况如下 :

![13537bbcb7c8427497fb56e46271c130.png][]

常见的SQL语句，在执行时，所加的行锁如下：

![6aeaf846f71b41d3b4a499398607ee01.png][]

#### 2). 演示  ####

默认情况下， InnoDB 在  REPEATABLE READ 事务隔离级别运行， InnoDB 使用  next-key  锁进行搜

索和索引扫描，以防止幻读。

针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。

InnoDB 的行锁是针对于索引加的锁，不通过索引条件检索数据，那么 InnoDB 将对表中的所有记

录加锁，此时 就会升级为表锁。

可以通过以下 SQL ，查看意向锁及行锁的加锁情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
    performance_schema.data_locks;

### **示例演示**  ###

数据准备 :

CREATE TABLE `stu` (
    `id` int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` varchar(255) DEFAULT NULL,
    `age` int NOT NULL
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4;
    INSERT INTO `stu` VALUES (1, 'tom', 1);
    INSERT INTO `stu` VALUES (3, 'cat', 3);
    INSERT INTO `stu` VALUES (8, 'rose', 8);
    INSERT INTO `stu` VALUES (11, 'jetty', 11);
    INSERT INTO `stu` VALUES (19, 'lily', 19);
    INSERT INTO `stu` VALUES (25, 'luci', 25);

演示行锁的时候，我们就通过上面这张表来演示一下。

### A. 普通的select语句，执行时，不会加锁。 ###

![8ba2c81d0cf9401e83948b64e67a184a.png][]

### B. select...lock in share mode，加共享锁，共享锁与共享锁之间兼容。  ###

![3865686ace0140bfa3890ffb5b1dd8b7.png][]

共享锁与排他锁之间互斥。

![3a085f746f9145169fc85cb106e499b5.png][]

客户端一获取的是 id 为 1 这行的共享锁，客户端二是可以获取 id 为 3 这行的排它锁的，因为不是同一行数据。 而如果客户端二想获取 id 为 1 这行的排他锁，会处于阻塞状态，以为共享锁与排他锁之间互 斥。

### C. 排它锁与排他锁之间互斥 ###

![52a1d8a11b1042c2af58748b9b16bf53.png][]

当客户端一，执行 update 语句，会为 id 为 1 的记录加排他锁； 客户端二，如果也执行 update 语句更

新 id 为 1 的数据，也要为 id 为 1 的数据加排他锁，但是客户端二会处于阻塞状态，因为排他锁之间是互斥的。 直到客户端一，把事务提交了，才会把这一行的行锁释放，此时客户端二，解除阻塞。

### D. 无索引行锁升级为表锁  ###

stu 表中数据如下 :

![c87300db640249a6a8f01dcf7b839055.png][]

我们在两个客户端中执行如下操作:

![090c5dc48aac4d58b66d7d1494e50987.png][]

在客户端一中，开启事务，并执行 update 语句，更新 name 为 Lily 的数据，也就是 id 为 19 的记录 。

然后在客户端二中更新 id 为 3 的记录，却不能直接执行，会处于阻塞状态，为什么呢？

原因就是因为此时，客户端一，根据 name 字段进行更新时， name 字段是没有索引的，如果没有索引，  此时行锁会升级为表锁 ( 因为行锁是对索引项加的锁，而 name 没有索引 ) 。

接下来，我们再针对 name 字段建立索引，索引建立之后，再次做一个测试：

![6f7aaefa09ff49b383944dacf07d8014.png][]

此时我们可以看到，客户端一，开启事务，然后依然是根据 name 进行更新。而客户端二，在更新 id 为 3的数据时，更新成功，并未进入阻塞状态。 这样就说明，我们根据索引字段进行更新操作，就可以避免行锁升级为表锁的情况。

### **4.3** **间隙锁****&****临键锁**  ###

默认情况下， InnoDB 在  REPEATABLE READ 事务隔离级别运行， InnoDB 使用  next-key  锁进行搜

索和索引扫描，以防止幻读。

索引上的等值查询 ( 唯一索引 ) ，给不存在的记录加锁时 ,  优化为间隙锁 。

索引上的等值查询 ( 非唯一普通索引 ) ，向右遍历时最后一个值不满足查询需求时， next-key

lock  退化为间隙锁。

索引上的范围查询 ( 唯一索引 )-- 会访问到不满足条件的第一个值为止。

> 注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

### **示例演示** ###

A.  索引上的等值查询 ( 唯一索引 ) ，给不存在的记录加锁时 ,  优化为间隙锁 。

![7d3c203f5a77481ca7b7d2935637cca0.png][]

B.  索引上的等值查询 ( 非唯一普通索引 ) ，向右遍历时最后一个值不满足查询需求时， next-key

lock  退化为间隙锁。

#### 介绍分析一下： ####

我们知道 InnoDB 的 B+ 树索引，叶子节点是有序的双向链表。 假如，我们要根据这个二级索引查询值为 18 的数据，并加上共享锁，我们是只锁定 18 这一行就可以了吗？ 并不是，因为是非唯一索引，这个结构中可能有多个 18 的存在，所以，在加锁时会继续往后找，找到一个不满足条件的值（当前案例中也  就是 29 ）。此时会对 18 加临键锁，并对 29 之前的间隙加锁。

![9bb69bf64919407cbe5f7024fb896af2.png][]

![eaf472d5cba540b3b871ebc70e420e76.png][]

C. 索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。

![455b55d73b854308b87e043cbfa78176.png][]

查询的条件为 id>=19 ，并添加共享锁。 此时我们可以根据数据库表中现有的数据，将数据分为三个部分：

\[19\]

(19,25\]

(25,+∞\]

所以数据库数据在加锁是，就是将 19 加了行锁， 25 的临键锁（包含 25 及 25 之前的间隙），正无穷的临键锁 ( 正无穷及之前的间隙 ) 。

> ♥️关注，就是我创作的动力
> 
> ♥️点赞，就是对我最大的认可
> 
> ♥️这里是小刘，励志用心做好每一篇文章，谢谢大家

[Link 1]: https://blog.csdn.net/lzl10211345?type=blog
[Link 2]: https://blog.csdn.net/lzl10211345/category_12064749.html?spm=1001.2014.3001.5482
[Link 3]: http://t.csdn.cn/M5LtH
[2cb7c3d8001d4d4bb90778b9025bf039.png]: https://img-blog.csdnimg.cn/2cb7c3d8001d4d4bb90778b9025bf039.png
[d0e442cd78a049c993d952e0742550a2.png]: https://img-blog.csdnimg.cn/d0e442cd78a049c993d952e0742550a2.png
[d66e633b933f4cdca10dbd1dbaee5f8b.png]: https://img-blog.csdnimg.cn/d66e633b933f4cdca10dbd1dbaee5f8b.png
[13537bbcb7c8427497fb56e46271c130.png]: https://img-blog.csdnimg.cn/13537bbcb7c8427497fb56e46271c130.png
[6aeaf846f71b41d3b4a499398607ee01.png]: https://img-blog.csdnimg.cn/6aeaf846f71b41d3b4a499398607ee01.png
[8ba2c81d0cf9401e83948b64e67a184a.png]: https://img-blog.csdnimg.cn/8ba2c81d0cf9401e83948b64e67a184a.png
[3865686ace0140bfa3890ffb5b1dd8b7.png]: https://img-blog.csdnimg.cn/3865686ace0140bfa3890ffb5b1dd8b7.png
[3a085f746f9145169fc85cb106e499b5.png]: https://img-blog.csdnimg.cn/3a085f746f9145169fc85cb106e499b5.png
[52a1d8a11b1042c2af58748b9b16bf53.png]: https://img-blog.csdnimg.cn/52a1d8a11b1042c2af58748b9b16bf53.png
[c87300db640249a6a8f01dcf7b839055.png]: https://img-blog.csdnimg.cn/c87300db640249a6a8f01dcf7b839055.png
[090c5dc48aac4d58b66d7d1494e50987.png]: https://img-blog.csdnimg.cn/090c5dc48aac4d58b66d7d1494e50987.png
[6f7aaefa09ff49b383944dacf07d8014.png]: https://img-blog.csdnimg.cn/6f7aaefa09ff49b383944dacf07d8014.png
[7d3c203f5a77481ca7b7d2935637cca0.png]: https://img-blog.csdnimg.cn/7d3c203f5a77481ca7b7d2935637cca0.png
[9bb69bf64919407cbe5f7024fb896af2.png]: https://img-blog.csdnimg.cn/9bb69bf64919407cbe5f7024fb896af2.png
[eaf472d5cba540b3b871ebc70e420e76.png]: https://img-blog.csdnimg.cn/eaf472d5cba540b3b871ebc70e420e76.png
[455b55d73b854308b87e043cbfa78176.png]: https://img-blog.csdnimg.cn/455b55d73b854308b87e043cbfa78176.png