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

  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佇列)
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

image-20230105161905827

技術交流群:

微信:掃碼新增GreatSQL社群助手微信好友,傳送驗證資訊加群

image-20221030163217640

相關文章