獨家揭秘丨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值,看具體業務需求。
Enjoy GreatSQL 😃
關於 GreatSQL
GreatSQL是適用於金融級應用的國內自主開源資料庫,具備高效能、高可靠、高易用性、高安全等多個核心特性,可以作為MySQL或Percona Server的可選替換,用於線上生產環境,且完全免費併相容MySQL或Percona Server。
相關連結: GreatSQL社群 Gitee GitHub Bilibili
GreatSQL社群:
社群部落格有獎徵稿詳情:https://greatsql.cn/thread-100-1-1.html
技術交流群:
微信:掃碼新增
GreatSQL社群助手
微信好友,傳送驗證資訊加群
。