首页 > 数据库 >MySQL是怎么加锁的

MySQL是怎么加锁的

时间：2024-12-06 20:32:39浏览次数：4  
标签：怎么 加锁 记录 Lock 查询 索引 MySQL id

1. 全局锁

1.1 什么是全局锁？

全局锁是一种一次性锁住整个数据库的锁定机制。一旦加上全局锁，整个数据库的所有表都会处于只读状态，这意味着所有修改操作（如INSERT、UPDATE、DELETE）都会被阻塞。

常用的SQL命令：

  • FLUSH TABLES WITH READ LOCK (FTWRL)
    • 这个命令会加全局读锁，让所有表进入只读模式。
    • 常用于做全库备份（逻辑备份）。

全局锁的特点：

  • 影响范围大： 影响整个库，所有写入操作都会阻塞，甚至影响分布式事务协调。
  • 性能开销高： 加锁期间可能导致服务变慢或挂起。
  • 非必要时少用： 通常只用于短时间操作，比如一致性快照备份。

1.2 全局锁与行级锁的区别

特性全局锁行级锁
作用范围整个数据库单条或多条记录
加锁代价高低
并发影响严重，所有写入被阻塞较小，影响特定记录
适用场景数据库备份常规事务处理

2. 行级锁

2.1 行级锁的概念

        行级锁是 MySQL 中最细粒度的锁机制之一，只锁定与操作相关的行，不会影响其他行。它能够显著提升并发性能，是事务中最常见的锁定方式。

2.2 什么操作会加行级锁？

以下三类操作通常会触发行级锁：

  1. 锁定读（Select with Lock）

    • SQL 示例： 
      SELECT * FROM table_name WHERE condition FOR UPDATE;
 -- 加独占锁 
SELECT * FROM table_name WHERE condition LOCK IN SHARE MODE; 
-- 加共享锁
    • 作用：
      • FOR UPDATE：加独占锁，其他事务无法修改此行，适用于需要更新的场景。
      • LOCK IN SHARE MODE：加共享锁，允许其他事务只读，但禁止修改，适用于仅验证或读取数据的场景。

  2. UPDATE 和 DELETE 操作

    • 这些操作会自动对涉及的行加独占锁，防止其他事务对相同行进行操作。
    • 例如： 
      UPDATE table_name SET col = value WHERE condition; 
DELETE FROM table_name WHERE condition;

  3. 插入冲突时的加锁

    • 如果两个事务尝试插入相同的唯一索引值，MySQL 会对相关记录加锁以避免冲突。

2.3 共享锁与独占锁

  1. 共享锁 (Shared Lock, S锁)

    • 允许多个事务同时读取相同行的数据，但不允许修改。
    • 示例： 
      SELECT * FROM table_name WHERE condition LOCK IN SHARE MODE;

  2. 独占锁 (Exclusive Lock, X锁)

    • 阻止其他事务对相同行进行读取和修改。
    • 示例： 
      SELECT * FROM table_name WHERE condition FOR UPDATE; 
UPDATE table_name SET col = value WHERE condition;

共享锁与独占锁的冲突矩阵：

当前锁类型新锁类型是否允许加锁
无锁共享锁✅
无锁独占锁✅
共享锁共享锁✅
共享锁独占锁❌
独占锁共享锁❌
独占锁独占锁❌

2.4 行级锁的种类

  1. Record Lock

    • 锁定索引记录本身（行记录）。
    • 示例：如果索引值为 5 的记录被锁住，只有这条记录会受到影响。

  2. Gap Lock

    • 锁定索引范围之间的“间隙”，防止在间隙中插入新记录。
    • 示例：在范围 (1, 5) 之间加锁，其他事务不能在该范围内插入新记录。

  3. Next-Key Lock

    • 是 Record Lock 和 Gap Lock 的组合，锁定当前索引记录和紧邻的间隙。
    • 示例：如果锁定索引值 5，则范围 (4, 5] 和 (5, 6) 都会被锁住。

3. MySQL 是如何加行级锁的？

