MySQL 由於MDL讀鎖select被阻塞

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

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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章