MySQL：MDL LOCK的“穿越行為”
一、案例描述
最近遇到這么一個(gè)案例（5.7版本），大概的截圖如下：
當(dāng)然這里是測(cè)試出來的，線上當(dāng)時(shí)也是一個(gè)lock table read的語句和一大批insert into的語句被堵塞，當(dāng)然要恢復(fù)很簡(jiǎn)單，我們可以通過2種方式，來查殺堵塞源頭，如下：
- table 級(jí)別的MDL LOCK，除非手動(dòng)發(fā)送lock table，那么其持續(xù)時(shí)間通常為事務(wù)級(jí)別，而innodb_trx中記錄了全部的事務(wù)（只讀和讀寫），因此我們可以通過innodb_trx查詢事務(wù)持續(xù)時(shí)間長(zhǎng)于processlist中“Waiting for table metadata lock”最長(zhǎng)時(shí)間的session的事務(wù)通常就是堵塞源頭（當(dāng)然也有例外，這個(gè)以后再討論）。
- 訪問sys.schema_table_lock_waits進(jìn)行判斷，如果為5.7需要手動(dòng)開啟MDL LOCK的instrument，并且需要注意本視圖只能檢查table 級(jí)別的MDL LOCK，也就是本例中的“Waiting for table metadata lock”。
二、測(cè)試堵塞
但是在測(cè)試中，我們發(fā)現(xiàn)如下的執(zhí)行順序insert是可以執(zhí)行表結(jié)構(gòu)和數(shù)據(jù)：
CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`)
)
mysql> select * from t1;
+----+------+------+
| id | a | b |
+----+------+------+
| 1 | 1 | 1 |
| 3 | 3 | 3 |
| 5 | 5 | 5 |
+----+------+------+1.模擬（S1事務(wù)不提交）
S1 | S2 | S3 |
begin; select * from t1 where id=1 for update; 不提交 | ||
lock table t1 read; 堵塞 | ||
insert into t1 values( 7,7,7); 插入成功 |
查看session狀態(tài)如下：
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+---------------------------------+--------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+-----------------+-----------+------+---------+------+---------------------------------+--------------------+-----------+---------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 1046 | Waiting on empty queue | NULL | 0 | 0 |
| 3 | root | localhost | new | Query | 0 | starting | show processlist | 0 | 0 |
| 4 | root | localhost | new | Query | 392 | Waiting for table metadata lock | lock table t1 read | 0 | 0 |
| 5 | root | localhost | new | Sleep | 341 | | NULL | 0 | 0 |
+----+-----------------+-----------+------+---------+------+---------------------------------+--------------------+-----------+---------------+這里可以看到這里只有l(wèi)ock table read的S2 處于堵塞狀態(tài)，而S3的insert的語句并沒有堵塞，那么案例中的insert 堵塞語句是哪里來的呢？
2. 模擬（S1提交）
S1 | S2 | S3 |
begin; select * from t1 where id=1 for update; 接著將這個(gè)事務(wù)提交 | ||
lock table t1 read; 執(zhí)行成功 | ||
insert into t1 values( 9,9,9); 堵塞 |
查看session狀態(tài)如下：
+----+-----------------+-----------+------+---------+------+---------------------------------+-------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+-----------------+-----------+------+---------+------+---------------------------------+-------------------------------+-----------+---------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 1325 | Waiting on empty queue | NULL | 0 | 0 |
| 3 | root | localhost | new | Query | 0 | starting | show processlist | 0 | 0 |
| 4 | root | localhost | new | Sleep | 671 | | NULL | 0 | 0 |
| 5 | root | localhost | new | Query | 4 | Waiting for table metadata lock | insert into t1 values( 9,9,9) | 0 | 0 |
+----+-----------------+-----------+------+---------+------+---------------------------------+-------------------------------+-----------+---------------這個(gè)時(shí)候因?yàn)閘ock table read執(zhí)行成功了，insert語句繼續(xù)插入行則被MDL LOCK堵塞了。
三、問題匯總和分析
- 問題1：為什么模擬中l(wèi)ock table table read堵塞后，insert 可以執(zhí)行？
- 問題2：為什么模擬中l(wèi)ock table table read執(zhí)行成功后，insert會(huì)被堵塞？
- 問題3：為什么案例中l(wèi)ock table table read被堵塞后，insert也被堵塞？
我們來一個(gè)問題一個(gè)問題的講述。首先我們要知道MDL LOCK 有2個(gè)矩陣，一個(gè)為優(yōu)先級(jí)矩陣，一個(gè)為兼容矩陣，當(dāng)判斷是否能過獲取的MDL LOCK的時(shí)候需要調(diào)用MDL_lock::can_grant_lock函數(shù)進(jìn)行判斷，其判斷的主要邏輯就是：
if (!(m_waiting.bitmap() & waiting_incompat_map))
{
if (! (fast_path_granted_bitmap() & granted_incompat_map)) //unobtrusive類型的MDL LOCK
{
if (! (m_granted.bitmap() & granted_incompat_map)) 首先想看優(yōu)先級(jí)矩陣，然后再看兼容矩陣，其中優(yōu)先級(jí)矩陣為：
Request | Pending requests for lock |
type | S SH SR SW SWLP SU SRO SNW SNRW X |
----------+--------------------------------------------+
S | + + + + + + + + + - |
SH | + + + + + + + + + + |
SR | + + + + + + + + - - |
SW | + + + + + + + - - - |
SWLP | + + + + + + - - - - |
SU | + + + + + + + + + - |
SRO | + + + - + + + + - - |
SNW | + + + + + + + + + - |
SNRW | + + + + + + + + + - |
X | + + + + + + + + + + |兼容矩陣為：
Request | Granted requests for lock |
type | S SH SR SW SWLP SU SRO SNW SNRW X |
----------+---------------------------------------------+
S | + + + + + + + + + - |
SH | + + + + + + + + + - |
SR | + + + + + + + + - - |
SW | + + + + + + - - - - |
SWLP | + + + + + + - - - - |
SU | + + + + + - + - - - |
SRO | + + + - - + + + - - |
SNW | + + + - - - + - - - |
SNRW | + + - - - - - - - - |
X | - - - - - - - - - - |當(dāng)然期間有unobtrusive類型的MDL LOCK，這部分主要是優(yōu)化MDL LOCK系統(tǒng)性能的，并不改變優(yōu)先級(jí)和兼容性。
1. 問題1
這個(gè)問題我們按照時(shí)間序列進(jìn)行描述：
S1 | S2 | S3 |
begin; select * from t1 where id=1 for update; 不提交，獲取MDL_SHARED_WRITE(SW)類型獲取成功 | ||
lock table t1 read; 堵塞 MDL_SHARED_READ_ONLY(SRO)類型鎖獲取失敗堵塞，放入到wait位圖中 | ||
insert into t1 values( 7,7,7); 插入成功其需要的為MDL_SHARED_WRITE(SW)類型的鎖首先和wait位圖比對(duì)，根據(jù)的是優(yōu)先級(jí)矩陣對(duì)比成功，可以嘗試獲取，然后和grant位圖(fast lock)，比對(duì)的兼容矩陣，對(duì)比成功可以獲取。因此insert 是可以執(zhí)行的 |
實(shí)際上這里s3的insert因?yàn)閮?yōu)先級(jí)矩陣并不會(huì)被堵塞中的MDL_SHARED_READ_ONLY(SRO)堵塞如下：
Request | Pending requests for lock |
type | S SH SR SW SWLP SU SRO SNW SNRW X |
----------+--------------------------------------------+
SW | + + + + + + + - - - |而比對(duì)兼容矩陣的時(shí)候同樣MDL_SHARED_WRITE(SW)和MDL_SHARED_WRITE(SW)是兼容的因此就執(zhí)行成功了,
Request | Granted requests for lock |
type | S SH SR SW SWLP SU SRO SNW SNRW X |
----------+---------------------------------------------+
SW | + + + + + + - - - - |這就看起來像S3的insert語句“穿越”了S2的堵塞，成功獲取了MDL LOCK一樣，實(shí)際上就是優(yōu)先級(jí)矩陣的判定。
2. 問題2
有了問題1的基礎(chǔ)，問題2我們可以直接看兼容矩陣，因?yàn)镾1事務(wù)提交了，S2的lock table table read執(zhí)行成功了，這個(gè)S3插入數(shù)據(jù)，實(shí)際上就是看MDL_SHARED_READ_ONLY(SRO)是否和MDL_SHARED_WRITE(SW)兼容，如下，
Request | Granted requests for lock |
type | S SH SR SW SWLP SU SRO SNW SNRW X |
----------+---------------------------------------------+
SW | + + + + + + - - - - |可以看到并不兼容，因此堵塞
3. 問題3
這個(gè)問題實(shí)際上和lock table read 一大批表有關(guān)，因?yàn)榧覯DL LOCK鎖并不是一氣呵成的。比如 lock t1 read,t2 read,t3 read,t4 read，其中t4 有一個(gè)for update事務(wù)，這個(gè)時(shí)候t1\t2\t3 的lock table table read就可以能執(zhí)行成功，而整個(gè)語句堵塞在t4的mdl lock上，而其他session如果對(duì)t1,t2,t3進(jìn)行insert 則也是會(huì)堵塞的。測(cè)試如下：
S1 | S2 | S3 |
begin; select * from tin for update; 不提交 | ||
lock table t999 read,test read ,tin read; 這里因?yàn)閠in不能獲取MDL LOCK成功，所以語句堵塞，但是t999和test獲取MDL LOCK成功了 | ||
insert into t999 values('a'); 堵塞，因?yàn)閘ock table t999 read執(zhí)行成功了。這里肯定就堵塞。 | ||
這通常和mysqldump 分庫(kù)導(dǎo)出表沒有去掉lock-tables有關(guān)，這會(huì)導(dǎo)致一個(gè)庫(kù)的所有表現(xiàn)執(zhí)行l(wèi)ock table read操作，因此我們要用--single-transaction來取掉這個(gè)加鎖的操作， |
Option automatically turns off --lock-tables這也是實(shí)際案例中的遇到的問題。
四、總結(jié)
本案例中我們得到幾個(gè)結(jié)論：
- 語句是否能夠執(zhí)行主要看的優(yōu)先級(jí)矩陣和兼容矩陣，前者用于判斷本次執(zhí)行的語句和堵塞中的MDL LOCK誰的優(yōu)先級(jí)更高，優(yōu)先級(jí)更高則可以繼續(xù)判斷兼容矩陣。后者用于判定本次執(zhí)行的語句和獲取MDL LOCK的語句（或者事務(wù)）是否兼容。
- lock table read 一大批表的時(shí)候，可能某些表加鎖成功了，而某些表加鎖堵塞了，看起來是整個(gè)lock table read語句堵塞了。
- mysqldump導(dǎo)出如果全是innodb表肯定是要--single-transaction的。