3.1 唯一索引等值查询

        唯一索引等值查询是 MySQL 中行级锁加锁的重要场景之一。MySQL 会根据查询条件和记录是否存在选择不同的加锁策略。

3.1.1 查询记录存在时的加锁情况

  • 加锁行为：
    唯一索引等值查询（如 SELECT * FROM table_name WHERE id = 5 FOR UPDATE），如果记录存在，MySQL 会对该记录加 Next-Key Lock。
    但是，由于查询条件是等值查询，且记录存在，锁会退化为 Record Lock，仅锁住当前记录，不涉及间隙部分。

  • 原因： Next-Key Lock 是 MySQL 默认的加锁机制，用于避免幻读（Phantom Read）。但等值查询记录已存在时，不会产生幻读，因此只需锁住记录本身即可，锁退化为 Record Lock。

  • 命令分析： 使用命令 SHOW ENGINE INNODB STATUS 查看当前锁的信息：

    SELECT * FROM table_name WHERE id = 5 FOR UPDATE; 
-- 查看加锁情况 
SHOW ENGINE INNODB STATUS;

3.1.2 查询记录不存在时的加锁情况

  • 加锁行为： 唯一索引等值查询（如 SELECT * FROM table_name WHERE id = 5 FOR UPDATE），如果记录不存在，MySQL 会在索引树中找到第一条大于该查询记录的索引，并对该记录的索引加 Next-Key Lock，但此锁会退化为 Gap Lock。

  • 原因：

    • 记录不存在时，为防止其他事务插入新的记录而破坏查询结果的唯一性，需要锁住目标记录对应的间隙。
    • 由于间隙锁不会锁住实际记录，插入 id = 5 的操作会被阻止。

  • 间隙范围的确定：

    • 假设索引为 (1, 3, 7)，查询 id = 5：
      • 在索引树中，id = 7 是第一个大于 5 的记录。
      • MySQL 会锁住间隙 (3, 7)。

3.2 间隙锁的范围

  • 间隙锁的定义： 间隙锁（Gap Lock）锁住的是索引区间，而不是实际的记录。

  • 间隙范围确定规则：

    • 在索引 (1, 3, 7) 中：
      • 查询 id = 4 会锁住 (3, 7)。
      • 查询 id = 8 会锁住 (7, ∞)。

  • 重要提示： 如果目标索引在索引树中是最后一条记录，那么间隙范围会包括正无穷。例如，查询 id > 7 会锁住 (7, ∞)。

3.3 唯一索引范围查询

实验一：针对「大于」的范围查询

  • 场景： 查询 id > 3 的记录。假设索引为 (1, 3, 5, 7)。
  • 加锁行为：
    • 查询会锁住 (3, 5) 和 (5, 7) 的所有范围（Next-Key Lock）。
    • 如果记录 id = 3 存在，该索引不会退化为 Record Lock，因为这是范围查询。

实验二：针对「大于等于」的范围查询

  • 场景： 查询 id >= 3 的记录。
  • 加锁行为：
    • 锁住 (3, 5)，同时对 id = 3 的记录加 Record Lock。
    • 如果 id = 3 存在，它会被单独锁住，保证范围内的一致性。

3.4 非唯一索引查询

非唯一索引等值查询：记录不存在时

  • 场景： 查询 age = 22 时，索引中只有 (20, 25)。
  • 加锁行为：
    • MySQL 会锁住 (20, 25) 的间隙，阻止在该范围内插入 age = 22 的记录。

阻塞和非阻塞的规则：

  • 非阻塞：
    如果插入的记录刚好是间隙锁边界（如 age = 20 或 age = 25），不会被阻塞。
  • 阻塞：
    如果插入的记录在间隙内部（如 age = 22），会被阻塞。

3.5 唯一索引范围查询的实验分析

针对「小于或者小于等于」的范围查询

实验一：针对「小于」的范围查询，且查询条件值不存在表中

  • 场景：
    查询条件 id < 5，假设索引为 (1, 3, 7)。
  • 加锁行为：
    • 在索引树中，MySQL 会找到第一条大于查询条件的记录，即 id = 7。
    • 锁定范围 (1, 3] 和 (3, 7)。
    • 因为目标记录 id = 5 不存在，间隙锁 (3, 7) 防止其他事务插入 id = 5。

