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

• granted队列别的线程没有不兼容锁
  
• waiting队列没有更高等级的锁在等待
  

具体按照以下的矩阵表来选出mdl是否可以被申请，其中waiting策略有四个矩阵，这四个矩阵主要是为了防止低优先级的锁等待太久产生锁饥饿，因此按照锁类型的数量必要的时候进行等待锁策略升级，说明见以下。
策略矩阵说明m_granted_incompatible以下第一个兼容图m_waiting_incompatible[0]以下第二个兼容图m_waiting_incompatible[1]获取的piglet锁数量超过max_write_lock_countm_waiting_incompatible[2]获取的hog锁数量超过max_write_lock_countm_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 t2SH和S一样，读元数据，但优先级比排他锁高。如DESCtSR读元数据，且读表数据，如事务中select rowsSW读元数据，且更新表数据，如事务中update rowsSWLP优先级低于SRO，DML时加LOW_PRIORITYSU可升级锁，允许并发读写表数据。可读元数据，及读表数据。可以升级到SNW、SNR、X锁。用在alter table的第一阶段，不阻塞DML，防止其他DDLSRO只读锁，可读元数据，读表数据，但不可DDL和修改数据。如lock table readSNW读元数据及表数据，阻塞他人修改数据，可升级到X锁。用在ALTER TABLE第一阶段，拷贝原始表数据到新表，允许读但不允许更新SNRW读元数据，及读写数据，阻塞他人读写数据，例如lock table writeX排他锁，可以修改字典和数据，例如alter table具体策略矩阵图：（以下+号代表可以被满足，-号代表不能被满足需要进入waiing队列等待）
grangted队列策略：m_granted_incompatible
请求类型已经申请到的lock(m_granted队列)SSHSRSWSWLPSUSROSNWSNRWXS+++++++++-SH+++++++++-SR++++++++--SW++++++----SWLP++++++----SU+++++-+---SRO+++--+++--SNW+++---+---SNRW++--------X----------waiting0队列策略：m_waiting_incompatible[0]，正常申请时候waiting队列的矩阵
请求类型待完成lock(m_waiting队列)SSHSRSWSWLPSUSROSNWSNRWXS+++++++++-SH++++++++++SR++++++++--SW+++++++---SWLP++++++----SU+++++++++-SRO+++-++++--SNW+++++++++-SNRW+++++++++-X++++++++++waiting1队列策略：m_waiting_incompatible[1]，使SW优先级比SRO低
请求类型待完成lock(m_waiting队列)SSHSRSWSWLPSUSROSNWSNRWXS+++++++++-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队列)SSHSRSWSWLPSUSROSNWSNRWXS++++++++++SH++++++++++SR++++++++++SW++++++++++SWLP++++++-+++SU++++++++++SRO+++-++++++SNW+++---+++-SNRW++-----++-X-------+++waiting3队列策略：m_waiting_incompatible[3]，优先选择 SRO 锁，而非 SW/SWLP 锁。此外，除 SW/SWLP 之外，非“hog”锁优先于“hog”锁。
请求类型待完成lock(m_waiting队列)SSHSRSWSWLPSUSROSNWSNRWXS++++++++++SH++++++++++SR++++++++++SW++++++----SWLP++++++----SU++++++++++SRO++++++++++SNW+++++-+++-SNRW++-++--++-X---++--+++三、策略升级对实际执行的影响

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

1. greatsql> CREATE TABLE `t20` (
2.   `s1` int NOT NULL,
3.   `s2` varchar(100) DEFAULT NULL,
4.   `s3` timestamp(3) NULL DEFAULT NULL,
5.   `i` varchar(100) DEFAULT NULL,
6.   PRIMARY KEY (`s1`)
7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
9. 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，因此进行了一次waiting策略升级，升级为了策略1。
session 1session 2session 3session 4session 5session 6begin;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策略1lock 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 6begin;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策略0update t20 set i=15 where s1=15;继续等待commitsession 1session 2session 3session 4session 5session 6锁状态SHARED_READ_ONLY获取SHARED_READ_ONLY获取SHARED_WRITE获取SHARED_READ_ONLY获取SHARED_WRITE继续等待。用命令查看一下锁状态

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

复制代码

3、锁改变策略时机
锁唤醒时机，参考MDL_lock::reschedule_waiters：
锁唤醒时机从granted或者waiting队列remove_ticket别的线程申请锁的时候进行waiting策略升级别的线程锁释放别的线程锁降级可以看到上面的例子就是在commit以后执行了锁唤醒才导致了策略升级，于是产生了跟第一个例子不同的结果。
四、总结

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

Enjoy GreatSQL
来源:https://www.cnblogs.com/greatsql/p/18371426
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！