MySQL5.6與MySQL5.7中語句lock table ...read加鎖的區別有哪些

小編給大家分享一下MySQL5.6與MySQL5.7中語句lock table ...read加鎖的區別有哪些，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

背景：最近在測試lock table xxx read與DML之間的鎖等待測試，突然發現mysql5.6與mysql5.7的show full processlist與show engine innodb status \G的顯示不相同
一個為 wait for table lock 而另外一個則是waiting for table metadata lock，甚是費解，于是就去追查了下 。

現象描述：
1.MySQL5.6
SESSION 1：


SESSION 2：


show full processlist與show engine innodb status \G的輸出（顯示為waiting for table level lock）



2.MySQL 5.7
SESSION 1：


SESSION 2：


show full processlist與show engine innodb status\G（顯示為waiting for table metadata lock）





分析：
在MySQL5.6中，我們從show engine innodb status以及show full processlist中都能看到狀態為waiting for table level lock，說明是一個事物表級鎖
而在MySQL5.7中，我們只從show full processlist看到了鎖等待，是waiting for table metadata lock，說明這是一個metadata lock(元數據鎖)而不是一個事物鎖
按本人對5.6理解是，session1語句lock table locktest1 read在MDL階段會加上MDL_SHARED_READ，而session2的update語句在MDL階段會加MDL_SHARED_WRITE,因此在MDL階段不會沖突，接下來，session1會在locktest1表上上S鎖（表鎖），而session2會在locktest1表上加IX鎖，由于IX鎖與session1的表級鎖S鎖沖突，故而在等待，結果為waiting for table level lock；然而如下5.7的顯示是waiting for metadata lock，是發生在MDL階段的鎖等待，這讓我很費解，是MySQL5.7鎖機制改變了呢？還是oracle官方的一個顯示bug（按理來說，oracle不會出現這么低級的錯誤）？
從mysql5.7.3起，在performance_schema庫中新增了metadata_locks表，用來監控MDL（metadata lock）的鎖情況，我們來追蹤一下：


從圖中，我們看出，MySQL5.7對于lock table locktest1 read 加的是SHARED_READ_ONLY（而MySQL5.7新增的類型）,與update語句的SHARED_WRITE互斥，導致了update在MDL階段等待，所以狀態為waiting for table metadata lock。
那為什么SHARED_READ_ONLY會與SHARED WRITE 互斥呢？我們從源代碼中找出描述，如下：
  /*
    A shared metadata lock for cases when we need to read data from table
    and block all concurrent modifications to it (for both data and metadata).
    Used by LOCK TABLES READ statement.
  */
  MDL_SHARED_READ_ONLY,

即：  MDL_SHARED_READ_ONLY會阻止所有的并發修改，（包括數據以及元數據）。

看完了這篇文章，相信你對“MySQL5.6與MySQL5.7中語句lock table ...read加鎖的區別有哪些”有了一定的了解，如果想了解更多相關知識，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！