实验二：针对「小于等于」的范围查询，且查询条件值存在表中

  • 场景：
    查询条件 id <= 3，假设索引为 (1, 3, 7)。
  • 加锁行为：
    • MySQL 会锁住 (1, 3] 的间隙，同时对 id = 3 的记录加 Record Lock。
    • 结果：
      • (1, 3] 是 Next-Key Lock，保护间隙。
      • id = 3 是 Record Lock，保护查询结果的记录。

实验三：针对「小于」的范围查询，且查询条件值存在表中

  • 场景：
    查询条件 id < 3，假设索引为 (1, 3, 7)。
  • 加锁行为：
    • MySQL 会锁住 (1, 3) 的间隙，但不会锁定 id = 3 的记录本身。
    • 因为 id < 3 的范围查询，不包括 id = 3。

3.6 非唯一索引查询的实验分析

实验一：针对非唯一索引等值查询时，查询的值不存在的情况

  • 场景：
    查询条件为 age = 22，假设非唯一索引为 (20, 25)，事务 A 持有间隙锁 (20, 25)。

  • 加锁行为：

    • 事务 A 加锁 (20, 25)，阻止任何在该间隙内插入 age = 22 的记录。

  • 插入操作规则：

    • 允许插入边界值：
      • 插入 age = 20 或 age = 25 时，成功。
    • 阻止插入间隙值：
      • 插入 age = 22 或 age = 23 时，被阻塞。

实验二：针对非唯一索引等值查询时，查询的值存在的情况

  • 场景：
    查询条件为 age = 22，假设非唯一索引为 (20, 22, 25)。

  • 加锁行为：

    • 查询会在 (20, 22) 和 (22, 25) 的间隙加锁，防止其他事务插入相同范围内的记录。

  • 特殊点：

    • 与唯一索引不同，非唯一索引不会将等值查询的 Next-Key Lock 退化为 Record Lock。原因是非唯一索引可能有多个匹配结果，Next-Key Lock 必须锁住完整范围以防止幻读。

3.7 非唯一索引范围查询

实验：针对 age >= 22 的范围查询

  • 场景：
    查询条件为 age >= 22，假设非唯一索引为 (20, 22, 25)。
  • 加锁行为：
    • 查询会锁住 (22, 25) 的范围，同时对 age = 22 的记录加 Record Lock。

为什么不会退化为记录锁？

  • 即使 age = 22 是等值匹配，非唯一索引需要锁定范围 (22, 25) 以避免新插入的记录（如 age = 23）破坏查询结果的一致性。

3.8 无索引的查询

当查询中未使用索引时，MySQL 默认会对整张表加锁。

  • 行为：
    • 如果事务操作未命中索引，MySQL 会锁住所有扫描过的记录。
  • 示例： 
    SELECT * FROM table_name WHERE col = value FOR UPDATE;
    • 若 col 未建立索引，查询将锁定全表，影响性能。

总结：
        通过上述分析，我们发现 MySQL 加锁行为会根据查询类型、索引种类、目标记录的存在与否发生变化。无论是唯一索引还是非唯一索引，MySQL 都会优先通过加锁保护数据的一致性，同时尽可能减少锁的粒度以提升并发性能。

4. 总结：MySQL 加锁机制核心要点

        通过上述内容分析，我们可以从以下几个方面总结 MySQL 加锁机制的特点和开发中的优化建议。

