MySQL MDL问题排查与理论

show processlist
select * from information_schema.innodb_trx\G;
select * from sys.innodb_lock_waits\G;
select * from performance_schema.metadata_locks;
select * from sys.schema_table_lock_waits\G;
select * from performance_schema.table_handles where OWNER_THREAD_ID!=0;

call sys.ps_setup_enable_instrument('sql/mdl');

　　

当mysql运行一条SQL语句时，在你预期的时间内，没有完成时，我们都会登陆到mysql数据库上想查看是不是出了什么问题，通常会使用的一个命令就是 show processlist，看看有哪些session，这些session在做什么事情。就从这个命令开始，显示如下：

 

 图中看到了显示了几处信息：

 id：为session_id，也就是processlist_id

 user：该session使用什么用户登陆的mysql数据库

 host：客户端登陆的ip地址（这里我都是本地登陆的）

 db：连接了哪个数据库（这里我只是连接上了数据库，并没有其他操作，所以都是NULL）

 command：当前session执行命令的类型

 Time：处于当前命令类型持续的时间

 State：当前命令类型的状态

 Info：具体命令信息

了解上面内容的含义后，初始化一下测试环境，模拟MDL锁等待，各个session按顺序执行命令如下：

id 41 id 42 id 43 id 44 id 45
  begin； begin； alter table testok add z varchar(10) not Null; select * from testok;
  select * from testok limit 1; select * from testok limit 1;    
 

 

 

 

说明：测试环境有一个test库，里面有一张表testok(innodb)，里面有几行数据。在id 41没有执行任何命令，该session用于查看结果。id 42 开启一个事务，接着执行了一条查询语句，紧接着id 43 开启一个事务，也执行了一条查询语句。id 44为该表添加一个字段，id 45查询testok表。

这时在id 41 执行show processlist，结果如下：

 

 

可以看到与之前的一些变化，其中id 44 45的state变成了 Waiting for table metadata lock，即等待元数据锁，后面的Info即为上面执行命令。

这里简单解释一下产生元数据锁的原因，元数据锁是server层的锁，表级锁，主要用于隔离DML和DDL操作之间的干扰。每执行一条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就在本次事务之外，其执行完成后不会持有读锁）

下表为总结的表级元数据锁信息：

1、SHARED_UPGRADABLE本身为读锁但有些特殊，所以列表里把它的优先级设置为0.

      一、并不受队列中的写锁等待而阻塞，只和当前持锁的session比对，当前持锁session为排他锁X，则等待，反之获得锁

      二、为了保证一张表同时只有一个DDL操作进行，SHARED_UPGRADABLE之间是互斥的，即一个时刻只有一个SHARED_UPGRADABLE是GRANTED状态，其余是被阻塞。

2、EXCLUSIVE、SHARED_NO_READ_WRITE级别相同，在队列中排队，先进先出。

3、SHARED_WRITE与SHARED_READ兼容，但SHARED_WRITE优先级高于SHARED_READ_ONLY且不兼容

4、SHARED_READ与SHARED_WRITE和SHARED_READ_ONLY分别兼容。即如果前面的持锁类型为SHARED_WRITE，则可以获得锁。如果前面持锁类型为SHARED_READ_ONLY，也可以获得锁。

5、SHARED_READ_ONLY优先级最低，主要是因为被SHARED_WRITE互斥，但如果只有SHARED_READ则他们的优先级是兼容的。

如果看元数据锁名字比较懵，可以查看mysql意向锁的兼容互斥表。

名称 类型 优先级 说明
SHARED_UPGRADABLE 共享升级锁 0 一般在执行DDL时在on-line情况下会产生该锁
EXCLUSIVE 排他锁X 1 一般在执行DDL时会产生该锁
SHARED_NO_READ_WRITE 排他锁X 1 执行lock tables xxx write产生该锁
SHARED_WRITE 意向排他锁IX 2 一般执行DML的更新语句 或 select ... for update产生该锁
SHARED_READ 意向共享锁IS 2.5 select ... lock in share mode产生该锁(8.0版本以后使用select...for share)
SHARED_READ_ONLY 共享锁S 3 执行lock tables xxx read产生该锁
所以在对表做DDL操作时，需要注意元数据锁的情况，避免事务长期持有元数据锁或在长事务执行时进行DDL操作，这样很容易阻塞该表的后续操作，而如果客户端有重试机制时，随着重试次数增多可能会打满数据库的连接，从而影响整个数据库。当然在目前版本中已经有了online DDL的支持，优化DDL操作时进行锁降级成读锁，在DDL过程中减小影响，但online DDL第一步仍然是需要获得元数据写锁，如果在第一步就卡住，结果和本次模拟操作是一样的，会影响后续操作。

