独家揭秘丨GreatSQL 的MDL锁策略升级对执行的影响

2024-08-21 14:36
文章标签 独家 执行 策略 升级 揭秘 影响 greatsql mdl

本文主要是介绍独家揭秘丨GreatSQL 的MDL锁策略升级对执行的影响，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

独家揭秘丨GreatSQL 的MDL锁策略升级对执行的影响

一、MDL锁策略介绍

GreatSQL 的MDL锁有个策略方法类MDL_lock_strategy，它根据对象的类型分为了scope类型和object类型，前者主要用于GLOBAL, COMMIT, TABLESPACE, BACKUP_LOCK and SCHEMA ,RESOURCE_GROUPS,FOREIGN_KEY,CHECK_CONSTRAINT,BACKUP_TABLES类型，后者主要用于DD表的锁表，本次主要介绍后者的策略原理和策略改变的动机以及对执行的影响。

MDL以表为单位进行锁表，包括3个主要的存储方式：m_fast_path_state位图、m_granted队列、m_waiting队列。

存储说明
m_fast_path_state用fast path方法获取的锁存在这里面
m_granted队列用slow path方法获取的锁存在这里面，在这之前需要先将fast path获取的锁从m_fast_path_state删除再存到这里面。这个用来存储表已经获取的锁。
m_waiting队列用slow path方法获取的锁存在这里面，这个用来存储表正在等待获取的锁。
类型说明
unobtrusiveS, SH, SR and SW，用m_fast_path_state计数，不保存具体锁信息。用fast path方法获取锁。用m_fast_path_state变量保存，不用m_granted队列保存锁
obtrusiveSU, SRO, SNW, SNRW, X，用slow path方法获取锁，用m_granted队列保存锁

二、MDL策略级别

mdl锁可以被申请条件：参考MDL_lock::can_grant_lock

  1. granted队列别的线程没有不兼容锁

  2. waiting队列没有更高等级的锁在等待

具体按照以下的矩阵表来选出mdl是否可以被申请，其中waiting策略有四个矩阵，这四个矩阵主要是为了防止低优先级的锁等待太久产生锁饥饿，因此按照锁类型的数量必要的时候进行等待锁策略升级，说明见以下。

策略矩阵说明
m_granted_incompatible以下第一个兼容图
m_waiting_incompatible[0]以下第二个兼容图
m_waiting_incompatible[1]获取的piglet锁数量超过max_write_lock_count
m_waiting_incompatible[2]获取的hog锁数量超过max_write_lock_count
m_waiting_incompatible[3]获取的piglet锁和hog锁总和数量超过max_write_lock_count
类型说明
独占型(hog)打算申请X, SNRW, SNW，别的锁在等待; 具有较强的不兼容性，优先级高，容易霸占锁，造成其他低优先级锁一直处于等待状态。m_hog_lock_count统计表申请到的hog类型锁
暗弱型(piglet)打算申请SW，SRO在等待; SW优先级仅高于SRO。m_piglet_lock_count统计表申请到的piglet类型锁
类型说明
S共享锁，读元数据，不读表数据，比如create table t1 like t2
SH和S一样，读元数据，但优先级比排他锁高。如DESCt
SR读元数据，且读表数据，如事务中select rows
SW读元数据，且更新表数据，如事务中update rows
SWLP优先级低于SRO，DML时加LOW_PRIORITY
SU可升级锁，允许并发读写表数据。可读元数据，及读表数据。可以升级到SNW、SNR、X锁。用在alter table的第一阶段，不阻塞DML，防止其他DDL
SRO只读锁，可读元数据，读表数据，但不可DDL和修改数据。如lock table read
SNW读元数据及表数据，阻塞他人修改数据，可升级到X锁。用在ALTER TABLE第一阶段，拷贝原始表数据到新表，允许读但不允许更新
SNRW读元数据，及读写数据，阻塞他人读写数据，例如lock table write
X排他锁，可以修改字典和数据，例如alter table

