独家揭秘丨 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 方法获取的锁存在这里面,这个用来存储表正在等待获取的锁。 |
类型 | 说明 |
---|---|
unobtrusive | S, SH, SR and SW,用 m_fast_path_state 计数,不保存具体锁信息。用 fast path 方法获取锁。用 m_fast_path_state 变量保存,不用 m_granted 队列保存锁 |
obtrusive | SU, 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_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 队列) | |||||||||
---|---|---|---|---|---|---|---|---|---|---|
S | SH | SR | SW | SWLP | SU | SRO | SNW | SNRW | X | |
S | + | + | + | + | + | + | + | + | + | - |
SH | + | + | + | + | + | + | + | + | + | - |
SR | + | + | + | + | + | + | + | + | - | - |
SW | + | + | + | + | + | + | - | - | - | - |
SWLP | + | + | + | + | + | + | - | - | - | - |
SU | + | + | + | + | + | - | + | - | - | - |
SRO | + | + | + | - | - | + | + | + | - | - |
SNW | + | + | + | - | - | - | + | - | - | - |
SNRW | + | + | - | - | - | - | - | - | - | - |
X | - | - | - | - | - | - | - | - | - | - |
waiting0 队列策略:m_waiting_incompatible [0],正常申请时候 waiting 队列的矩阵
请求类型 | 待完成 lock (m_waiting 队列) | |||||||||
---|---|---|---|---|---|---|---|---|---|---|
S | SH | SR | SW | SWLP | SU | SRO | SNW | SNRW | X | |
S | + | + | + | + | + | + | + | + | + | - |
SH | + | + | + | + | + | + | + | + | + | + |
SR | + | + | + | + | + | + | + | + | - | - |
SW | + | + | + | + | + | + | + | - | - | - |
SWLP | + | + | + | + | + | + | - | - | - | - |
SU | + | + | + | + | + | + | + | + | + | - |
SRO | + | + | + | - | + | + | + | + | - | - |
SNW | + | + | + | + | + | + | + | + | + | - |
SNRW | + | + | + | + | + | + | + | + | + | - |
X | + | + | + | + | + | + | + | + | + | + |
waiting1 队列策略:m_waiting_incompatible [1],使 SW 优先级比 SRO 低
请求类型 | 待完成 lock (m_waiting 队列) | |||||||||
---|---|---|---|---|---|---|---|---|---|---|
S | SH | SR | SW | SWLP | SU | SRO | SNW | SNRW | X | |
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 队列) | |||||||||
---|---|---|---|---|---|---|---|---|---|---|
S | SH | SR | SW | SWLP | SU | SRO | SNW | SNRW | X | |
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 队列) | |||||||||
---|---|---|---|---|---|---|---|---|---|---|
S | SH | SR | SW | SWLP | SU | SRO | SNW | SNRW | X | |
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 1 | session 2 | session 3 | session 4 | session 5 | session 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 1 | session 2 | session 3 | session 4 | session 5 | session 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 1 | session 2 | session 3 | session 4 | session 5 | session 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 1 | session 2 | session 3 | session 4 | session 5 | session 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 1 | session 2 | session 3 | session 4 | session 5 | session 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 1 | session 2 | session 3 | session 4 | session 5 | session 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 1 | session 2 | session 3 | session 4 | session 5 | session 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 1 | session 2 | session 3 | session 4 | session 5 | session 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 值,看具体业务需求。