MySQL InnoDB锁算法，可算搞明白了

2023-12-03 15:08
文章标签 算法 mysql innodb database 明白 可算

本文主要是介绍MySQL InnoDB锁算法，可算搞明白了，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

在Innodb事务隔离性实现原理，你了解吗？里讲了行锁，但Innodb除了行锁还有间隙锁（Gap Lock）和next-key lock。这些锁之间有什么关系呢？我们经常会听到全局锁、表级锁、行锁，这些又是什么？这篇文章带你搞懂这些内容。本篇如果不做特别说明，隔离级别都为可重复读。

锁级别

MySQL锁级别有三类：全局锁、表级锁、行锁。

全局锁

对整个数据库实例加锁

  • 加锁命令：Flush tables with read lock (FTWRL)

  • 解锁命令：

  • unlock tables

  • 断开加锁session的连接

  • 目的：使整个库处于只读状态

  • 规则：全局锁加锁成功，会阻塞所有级别的写锁，读锁不受影响

  • 使用场景：如做全库逻辑备份

  • 影响：主库加锁，导致任何更新操作都会被阻塞，业务停摆；从库加锁，导致主从延迟

表级别锁

对表进行加锁，有三种：

  • 两种来自MySQL，为表锁和元数据锁（meta data lock，MDL）

  • 一种来自Innodb，为意向锁，分意向共享锁(IS)和意向排它锁(IX)

表锁

  • 加锁命令：lock tables … read/write

  • 解锁命令：

  • unlock tables

  • 断开加锁session的连接

  • 目的：使指定表处于只读、只写状态

  • 规则：

    | 读锁 | 写锁 |
    | — | — | — |
    | 读锁 | 不冲突 | 冲突 |
    | 写锁 | 冲突 | 冲突 |

  • 使用场景：如无行锁的存储引擎可用来处理并发问题

  • 影响：lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象，即如果设置表为只读，本线程也无写的能力

MDL

  • 加解锁：MDL不需要显式使用，在访问一个表的时候会被自动加上

  • 目的：保证读写的正确性，方便处理DDL操作和DML操作的冲突

  • 规则：当对一个表做增删改查操作的时候，自动加 MDL读锁；当要对表做结构变更操作的时候，自动加 MDL 写锁。MDL读锁和大家平时理解的读锁不一样，增删改查加的都是读锁。

    | MDL读锁 | MDL写锁 |
    | — | — | — |
    | MDL读锁 | 不冲突 | 冲突 |
    | MDL写锁 | 冲突 | 冲突 |

意向锁

  • 加解锁：意向锁不需要显式使用，在访问一个表的时候会被自动加上

  • 事务想要给数据库某些行加共享锁，需要先加上意向共享锁

  • 事务想要给数据库某些行加互斥锁，需要先加上意向互斥锁

  • 目的：为了实现多粒度锁，和表锁做冲突。当加行锁的时候，同时加了意向锁，这样要加表锁的时候，根据意向锁的情况，能迅速断定能否加表锁。

  • 规则：意向锁不会与行级的共享 / 排他锁互斥

    | 意向共享锁 | 意向排它锁 |
    | — | — | — |
    | 意向共享锁 | 不冲突 | 冲突 |
    | 意向排它锁 | 冲突 | 冲突 |

行级别锁

对数据表中行记录加锁。MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如MyISAM 引擎就不支持行锁。

  • 加锁：select lock in share mode(共享锁S), select for update ; update, insert ,delete(排它锁X)

  • 解锁：事务commit后释放

  • 目的：做并发控制

  • 规则：

| 共享锁 | 排它锁 |
| — | — | — |
| 共享锁 | 不冲突 | 冲突 |
| 排它锁 | 冲突 | 冲突 |

冲突

本想把所有的锁冲突情况汇集在一起，不过有些锁之间是共生关系，没想好怎么整理。大家如果有好的建议可以留言说一下。

| FTWRL | 表读锁 | 表写锁 | MDL读锁 | MDL写锁 | 意向共享锁 | 意向排它锁 | 行共享锁 | 行排它锁 |
| — | — | — | — | — | — | — | — | — | — |
| FTWRL | | | | | | | | | |
| 表读锁| | | | | | | | | |
| 表写锁 | | | | | | | | | |
| MDL读锁 | | | | | | | | | |
| MDL写锁 | | | | | | | | | |
| 意向共享锁 | | | | | | | | | |
| 意向排它锁| | | | | | | | | |
| 行共享锁| | | | | | | | | |
| 行排它锁| | | | | | | | | |

锁算法

怎么计算哪些行是需要加锁的呢？这就涉及到锁的算法。

三种

首先我们要知道，锁是加在索引上的，一张表可能有多个索引，根据情况不同，一个语句产生的锁可能加在一个或多个索引上。

锁算法有三种，分别为：

行锁：单个行记录上的锁

Gap Lock：间隙锁，锁定一个范围，但不包含记录本身；间隙锁之间没有冲突；间隙锁在可重复读隔离级别下才有效