具体策略矩阵图：（以下+号代表可以被满足，-号代表不能被满足需要进入waiing队列等待）

grangted队列策略：m_granted_incompatible

请求类型已经申请到的lock(m_granted队列)
SSHSRSWSWLPSUSROSNWSNRWX
S+++++++++-
SH+++++++++-
SR++++++++--
SW++++++----
SWLP++++++----
SU+++++-+---
SRO+++--+++--
SNW+++---+---
SNRW++--------
X----------

waiting0队列策略：m_waiting_incompatible[0]，正常申请时候waiting队列的矩阵

请求类型待完成lock(m_waiting队列)
SSHSRSWSWLPSUSROSNWSNRWX
S+++++++++-
SH++++++++++
SR++++++++--
SW+++++++---
SWLP++++++----
SU+++++++++-
SRO+++-++++--
SNW+++++++++-
SNRW+++++++++-
X++++++++++

waiting1队列策略：m_waiting_incompatible[1]，使SW优先级比SRO低

请求类型待完成lock(m_waiting队列)
SSHSRSWSWLPSUSROSNWSNRWX
S+++++++++-
SH++++++++++
SR++++++++--
SW++++++----
SWLP++++++----
SU+++++++++-
SRO++++++++--
SNW+++++++++-
SNRW+++++++++-
X++++++++++

waiting2队列策略：m_waiting_incompatible[2],S, SH, SR, SW, SNRW, SRO and SU优先度比SNW、SNRW、X高

请求类型待完成lock(m_waiting队列)
SSHSRSWSWLPSUSROSNWSNRWX
S++++++++++
SH++++++++++
SR++++++++++
SW++++++++++
SWLP++++++-+++
SU++++++++++
SRO+++-++++++
SNW+++---+++-
SNRW++-----++-
X-------+++

waiting3队列策略：m_waiting_incompatible[3]，优先选择 SRO 锁，而非 SW/SWLP 锁。此外，除 SW/SWLP 之外，非“hog”锁优先于“hog”锁。

请求类型待完成lock(m_waiting队列)
SSHSRSWSWLPSUSROSNWSNRWX
S++++++++++
SH++++++++++
SR++++++++++
SW++++++----
SWLP++++++----
SU++++++++++
SRO++++++++++
SNW+++++-+++-
SNRW++-++--++-
X---++--+++

三、策略升级对实际执行的影响

当有多线程多资源在抢同一张表的锁资源的时候，如果想要低优先级的锁先得到授权，那么可以通过修改系统变量max_write_lock_count来实现目的。下面通过2个例子来看看修改max_write_lock_count如何影响多线程的锁等待动作。

首先创建一张表。

