MySQL:MDL LOCK的“穿越行為”
來源：MySQL學習
一、案例描述
最近遇到這麼一個案例（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 |
+----+------+------+
2.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.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系統效能的，並不改變優先順序和相容性。
3.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一樣，實際上就是優先順序矩陣的判定。
3.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 問題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的。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70027826/viewspace-2993594/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL：理解MDL LockMySql
- mysql觀測METADATA LOCK(MDL)鎖MySql
- MySQL:Innodb 讓MDL LOCK和ROW LOCK 記錄到errlogMySql
- mysql lock操作MySql
- MySQL 由於MDL讀鎖select被阻塞MySql
- mysql innodb lock鎖之record lock之一MySql
- MySQL的GET_LOCK函式MySql函式
- MySQL 5.7從庫system lock執行緒解釋MySql執行緒
- Mysql DDL出現長時間等待MDL問題分析MySql
- MySQL-lock(鎖)-v2.0MySql
- MySQL5.7 Waiting for global read lockMySqlAI
- 一步步搞懂 MySQL 後設資料鎖（MDL）MySql
- 為什麼要這樣寫final ReentrantLock lock = this.lock; ？ReentrantLock
- 獨家揭秘丨GreatSQL 的MDL鎖策略升級對執行的影響SQL
- MySQL新增索引偶遇waiting for table metadata lockMySql索引AI
- MySQL更新卡住報錯lock wait timeoutMySqlAI
- MySQL DDL Waiting for table metadata lock 解決MySqlAI
- 穿越街道的女人
- “華為號”，決定穿越計算光年
- mysql關於FLUSH TABLES和FLUSH TABLES WITH READ LOCK的理解MySql
- MySQL RC隔離級別下罕見的gap lockMySql
- Ubuntu將Caps Lock改為CtrlUbuntu
- MySQL 共享鎖 (lock in share mode)，排他鎖 (for update)MySql
- mysql, Lock wait timeout exceeded; try restarting 解決MySqlAIREST
- NAT穿越
- mysql metadata lock後設資料鎖之鎖狀態lock_status流轉圖MySql
- 從Mysql slave system lock延遲說開去MySql
- MySql報錯：Lock wait timeout exceeded: try restadina transactionMySqlAIREST
- 資料庫週刊59丨GaussDB(for openGauss)開放商用；MDL鎖導致的MySQL問題分析……資料庫MySql
- 穿越邊界的姿勢
- BUUCTF：穿越時空的思念
- Innodb：為什麼lock in share mode在show engine看不到行鎖資訊
- MySQL：簡單記錄一下Waiting for commit lockMySqlAIMIT
- 我們為什麼需要 lock 檔案
- Java多執行緒學習（六）Lock鎖的使用Java執行緒
- MySQL表結構變更引起的Metadata Lock|如何定位DDL被阻塞MySql
- MySQL next-key lock 加鎖範圍是什麼？MySql
- 深入理解MDL後設資料鎖