MySQL：MDL LOCK的“穿越行为”

一、穿越行为案例描述

最近遇到这么一个案例（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.2.3.4.5.6.7.8.9.10.11.12.13.14.15. 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 | +----+-----------------+-----------+------+---------+------+---------------------------------+--------------------+-----------+---------------+1.2.3.4.5.6.7.8.9.

这里可以看到这里只有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 | +----+-----------------+-----------+------+---------+------+---------------------------------+-------------------------------+-----------+---------------1.2.3.4.5.6.7.8.

这个时候因为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)) 1.2.3.4.5.

首先想看优先级矩阵，然后再看兼容矩阵，其中优先级矩阵为：

复制 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 | + + + + + + + + + + |1.2.3.4.5.6.7.8.9.10.11.12.13.

兼容矩阵为：

复制 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 | - - - - - - - - - - |1.2.3.4.5.6.7.8.9.10.11.12.13.

当然期间有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 | + + + + + + + - - - |1.2.3.4.

而比对兼容矩阵的时候同样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 | + + + + + + - - - - |1.2.3.4.

这就看起来像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 | + + + + + + - - - - |1.2.3.4.

可以看到并不兼容，因此堵塞

3. 问题3

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

这也是实际案例中的遇到的问题。

四、总结

本案例中我们得到几个结论：

语句是否能够执行主要看的优先级矩阵和兼容矩阵，前者用于判断本次执行的语句和堵塞中的MDL LOCK谁的优先级更高，优先级更高则可以继续判断兼容矩阵。后者用于判定本次执行的语句和获取MDL LOCK的语句（或者事务）是否兼容。lock table read 一大批表的时候，可能某些表加锁成功了，而某些表加锁堵塞了，看起来是整个lock table read语句堵塞了。mysqldump导出如果全是innodb表肯定是要--single-transaction的。