MySQL：MDL LOCK的“穿越行為”
一、案例描述
最近遇到這么一個案例（5.7版本），大概的截圖如下：
當然這里是測試出來的，線上當時也是一個lock table read的語句和一大批insert into的語句被堵塞，當然要恢復很簡單，我們可以通過2種方式，來查殺堵塞源頭，如下：
- table 級別的MDL LOCK，除非手動發送lock table，那么其持續時間通常為事務級別，而innodb_trx中記錄了全部的事務（只讀和讀寫），因此我們可以通過innodb_trx查詢事務持續時間長于processlist中“Waiting for table metadata lock”最長時間的session的事務通常就是堵塞源頭（當然也有例外，這個以后再討論）。
- 訪問sys.schema_table_lock_waits進行判斷，如果為5.7需要手動開啟MDL LOCK的instrument，并且需要注意本視圖只能檢查table 級別的MDL LOCK，也就是本例中的“Waiting for table metadata lock”。
二、測試堵塞
但是在測試中，我們發現如下的執行順序insert是可以執行表結構和數據：
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事務不提交）
S1 | S2 | S3 |
begin; select * from t1 where id=1 for update; 不提交 | ||
lock table t1 read; 堵塞 | ||
insert into t1 values( 7,7,7); 插入成功 |
查看session狀態如下：
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 |
+----+-----------------+-----------+------+---------+------+---------------------------------+--------------------+-----------+---------------+這里可以看到這里只有lock table read的S2 處于堵塞狀態，而S3的insert的語句并沒有堵塞，那么案例中的insert 堵塞語句是哪里來的呢？
2. 模擬（S1提交）
S1 | S2 | S3 |
begin; select * from t1 where id=1 for update; 接著將這個事務提交 | ||
lock table t1 read; 執行成功 | ||
insert into t1 values( 9,9,9); 堵塞 |
查看session狀態如下：
+----+-----------------+-----------+------+---------+------+---------------------------------+-------------------------------+-----------+---------------+
| 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 |
+----+-----------------+-----------+------+---------+------+---------------------------------+-------------------------------+-----------+---------------這個時候因為lock table read執行成功了，insert語句繼續插入行則被MDL LOCK堵塞了。
三、問題匯總和分析
- 問題1：為什么模擬中lock table table read堵塞后，insert 可以執行？
- 問題2：為什么模擬中lock table table read執行成功后，insert會被堵塞？
- 問題3：為什么案例中lock table table read被堵塞后，insert也被堵塞？
我們來一個問題一個問題的講述。首先我們要知道MDL LOCK 有2個矩陣，一個為優先級矩陣，一個為兼容矩陣，當判斷是否能過獲取的MDL LOCK的時候需要調用MDL_lock::can_grant_lock函數進行判斷，其判斷的主要邏輯就是：
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)) 首先想看優先級矩陣，然后再看兼容矩陣，其中優先級矩陣為：
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 | - - - - - - - - - - |當然期間有unobtrusive類型的MDL LOCK，這部分主要是優化MDL LOCK系統性能的，并不改變優先級和兼容性。
1. 問題1
這個問題我們按照時間序列進行描述：
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位圖比對，根據的是優先級矩陣對比成功，可以嘗試獲取，然后和grant位圖(fast lock)，比對的兼容矩陣，對比成功可以獲取。因此insert 是可以執行的 |
實際上這里s3的insert因為優先級矩陣并不會被堵塞中的MDL_SHARED_READ_ONLY(SRO)堵塞如下：
Request | Pending requests for lock |
type | S SH SR SW SWLP SU SRO SNW SNRW X |
----------+--------------------------------------------+
SW | + + + + + + + - - - |而比對兼容矩陣的時候同樣MDL_SHARED_WRITE(SW)和MDL_SHARED_WRITE(SW)是兼容的因此就執行成功了,
Request | Granted requests for lock |
type | S SH SR SW SWLP SU SRO SNW SNRW X |
----------+---------------------------------------------+
SW | + + + + + + - - - - |這就看起來像S3的insert語句“穿越”了S2的堵塞，成功獲取了MDL LOCK一樣，實際上就是優先級矩陣的判定。
2. 問題2
有了問題1的基礎，問題2我們可以直接看兼容矩陣，因為S1事務提交了，S2的lock table table read執行成功了，這個S3插入數據，實際上就是看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
這個問題實際上和lock table read 一大批表有關，因為加MDL LOCK鎖并不是一氣呵成的。比如 lock t1 read,t2 read,t3 read,t4 read，其中t4 有一個for update事務，這個時候t1\t2\t3 的lock table table read就可以能執行成功，而整個語句堵塞在t4的mdl lock上，而其他session如果對t1,t2,t3進行insert 則也是會堵塞的。測試如下：
S1 | S2 | S3 |
begin; select * from tin for update; 不提交 | ||
lock table t999 read,test read ,tin read; 這里因為tin不能獲取MDL LOCK成功，所以語句堵塞，但是t999和test獲取MDL LOCK成功了 | ||
insert into t999 values('a'); 堵塞，因為lock table t999 read執行成功了。這里肯定就堵塞。 | ||
這通常和mysqldump 分庫導出表沒有去掉lock-tables有關，這會導致一個庫的所有表現執行lock table read操作，因此我們要用--single-transaction來取掉這個加鎖的操作， |
Option automatically turns off --lock-tables這也是實際案例中的遇到的問題。
四、總結
本案例中我們得到幾個結論：
- 語句是否能夠執行主要看的優先級矩陣和兼容矩陣，前者用于判斷本次執行的語句和堵塞中的MDL LOCK誰的優先級更高，優先級更高則可以繼續判斷兼容矩陣。后者用于判定本次執行的語句和獲取MDL LOCK的語句（或者事務）是否兼容。
- lock table read 一大批表的時候，可能某些表加鎖成功了，而某些表加鎖堵塞了，看起來是整個lock table read語句堵塞了。
- mysqldump導出如果全是innodb表肯定是要--single-transaction的。