Next key Lock：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身

加锁规则

下面是林晓斌总结出的规则，适用于 5.x 系列 <=5.7.24，8.0 系列 <=8.0.13。

为两个“原则”、两个“优化”和一个“bug”。

  1. 原则 1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。

  2. 原则 2：查找过程中访问到的对象才会加锁。

  3. 优化 1：索引上的等值查询，给主键/唯一索引加锁，数据中查找到指定行，next-key lock退化为行锁。

  4. 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next key lock 退化为间隙锁。

  5. 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

即使不看代码，这些规则也很符合解决问题的思路。

  1. 以next-key lock为基本单位，因为它是范围最广的锁，如果锁的范围大了，后面删减

  2. 如果是主键/唯一索引上等值查询，如果找到对应行，说明最多只会有这一行，退化成行锁毫无问题，还能减少锁的范围

  3. 如果索引上等值查询，最后一个值不满足等值条件，肯定不必锁

  4. 只有访问的对象才加锁，因为只需要保证当前语句重复执行时获取到的数据是不变的即可，如果语句没用到其它索引，所以自然不必管其它对象

  5. 两个没想通的地方

  • 一是唯一索引上的范围查询会访问到不满足条件的第一个值为止，根据唯一索引的特性，理论上不需要再继续查找了

  • 二是对索引上的范围查找，如c>10 && c<15，如果最后一个值不满足条件，理论上也可退化为间隙锁

实例

让我们根据实例，验证一下加锁规则。

先创建表结构与数据：

CREATE TABLE `t` (`id` int(11) NOT NULL,`c` int(11) DEFAULT NULL,`d` int(11) DEFAULT NULL,PRIMARY KEY (`id`),KEY `c` (`c`)
) ENGINE=InnoDB;insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

根据主键/唯一索引、普通索引、无索引，进行等值查询、范围查询，查看锁定情况：

等值查询范围查询
主键/唯一索引请添加图片描述最终在主键，锁住范围为(5,10)。因为是在主键索引上进行查找，所以无需从头查找，直接定位到(5,10]，根据原则1加next key lock；而又因为是等值查询，最后一个值为10，不等于7，根据优化2，退化为间隙锁。请添加图片描述最终在主键锁住[10,15]。因为从>=10，锁(5,10]，根据优化1只锁10，对于<11，锁住(10,15]。请添加图片描述最终在主键锁住(10,20]。因为>10，锁(10,15]；根据bug，会访问到不满足条件的第一个值为止，所以继续锁(15,20]。
普通索引请添加图片描述最终在索引C，锁住范围(0,10)。因为在C索引上进行查找，根据原则1，锁(0,5]，向右继续遍历，锁(5,10]；根据优化2，退化为(5,10)，最终锁定范围为(0,10)；因为session A只select id，C为覆盖索引，所以根本不需要遍历主键索引，根据原则2，主键索引没有加锁，所以实现了session B和session C的效果。请添加图片描述最终索引C和主键索引锁住(5,15]。因为C>=10，锁住(5,10]，向右继续查找，锁住(10,15]，同时会对主键进行加锁。
无索引可认为锁[负无穷，正无穷]可认为锁[负无穷，正无穷]

根据对实例的分析，大家加锁的时候，尽量加在主键/唯一索引上，并确保值确实存在。在这种情况下，添加的是行锁，冲突最小。

锁查看

不同版本查看方案加锁方案不一致，对于8.0版本，

  1. 可用 select * from performance_schema.data_locks 进行查看。

  2. 可查看锁日志：

    show variables like ‘innodb_status_output’;

    set GLOBAL innodb_status_output=ON;

    set GLOBAL innodb_status_output_locks=ON;

    show engine innodb status ;

幻读

间隙锁在可重复读隔离级别下才有效。next key lock能够解决幻读问题。如果没有这些锁算法，则重复读隔离级别下，读出的行数可能不同，违反了可重复读的含义。

在MySQL官方文档中，将不可重复读定义为Phantom Problem，即幻象问题。

在Next-Key Lock算法下，对于索引的扫描，不仅仅是锁住扫描到的索引，而且还锁住这些索引覆盖的范围（gap）。因此对于这个范围内的插入都是不允许的。这样就避免了另外的事务在这个范围内插入数据导致的不可重复读的问题。

有两个重点内容需要再说明一下：

  1. 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。

  2. 幻读仅专指“新插入的行”。

资料

  1. mysql的innodb有哪几种锁_MySQL InnoDB 引擎中的 7 种锁类型，你都知道吗？

  2. MySQL 全局锁和表锁是什么

  3. MySQL学习笔记（三）—数据库意向锁和元数据锁

  4. mysql锁系列之全局锁

  5. MySQL技术内幕：InnoDB存储引擎

  6. mysql中查看sql语句的加锁信息

  7. mysql sys 查看锁_mysql8.0查看锁信息

  8. Mysql 锁表日志查看

最后

大家如果喜欢我的文章，可以关注我的公众号（程序员麻辣烫）

