MySQL:MDL LOCK的“穿越行为”

2024年 1月 2日 75.5k 0

一、案例描述

最近遇到这么一个案例(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 |
+----+------+------+

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. 模拟(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系统性能的,并不改变优先级和兼容性。

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一样,实际上就是优先级矩阵的判定。

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

这个问题实际上和lock table read 一大批表有关,因为加MDL LOCK锁并不是一气呵成的。比如 lock t1 read,t2 read,t3 read,t4 read,其中t4 有一个for update事务,这个时候t1t2t3 的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的。

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论