所以上面语句执行完成后，id 42开启了事务执行了查询，此时先申请到了MDL读锁（也就是意向共享锁IS），并持有该锁，因为并没有提交。id 43开启了事务执行了查询，此时也可以申请到MDL读锁，所以他的查询语句是可以正常执行的。id 44对表结构进行了修改，需要申请MDL写锁，此时与id 42和 id 43互斥，无法得到写锁，所以他会被卡住，进入锁等待。而id 45只是查询该表，申请MDL读锁即可，与id 42和 id 43并不冲突，但是排在他前面id 44是写锁等待，而它只能排在id 44后面得到锁，所以被互斥，进入锁等待。

但在实际环境中，我们从上图能获得的信息是id 44、id 45进入了锁等待，但是并不知道是哪个session持有这个元数据锁。这时我们可能需要performance_schema库下的四张表metadata_locks、threads、events_statements_current及events_statements_history。

events_statements_current记录了所有在线session执行的最后一条语句

events_statements_history记录了所有在线session执行语句的历史记录（默认每个session记录10条数据，由全局参数performance_schema_events_statements_history_size决定，如果session下线则相关记录会自动被删除）

threads表用来关联processlist_id及thread_id

metadata_locks表记录了元数据锁的信息

在开始之前需要开启metadata_locks的监控,执行如下语句：

mysql > UPDATE performance_schema.setup_instruments set enabled='YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';

现在开始查找具体是哪个session持有该锁，打开监控后首先要查找的表是 metadata_locks，结果如下：

 

观察输出：

第1行：表示thread_id 68 持有testok的元数据读锁（lock_status为granted）

第2行：表示thread_id 69 持有testok的元数据读锁（lock_status为granted）

第3-6行：表示thread_id 70分别加了全局意向排他锁，test数据库的意向排他锁，表空间testok的意向排他锁，这是由于我们在执行alter table命令时需要额外加的锁，元数据锁也是从树状态结构一级一级加下来的，全局>数据库>表空间>表本身，保证每一个层级的操作权限。为什么DDL操作需要加这些锁，试想以下情况，执行flush tables with read lock去做一些备份的事情，如果此时执行alter table而不判断全局层的锁信息，会直接在表上尝试去加元数据排他锁（写），然而发现上面已经有了共享锁（读），则直接进入了锁等待，根据上面说的情况，会阻塞后面的查询请求。而首先尝试在全局层加意向排他锁时，发现无法获得，则在全局层就报错了，回退。而不影响表的操作。其中第5行在testok表上加了shared_upgradable共享升级锁，这个和mysql的online DDL特性有关（如想了解可以自行查找相关信息，后续也会写一下）。

第7行：表示thread_id 70在testok表上申请了元数据排他锁，但是pending了，就是上面看到的锁等待。

第8行：表示thread_id 71在testok表上申请了元数据共享锁，但是也pending了。

第9行：是查询metadata_locks表产生的元数据共享锁，忽略。

经过以上的查找，我们了解了目前thread_id 68 69持有testok的元数据读锁，而thread_id 70 71在等待这个读锁，这时已经找到了“带着面具”的元凶了，接下来需要把面具撕下来，看看它到底是谁。

查找threads表，以其中一条数据为例：

 

通过该表我们可以将thread_id与processlist_id联系起来，也可以与thread_os_id联系起来（在操作系统中执行top -H -p [mysql_pid]）

至此就找到了thread 68 69 对应的processlist id 42 43就是持有该锁的session.

同时我们可以通过events_statements_current、events_statements_history两张表查看这两个session执行哪些语句导致了锁没有释放。

首先是events_statements_current，可以看出thread 68 69最后执行的语句正是上面列表中的select语句，但这并不是不释放锁的原因，前面已经说了事务完成后会将锁释放掉

 

所以还需要查看events_statements_history，观察下表，以thread_id 69为例，按照event_id排序，发现select的上一个事件是begin开启了事务，但是并没有commit，至此就回溯到了根本原因。

 

接下来就是如何处理：

1.如果session的客户端还健在的话，可以直接执行commit

2.如果session的客户端已经挂了，执行kill processlist_id

3.调整lock_wait_timeout锁超时等待时间，让超时的事务自动回滚。（该值默认值是一年....） 
转自：https://blog.csdn.net/finalkof1983/article/details/88063328