獨家揭秘丨GreatSQL 的MDL鎖策略升級對執行的影響

GreatSQL發表於2024-08-21

獨家揭秘丨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社群助手微信好友，傳送驗證資訊加群。

獨家揭秘丨GreatSQL 沒開Binlog時多執行緒插入資料效能劣化之謎
2024-07-19
SQL執行緒
執行緒和鎖,鎖升級
2020-11-15
執行緒
cluster factor對執行計劃的影響
2020-06-04
Git 分支策略與submodule對分支策略的影響
2018-11-05
Git
索引及排序對執行計劃的影響
2012-05-23
索引排序
try catch 對程式碼執行的效能影響
2015-10-30
使用者被鎖定不影響JOB的執行
2009-08-26
執行緒數目對資料庫的影響
2017-05-12
執行緒資料庫
oracle cardinality對於執行計劃的影響
2012-12-24
Oracle
not-null約束對執行計劃的影響
2012-05-21
Null
微信iOS客戶端升級核心對自定義分享的影響
2017-03-20
iOS客戶端
時區調整對job的執行時間的影響
2017-07-10
轉型策略對打造智慧園區的影響
2022-05-06
arraysize/TDU/SDU的設定對sql執行代價的影響
2011-05-31
SQL
實驗-資料分佈對執行計劃的影響.txt
2013-05-09
_complex_view_merging對執行計劃的影響
2009-12-09
View
synchronized鎖的升級
2020-12-20
synchronized
淺析影響專案執行的因素
2009-01-12
GreatSQL 的重新整理鎖
2024-07-29
SQL
MySQL 避免行鎖升級為表鎖——使用高效的索引
2017-04-14
MySql索引
Oracle優化案例-統計資訊對執行計劃的影響（十三）
2018-12-22
Oracle優化
引數Optimizer_index_cost_adj 對執行計劃的影響
2008-03-14
Index
淺析影響專案執行的因素（續）
2009-01-12
物聯網對醫療行業的影響
2022-05-16
行業
再說索引與Null值對於Hints及執行計劃的影響
2009-03-25
索引Null
收集統計資訊中的no_invalidate選項對執行計劃的影響
2014-07-11
SQL SERVER 2008 指定允許的對錶的鎖進行升級的方法
2010-07-28
SQLServer
【Oracle】-【COMMIT對索引的影響】-從trace看COMMIT對索引的影響
2013-07-31
OracleMIT索引
並行查詢對於響應時間的影響實驗
2010-05-08
並行
Oracle六大鎖模式級別越高影響越多
2012-06-20
Oracle模式
Linux讀寫執行許可權對目錄和檔案的影響
2018-09-10
Linux
伺服器的容錯性對伺服器執行有什麼影響
2023-02-02
伺服器
shrink 操作對索引的影響
2010-06-25
索引
Update操作對索引的影響
2009-06-08
索引
12張圖讀懂疫情對銀行業的影響
2020-03-01
行業
大資料對法律行業產生的影響
2016-08-22
大資料行業
【分析方法論】屬性對行為的影響分析
2017-04-19
對avalon的類名操作進行升級
2014-08-19

獨家揭秘丨GreatSQL 的MDL鎖策略升級對執行的影響

獨家揭秘丨GreatSQL 的MDL鎖策略升級對執行的影響

一、MDL鎖策略介紹

二、MDL策略級別

三、策略升級對實際執行的影響

四、總結

關於 GreatSQL

GreatSQL社群：

技術交流群：

相關文章