MySQL 由於MDL讀鎖select被阻塞

哎呀我的天吶發表於2020-06-10
MySql

thread 1、begin；

更新表；沒有提交，也沒有回滾操作


thread2、create index 在這個表上

這時候客戶端超時中斷

再次連線會話查詢此表被阻塞，無法查詢


thread3、查詢 select * from test；

root@localhost : yaochong 17:08:27> select id,user,host,db,command,time,state,info from information_schema.processlist where user <>'system user' and info not like '%system user%';
+-------+------+-----------+----------+---------+------+---------------------------------+---------------------------------------------------------------+
| id    | user | host      | db       | command | time | state                           | info                                                          |
+-------+------+-----------+----------+---------+------+---------------------------------+---------------------------------------------------------------+
| 10161 | root | localhost | yaochong | Query   | 3386 | Waiting for table metadata lock | select * from test                                            |
| 10092 | root | localhost | yaochong | Query   | 6375 | Waiting for table metadata lock | alter table test add key(name) , ALGORITHM=INPLACE, LOCK=NONE |
+-------+------+-----------+----------+---------+------+---------------------------------+---------------------------------------------------------------+
2 rows in set (0.00 sec)


原因參考如下MDL的讀寫鎖互斥


MySQL 由於MDL讀鎖select被阻塞

為什麼C等待拿鎖之後，D也會阻塞？其實這裡並沒有解釋清楚。因為如果按併發理解的話，

C，D應當是同等級，都有可能拿到鎖的。但C讀寫鎖互斥，D讀讀不互斥，這樣的話就跟上圖所述相悖了。

就，查了一下。

（鳴謝 一夢如是YFL提供的文章）


首先是MDL（metaData Lock）的概念。後設資料鎖是server層的鎖，表級鎖，主要用於隔離DML（Data Manipulation Language，資料操縱語言，如select）和DDL（Data Definition Language，資料定義語言，如改表頭新增一列）操作之間的干擾。 每執行一條DML、DDL語句時都會申請MDL鎖， DML操作需要MDL讀鎖，DDL操作需要MDL寫鎖（MDL加鎖過程是系統自動控制，無法直接干預，讀讀共享，讀寫互斥，寫寫互斥）

申請MDL鎖的操作會形成一個佇列， 佇列中寫鎖獲取優先順序高於讀鎖 。一旦出現寫鎖等待，不但當前操作會被阻塞，同時還會阻塞後續該表的所有操作。事務一旦申請到MDL鎖後，直到事務執行完才會將鎖釋放。（這裡有種特殊情況如果事務中包含DDL操作，mysql會在DDL操作語句執行前，隱式提交commit，以保證該DDL語句操作作為一個單獨的事務存在，同時也保證後設資料排他鎖的釋放，例如id 44的語句改為<begin;alter table testok add z varchar(10) not Null;select * from testok;>，此時一旦alter語句執行完成會馬上提交事務（autocommit=1），後面的select就在本次事務之外，其執行完成後不會持有讀鎖）

這樣就能解釋通為什麼session C被阻塞後，session D也執行不了的原因了。


簡而言之MDL鎖互斥，select也要申請MDL的讀鎖，這一點真是有點噁心。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29990276/viewspace-2697452/，如需轉載，請註明出處，否則將追究法律責任。

相關文章