4.1 MySQL 加锁机制的核心逻辑

  1. 锁的层次与范围

    • 全局锁：
      影响整个数据库，适用于一致性快照备份，但会严重影响并发性能，非必要情况应避免使用。
    • 表级锁：
      影响单张表的所有记录，例如 LOCK TABLES。适用于临时阻止表的并发写操作。
    • 行级锁：
      影响特定记录或索引范围，包括 Record Lock、Gap Lock 和 Next-Key Lock，是事务中最常用的锁机制。

  2. 不同操作的加锁行为

    • 等值查询：
      • 唯一索引：查询结果存在时，Next-Key Lock 退化为 Record Lock；结果不存在时，退化为 Gap Lock。
      • 非唯一索引：Next-Key Lock 不会退化，始终锁定查询范围，防止幻读。
    • 范围查询：
      始终加 Next-Key Lock，锁住所有可能的间隙与记录，保证数据一致性。
    • 无索引查询：
      扫描所有记录并加锁，会影响整个表的性能，应避免。

  3. 幻读与加锁策略

    • Next-Key Lock 是为了解决幻读问题，确保事务的一致性和隔离性。
    • 间隙锁（Gap Lock）用于阻止在索引范围内插入新记录，从而避免影响查询结果。

4.2 开发中的优化建议

  1. 选择合适的事务隔离级别

    • 在性能与一致性之间寻找平衡：
      • 如果业务对一致性要求高，使用 REPEATABLE READ。
      • 如果性能优先，可以考虑 READ COMMITTED，避免间隙锁的开销。

  2. 使用索引优化锁的范围

    • 优化查询语句，尽量使用索引覆盖查询，减少扫描范围。
    • 避免无索引查询导致全表锁。

  3. 合理设计索引结构

    • 唯一索引更适合需要高并发的场景，因为其加锁范围较小。
    • 对需要范围查询的字段，设计合适的联合索引，减少锁的粒度。

  4. 避免长时间持锁

    • 在事务中，尽量减少锁的持有时间：
      • 在事务中后置查询操作，优先完成逻辑处理。
      • 确保事务代码逻辑简洁高效。

  5. 利用分析工具检查锁状态

    • 使用 SHOW ENGINE INNODB STATUS 或性能分析工具，检查当前加锁情况，及时优化。

4.3 真实场景示例

  1. 防止插入冲突的案例

    • 场景：
      一个电商库存系统，需要查询商品库存并更新记录。
    • 问题：
      在查询和更新库存之间，可能有其他事务插入新的库存记录，导致幻读。
    • 解决方案：
      使用 FOR UPDATE 加独占锁，锁住目标记录，避免其他事务的修改。

  2. 避免死锁的案例

    • 场景：
      两个事务同时更新用户余额和订单状态。
    • 问题：
      锁的获取顺序不一致导致死锁。
    • 解决方案：
      • 明确加锁顺序，避免循环依赖。
      • 使用较短事务，快速释放锁。

4.4 核心理解与优化思路

  1. 锁的本质：
    锁是为了在高并发场景下，保障数据的一致性与隔离性，而设计的一种协调机制。

  2. 性能与一致性的平衡：

    • MySQL 提供多种锁机制，让我们根据业务需求灵活选择锁的粒度与类型。
    • 对于高并发场景，尽量减少锁的范围和持有时间。

  3. 工具化分析：

    • 借助 SHOW ENGINE INNODB STATUS、慢查询日志等工具，监控锁的使用情况。
    • 通过索引优化、SQL 优化等手段减少锁冲突。

        MySQL 的加锁机制在不同场景下会有复杂的行为逻辑，但其设计目标始终是提升并发性能的同时，保证事务的 ACID 特性。在开发中，掌握加锁的原理和特点，可以帮助我们优化查询性能、提升系统稳定性。

标签：怎么,加锁,记录,Lock,查询,索引,MySQL,id
From： https://blog.csdn.net/m0_53926113/article/details/144299479