greatsql> CREATE TABLE `t20` (`s1` int NOT NULL,`s2` varchar(100) DEFAULT NULL,`s3` timestamp(3) NULL DEFAULT NULL,`i` varchar(100) DEFAULT NULL,PRIMARY KEY (`s1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;greatsql> INSERT INTO t2 VALUES (1,'aaaa','2021-01-19 03:14:07.123'),(2,null,'2022-01-19 03:14:07.123'),(3,'bbbb',null),(4,null,null),(15,'cccc','2025-01-19 03:14:07.123');

1、max_write_lock_count设置为100

SET GLOBAL max_write_lock_count=100; 打开6个session进行实验。分别敲入以下SQL命令。因为m_piglet_lock_count<max_write_lock_count因此以下的6个session都是执行waiting的策略0。

session 1session 2session 3session 4session 5session 6
begin;
update t20 set i=15 where s1=15;
lock table t20 read; 卡住
lock table t20 read; 卡住
update t20 set i=15 where s1=15;卡住
lock table t20 read; 卡住
update t20 set i=15 where s1=15;卡住
session 1session 2session 3session 4session 5session 6
锁状态SHARED_WRITE获取SHARED_READ_ONLY等待；SHARED_READ_ONLY等待SHARED_WRITE获取，虽然看到sql卡住，但是超时会主动报错。这里卡住是被innodb的行锁控制了SHARED_READ_ONLY等待；SHARED_WRITE等待

接着第一个session执行commit，观察一下后面几个session锁的变化，可以看到最后一个session的SW锁因为实行的是策略0因此commit之后按照SW优先度比SRO高获取到了SW锁。

session 1session 2session 3session 4session 5session 6
begin;
update t20 set i=15 where s1=15;
lock table t20 read; 成功
lock table t20 read; 成功
update t20 set i=15 where s1=15;成功
lock table t20 read; 成功
update t20 set i=15 where s1=15;成功
commit
session 1session 2session 3session 4session 5session 6
锁状态SHARED_READ_ONLY获取；SHARED_READ_ONLY获取；SHARED_WRITE获取SHARED_READ_ONLY获取；SHARED_WRITE获取

2、max_write_lock_count设置为1

SET GLOBAL max_write_lock_count=1; 这里在执行完session4的时候因为m_piglet_lock_count>=max_write_lock_count，因此进行了一次waiting策略升级，升级为了策略1。

session 1session 2session 3session 4session 5session 6
begin;
update t20 set i=15 where s1=15;
lock table t20 read; 卡住
lock table t20 read; 卡住
update t20 set i=15 where s1=15;卡住。这里转换为waiting策略1
lock table t20 read; 卡住
update t20 set i=15 where s1=15;卡住
session 1session 2session 3session 4session 5session 6
锁状态SHARED_WRITE获取SHARED_READ_ONLY等待；SHARED_READ_ONLY等待；SHARED_WRITE获取SHARED_READ_ONLY等待；SHARED_WRITE等待

接着第一个session执行commit释放SHARED_WRITE锁，可以看到最后一个session的SW锁应该在策略1优先度比SRO低，因此还处于等待状态。而在之前第一个例子里，因为实行的是策略0因此commit之后最后一个session因为优先度比SRO高因此获取到了SW锁。

在session5的SRO获取到锁以后，因为已经没有SRO锁在等待了，因此进行了一次waiting策略降级，重新降级为了0。

session 1session 2session 3session 4session 5session 6
begin;
update t20 set i=15 where s1=15;
lock table t20 read; 成功
lock table t20 read; 成功
update t20 set i=15 where s1=15;成功。
lock table t20 read; 成功。这里转换为waiting策略0
update t20 set i=15 where s1=15;继续等待
commit
session 1session 2session 3session 4session 5session 6
锁状态SHARED_READ_ONLY获取SHARED_READ_ONLY获取SHARED_WRITE获取SHARED_READ_ONLY获取SHARED_WRITE继续等待。

用命令查看一下锁状态

greatsql> SELECT * FROM performance_schema.metadata_locks where object_schema='db1' and object_name='t20';
+-------------+---------------+-------------+-------------+-----------------------+------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE        | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+---------------+-------------+-------------+-----------------------+------------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | db1           | t20         | NULL        |       140733798645792 | SHARED_READ_ONLY | TRANSACTION   | GRANTED     | sql_parse.cc:6723 |              73 |             20 |
| TABLE       | db1           | t20         | NULL        |       140733664568448 | SHARED_READ_ONLY | TRANSACTION   | GRANTED     | sql_parse.cc:6723 |              56 |             22 |
| TABLE       | db1           | t20         | NULL        |       140733327666736 | SHARED_READ_ONLY | TRANSACTION   | GRANTED     | sql_parse.cc:6723 |              75 |             27 |
| TABLE       | db1           | t20         | NULL        |       140733396820960 | SHARED_WRITE     | TRANSACTION   | PENDING     | sql_parse.cc:6723 |              77 |              9 |
+-------------+---------------+-------------+-------------+-----------------------+------------------+---------------+-------------+-------------------+-----------------+----------------+
# 最后一个session的SW锁在等待

3、锁改变策略时机

锁唤醒时机，参考MDL_lock::reschedule_waiters：

锁唤醒时机
从granted或者waiting队列remove_ticket
别的线程申请锁的时候进行waiting策略升级
别的线程锁释放
别的线程锁降级

可以看到上面的例子就是在commit以后执行了锁唤醒才导致了策略升级，于是产生了跟第一个例子不同的结果。

四、总结

实际生产中如果在多个线程抢同一张表的锁资源的时候，如果想要低优先级的锁优先获得锁，可以尝试修改系统变量max_write_lock_count，改小可以防止锁饥饿，但是可能会影响别的线程正在执行的业务，因此也要谨慎使用。当然如果想要高优先级锁先获得锁也可以改大max_write_lock_count值，看具体业务需求。

Enjoy GreatSQL :)

关于 GreatSQL

GreatSQL是适用于金融级应用的国内自主开源数据库，具备高性能、高可靠、高易用性、高安全等多个核心特性，可以作为MySQL或Percona Server的可选替换，用于线上生产环境，且完全免费并兼容MySQL或Percona Server。

相关链接： GreatSQL社区 Gitee GitHub Bilibili

GreatSQL社区：

image

社区有奖建议反馈： https://greatsql.cn/thread-54-1-1.html

社区博客有奖征稿详情： https://greatsql.cn/thread-100-1-1.html

（对文章有疑问或者有独到见解都可以去社区官网提出或分享哦~）

技术交流群：

微信&QQ群：

QQ群：533341697

微信群：添加GreatSQL社区助手（微信号：wanlidbc ）好友，待社区助手拉您进群。

这篇关于独家揭秘丨GreatSQL 的MDL锁策略升级对执行的影响的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


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

相关文章

大模型研发全揭秘：客服工单数据标注的完整攻略

大模型研发全揭秘：客服工单数据标注的完整攻略

在人工智能（AI）领域，数据标注是模型训练过程中至关重要的一步。无论你是新手还是有经验的从业者，掌握数据标注的技术细节和常见问题的解决方案都能为你的AI项目增添不少价值。在电信运营商的客服系统中，工单数据是客户问题和解决方案的重要记录。通过对这些工单数据进行有效标注，不仅能够帮助提升客服自动化系统的智能化水平，还能优化客户服务流程，提高客户满意度。本文将详细介绍如何在电信运营商客服工单的背景下进行
阅读更多...
揭秘世界上那些同时横跨两大洲的国家

揭秘世界上那些同时横跨两大洲的国家

我们在《世界人口过亿的一级行政区分布》盘点全球是那些人口过亿的一级行政区。 现在我们介绍五个横跨两州的国家，并整理七大洲和这些国家的KML矢量数据分析分享给大家，如果你需要这些数据，请在文末查看领取方式。 世界上横跨两大洲的国家 地球被分为七个大洲分别是亚洲、欧洲、北美洲、南美洲、非洲、大洋洲和南极洲。 七大洲示意图 其中，南极洲是无人居住的大陆，而其他六个大洲则孕育了众多国家和
阅读更多...
三国地理揭秘：为何北伐之路如此艰难，为何诸葛亮无法攻克陇右小城？

三国地理揭秘：为何北伐之路如此艰难，为何诸葛亮无法攻克陇右小城？

俗话说:天时不如地利，不是随便说说，诸葛亮六出祁山，连关中陇右的几座小城都攻不下来，行军山高路险，无法携带和建造攻城器械，是最难的，所以在汉中，无论从哪一方进攻，防守方都是一夫当关，万夫莫开；再加上千里运粮，根本不需要打，司马懿只需要坚守城池拼消耗就能不战而屈人之兵。 另一边，洛阳的虎牢关，一旦突破，洛阳就无险可守，这样的进军路线，才是顺势而为的用兵之道。 读历史的时候我们常常看到某一方势
阅读更多...
在JS中的设计模式的单例模式、策略模式、代理模式、原型模式浅讲

在JS中的设计模式的单例模式、策略模式、代理模式、原型模式浅讲

1. 单例模式（Singleton Pattern） 确保一个类只有一个实例，并提供一个全局访问点。 示例代码： class Singleton {constructor() {if (Singleton.instance) {return Singleton.instance;}Singleton.instance = this;this.data = [];}addData(value)
阅读更多...
无线领夹麦克风什么牌子好用？揭秘领夹麦克风哪个牌子音质好！

无线领夹麦克风什么牌子好用？揭秘领夹麦克风哪个牌子音质好！

随着短视频行业的星期，围绕着直播和视频拍摄的电子数码类产品也迎来了热销不减的高增长，其中除了数码相机外，最为重要的麦克风也得到了日益增长的高需求，尤其是无线领夹麦克风，近几年可谓是异常火爆。别看小小的一对无线麦克风，它对于视频拍摄的音质起到了极为关键的作用。 不过目前市面上的麦克风品牌种类多到让人眼花缭乱，盲目挑选的话容易踩雷，那么无线领夹麦克风什么牌子好用？今天就给大家推荐几款音质好的
阅读更多...
SWAP作物生长模型安装教程、数据制备、敏感性分析、气候变化影响、R模型敏感性分析与贝叶斯优化、Fortran源代码分析、气候数据降尺度与变化影响分析

SWAP作物生长模型安装教程、数据制备、敏感性分析、气候变化影响、R模型敏感性分析与贝叶斯优化、Fortran源代码分析、气候数据降尺度与变化影响分析

查看原文>>>全流程SWAP农业模型数据制备、敏感性分析及气候变化影响实践技术应用 SWAP模型是由荷兰瓦赫宁根大学开发的先进农作物模型，它综合考虑了土壤-水分-大气以及植被间的相互作用；是一种描述作物生长过程的一种机理性作物生长模型。它不但运用Richard方程，使其能够精确的模拟土壤中水分的运动，而且耦合了WOFOST作物模型使作物的生长描述更为科学。 本文让更多的科研人员和农业工作者
阅读更多...
负债不再是障碍？银行信贷“白名单“揭秘

负债不再是障碍？银行信贷“白名单“揭秘

谈及银行信贷产品，常闻有言称存在无需考量负债与查询记录之奇品，此等说法十有八九为中介诱人上钩之辞。轻信之下，恐将步入连环陷阱。除非个人资质出类拔萃，如就职于国央企或事业单位，工龄逾年，五险一金完备，还款能力卓越，或能偶遇线下产品对查询记录稍显宽容，然亦非全然无视。宣称全然不顾者，纯属无稽之谈。 银行非慈善机构，不轻易于困境中援手，更偏爱锦上添花之举。若无坚实资质，即便求助于银行亦难获青睐。反
阅读更多...
maven 编译构建可以执行的jar包

maven 编译构建可以执行的jar包

💝💝💝欢迎莅临我的博客，很高兴能够在这里和您见面！希望您在这里可以感受到一份轻松愉快的氛围，不仅可以获得有趣的内容和知识，也可以畅所欲言、分享您的想法和见解。 推荐：「stormsha的主页」👈，「stormsha的知识库」👈持续学习，不断总结，共同进步，为了踏实，做好当下事儿~ 专栏导航 Python系列: Python面试题合集，剑指大厂Git系列: Git操作技巧GO
阅读更多...
macOS升级后SVN升级

macOS升级后SVN升级

问题 svn: error: The subversion command line tools are no longer provided by Xcode. 解决 sudo chown -R $(whoami) /usr/local/Cellar brew install svn
阅读更多...
jenkins 插件执行shell命令时，提示“Command not found”处理方法

jenkins 插件执行shell命令时，提示“Command not found”处理方法

首先提示找不到“Command not found，可能我们第一反应是查看目标机器是否已支持该命令，不过如果相信能找到这里来的朋友估计遇到的跟我一样，其实目标机器是没有问题的通过一些远程工具执行shell命令是可以执行。奇怪的就是通过jenkinsSSH插件无法执行，经一番折腾各种搜索发现是jenkins没有加载/etc/profile导致。 【解决办法】： 需要在jenkins调用shell脚
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2