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

2023-12-03 15:08

本文主要是介绍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 8 中的一个强大功能 JSON_TABLE示例详解

《MySQL8中的一个强大功能JSON_TABLE示例详解》JSON_TABLE是MySQL8中引入的一个强大功能,它允许用户将JSON数据转换为关系表格式,从而可以更方便地在SQL查询中处理J... 目录基本语法示例示例查询解释应用场景不适用场景1. ‌jsON 数据结构过于复杂或动态变化‌2. ‌性能要

MySQL字符串常用函数详解

《MySQL字符串常用函数详解》本文给大家介绍MySQL字符串常用函数,本文结合实例代码给大家介绍的非常详细,对大家学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql字符串常用函数一、获取二、大小写转换三、拼接四、截取五、比较、反转、替换六、去空白、填充MySQL字符串常用函数一、

MySQL中比较运算符的具体使用

《MySQL中比较运算符的具体使用》本文介绍了SQL中常用的符号类型和非符号类型运算符,符号类型运算符包括等于(=)、安全等于(=)、不等于(/!=)、大小比较(,=,,=)等,感兴趣的可以了解一下... 目录符号类型运算符1. 等于运算符=2. 安全等于运算符<=>3. 不等于运算符<>或!=4. 小于运

虚拟机Centos7安装MySQL数据库实践

《虚拟机Centos7安装MySQL数据库实践》用户分享在虚拟机安装MySQL的全过程及常见问题解决方案,包括处理GPG密钥、修改密码策略、配置远程访问权限及防火墙设置,最终通过关闭防火墙和停止Net... 目录安装mysql数据库下载wget命令下载MySQL安装包安装MySQL安装MySQL服务安装完成

MySQL进行数据库审计的详细步骤和示例代码

《MySQL进行数据库审计的详细步骤和示例代码》数据库审计通过触发器、内置功能及第三方工具记录和监控数据库活动,确保安全、完整与合规,Java代码实现自动化日志记录,整合分析系统提升监控效率,本文给大... 目录一、数据库审计的基本概念二、使用触发器进行数据库审计1. 创建审计表2. 创建触发器三、Java

MySQL逻辑删除与唯一索引冲突解决方案

《MySQL逻辑删除与唯一索引冲突解决方案》本文探讨MySQL逻辑删除与唯一索引冲突问题,提出四种解决方案:复合索引+时间戳、修改唯一字段、历史表、业务层校验,推荐方案1和方案3,适用于不同场景,感兴... 目录问题背景问题复现解决方案解决方案1.复合唯一索引 + 时间戳删除字段解决方案2:删除后修改唯一字

Zabbix在MySQL性能监控方面的运用及最佳实践记录

《Zabbix在MySQL性能监控方面的运用及最佳实践记录》Zabbix通过自定义脚本和内置模板监控MySQL核心指标(连接、查询、资源、复制),支持自动发现多实例及告警通知,结合可视化仪表盘,可有效... 目录一、核心监控指标及配置1. 关键监控指标示例2. 配置方法二、自动发现与多实例管理1. 实践步骤

MySQL 主从复制部署及验证(示例详解)

《MySQL主从复制部署及验证(示例详解)》本文介绍MySQL主从复制部署步骤及学校管理数据库创建脚本,包含表结构设计、示例数据插入和查询语句,用于验证主从同步功能,感兴趣的朋友一起看看吧... 目录mysql 主从复制部署指南部署步骤1.环境准备2. 主服务器配置3. 创建复制用户4. 获取主服务器状态5

SpringBoot中六种批量更新Mysql的方式效率对比分析

《SpringBoot中六种批量更新Mysql的方式效率对比分析》文章比较了MySQL大数据量批量更新的多种方法,指出REPLACEINTO和ONDUPLICATEKEY效率最高但存在数据风险,MyB... 目录效率比较测试结构数据库初始化测试数据批量修改方案第一种 for第二种 case when第三种

MySql基本查询之表的增删查改+聚合函数案例详解

《MySql基本查询之表的增删查改+聚合函数案例详解》本文详解SQL的CURD操作INSERT用于数据插入(单行/多行及冲突处理),SELECT实现数据检索(列选择、条件过滤、排序分页),UPDATE... 目录一、Create1.1 单行数据 + 全列插入1.2 多行数据 + 指定列插入1.3 插入否则更