相关文章

  • 《鸣潮》进入游戏后弹窗“错误代码[126]:加载 x3daudio1_7.dll 失败，该文件缺失或损坏!
     《鸣潮》进入游戏后弹窗“错误代码[126]:加载x3daudio1_7.dll失败，该文件缺失或损坏！”是什么原因？“x3daudio1_7.dll文件缺失”要怎么解决？作为一位软件开发从业者，深知电脑游戏运行过程中可能会遇到的各种问题，其中文件丢失、文件损坏和系统报错尤为常见。最近，不少《鸣潮》......
  • 【AIGC进阶提示词分享】下行周期，怎么样沟通才能保住饭碗？
    引言在现代职场中，沟通能力已成为衡量职场人专业素养的重要指标。话语的成熟度不仅反映了个人的职业素养，更直接影响工作效率和人际关系的建立。本文将深入分析职场话语成熟度的构成要素，并通过具体案例说明如何提升沟通的专业性。提示词和示例在下方提示词和示例在下方......
  • mysqldump 导出批量忽略某些表的bat脚本
    @echooff::启用了延迟变量扩展，允许在代码块中使用!variable!来访问变量的最新值。setlocalenabledelayedexpansionSETYYYY=%DATE:~0,4%SETMM=%DATE:~5,2%SETDD=%DATE:~8,2%echo%YYYY%-%MM%-%DD%  %TIME%::设置数据库连接信息setDB_USER=rootsetDB_PASSWORD=1234s......
  • Y20030029 Java+微信+SPRINGBOOT+MYSQL+LW+传统文化展示微信小程序的设计与开发 配置
    传统文化展示微信小程序1.项目摘要2.课题开发的背景和意义3.项目功能4.界面展示5.源码获取1.项目摘要基于微信小程序的传统文化展示小程序是一个集合了多种传统文化元素与现代化技术的创新平台。它充分利用了微信小程序的便捷性和普及性，为广大用户提供了一个深入......
  • AI绘画和视频怎么变现？学会AI绘画怎么赚钱，目前主要的变现方式有哪些？
    AI绘画和视频怎么变现？学会AI绘画怎么赚钱，目前主要的变现方式有哪些？1、AI绘画作品拍卖。AI也可以画画，而且画得还不错。有些AI画的画，甚至可以拍卖出高价。比如有一幅叫做“EdmonddeBelamy”的画，就是AI画的，它的拍卖价格竟然达到了43万美元。你说这是不是AI的一种炫富方式......
  • MySQL数据库写入异常，主库内存溢出，扩容+清理buff/cach！很开门
    MySQL数据库写入异常，主库内存溢出，扩容+清理buff/cach！很开门最近数据出现了两次写入异常报错如下：org.springframework,jdbc.UncategorizedsQlException:PreparedstatementCallback;uncategzed50LExcention[sql语句]TheMysOlserverisrunningwiththe--read-onlyoption......
  • 【MySQL运维DBA】【SQL基础系列002篇】
    文章目录DDL（数据定义语言）DCL（数据控制语言）DML（数据操作语言）联合查询（UNION）使用窗口函数联合查询结合窗口函数复杂的DML操作：更新操作结合子查询和窗口函数总结在MySQL中，DDL（数据定义语言）、DCL（数据控制语言）和DML（数据操作语言）是三种主要的SQL语言类别。然而，由于每种类别的......
  • 【MySQL运维DBA】【SQL基础系列001篇】
    文章目录一、SQL简介二、(My)SQL使用入门（一）SQL分类（二）DDL语句（四）DCL语句三、帮助的使用（一）按照层次看帮助（二）快速查阅帮助（三）常用的网络资源四、查询元数据信息五、小结一、SQL简介SQL（StructuredQueryLanguage）即结构化查询语言，是一种用于管理关系型数据库的标......
  • 国标GB28181软件LiteGBS国标GB28181-2022平台预览画面提示“获取监控点预览信息失败”
    随着视频技术的不断进步，视频监控、直播、执法记录仪等多种视频资源的应用场景愈发广泛且多样化。这些视频资源不仅在数量上快速增长，更在质量、格式及编码标准等方面展现出极高的多样性。因此，为了实现对这些资源的有效整合和统一管理输出，信息化项目中对于视频综合接入能力的需求愈......
  • Mysql8.0修改配置参数lower_case_table_names
    现象今天在配置一个环境的数据库，所使用的系统要求该数据库lower_case_table_names=1（对数据库表明、列名大小写不敏感）我看了一下，在Windows上，默认值为1。在macOS上，默认值是2。在Linux上，不支持值2；服务器会将该值设置为0。那0是不符合我们需求的，于是我打开my.cnf进......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99