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加锁的区别有哪些”有了一定的了解，如果想了解更多相关知识，欢迎关注亿速云行业资讯频道，感谢各位的阅读！