独家揭秘丨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社区GiteeGitHubBilibili

GreatSQL社区:

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

技术交流群:

微信:扫码添加GreatSQL社区助手微信好友,发送验证信息加群

作者:GreatSQL原文地址:https://www.cnblogs.com/greatsql/p/18371426

%s 个评论

要回复文章请先登录注册