我的个人博客为：https://shidawuhen.github.io/

图片

往期文章回顾：

  1. 设计模式

  2. 招聘

  3. 思考

  4. 存储

  5. 算法系列

  6. 读书笔记

  7. 小工具

  8. 架构

  9. 网络

  10. Go语言

这篇关于MySQL InnoDB锁算法，可算搞明白了的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/449877。

相关文章

MySQL主从同步延迟问题的全面解决方案

MySQL主从同步延迟问题的全面解决方案

《MySQL主从同步延迟问题的全面解决方案》MySQL主从同步延迟是分布式数据库系统中的常见问题,会导致从库读取到过期数据,影响业务一致性,下面我将深入分析延迟原因并提供多层次的解决方案,需要的朋友可... 目录一、同步延迟原因深度分析1.1 主从复制原理回顾1.2 延迟产生的关键环节二、实时监控与诊断方案
阅读更多...
慢sql提前分析预警和动态sql替换-Mybatis-SQL

慢sql提前分析预警和动态sql替换-Mybatis-SQL

《慢sql提前分析预警和动态sql替换-Mybatis-SQL》为防止慢SQL问题而开发的MyBatis组件,该组件能够在开发、测试阶段自动分析SQL语句,并在出现慢SQL问题时通过Ducc配置实现动... 目录背景解决思路开源方案调研设计方案详细设计使用方法1、引入依赖jar包2、配置组件XML3、核心配
阅读更多...
MySQL数据库约束深入详解

MySQL数据库约束深入详解

《MySQL数据库约束深入详解》：本文主要介绍MySQL数据库约束,在MySQL数据库中,约束是用来限制进入表中的数据类型的一种技术,通过使用约束,可以确保数据的准确性、完整性和可靠性,需要的朋友... 目录一、数据库约束的概念二、约束类型三、NOT NULL 非空约束四、DEFAULT 默认值约束五、UN
阅读更多...
MySQL 多表连接操作方法(INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN)

MySQL 多表连接操作方法(INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN)

《MySQL多表连接操作方法(INNERJOIN、LEFTJOIN、RIGHTJOIN、FULLOUTERJOIN)》多表连接是一种将两个或多个表中的数据组合在一起的SQL操作,通过连接,... 目录一、 什么是多表连接？二、 mysql 支持的连接类型三、 多表连接的语法四、实战示例 数据准备五、连接的性
阅读更多...
MySQL中的分组和多表连接详解

MySQL中的分组和多表连接详解

《MySQL中的分组和多表连接详解》：本文主要介绍MySQL中的分组和多表连接的相关操作,本文通过实例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录mysql中的分组和多表连接一、MySQL的分组（group javascriptby ）二、多表连接（表连接会产生大量的数据垃圾）MySQL中的
阅读更多...
MySQL 中的 JSON 查询案例详解

MySQL 中的 JSON 查询案例详解

《MySQL中的JSON查询案例详解》：本文主要介绍MySQL的JSON查询的相关知识,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql 的 jsON 路径格式基本结构路径组件详解特殊语法元素实际示例简单路径复杂路径简写操作符注意MySQL 的 J
阅读更多...
Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

《Windows上如果忘记了MySQL密码重置密码的两种方法》：本文主要介绍Windows上如果忘记了MySQL密码重置密码的两种方法,本文通过两种方法结合实例代码给大家介绍的非常详细,感... 目录方法 1：以跳过权限验证模式启动 mysql 并重置密码方法 2：使用 my.ini 文件的临时配置在 Wi
阅读更多...
MySQL重复数据处理的七种高效方法

MySQL重复数据处理的七种高效方法

《MySQL重复数据处理的七种高效方法》你是不是也曾遇到过这样的烦恼：明明系统测试时一切正常,上线后却频频出现重复数据,大批量导数据时,总有那么几条不听话的记录导致整个事务莫名回滚,今天,我就跟大家分... 目录1. 重复数据插入问题分析1.1 问题本质1.2 常见场景图2. 基础解决方案：使用异常捕获3.
阅读更多...
SQL中redo log 刷⼊磁盘的常见方法

SQL中redo log 刷⼊磁盘的常见方法

《SQL中redolog刷⼊磁盘的常见方法》本文主要介绍了SQL中redolog刷⼊磁盘的常见方法,将redolog刷入磁盘的方法确保了数据的持久性和一致性,下面就来具体介绍一下,感兴趣的可以了解... 目录Redo Log 刷入磁盘的方法Redo Log 刷入磁盘的过程代码示例（伪代码）在数据库系统中，r
阅读更多...
mysql中的group by高级用法

mysql中的group by高级用法

《mysql中的groupby高级用法》MySQL中的GROUPBY是数据聚合分析的核心功能,主要用于将结果集按指定列分组,并结合聚合函数进行统计计算,下面给大家介绍mysql中的groupby用法... 目录一、基本语法与核心功能二、基础用法示例1. 单列分组统计2. 多列组合分组3. 与WHERE结合使
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2