©2020-2024 ioDraw All rights reserved,
Privacy Policy
基础材料：
centos7.5 mysql 5.7.24
当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 41id 42id 43id 44id 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排他锁X1一般在执行DDL时会产生该锁
SHARED_NO_READ_WRITE排他锁X1执行lock tables xxx write产生该锁
SHARED_WRITE意向排他锁IX2一般执行DML的更新语句 或 select ... for update产生该锁
SHARED_READ意向共享锁IS2.5select ... lock in share mode产生该锁(8.0版本以后使用select...for
share)
SHARED_READ_ONLY共享锁S3执行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锁超时等待时间，让超时的事务自动回滚。（该值默认值是一年....）
最后还要补充一个相关问题，id 44和id 45在本次实验中肉眼观察是一起完成的，实际上他们即不是一起完成的，也不是id 44的写操作先完成，在去执行id
45的查询操作。而是id 45的查询操作先完成，id 44的DDL操作后完成。
可以实验一下，如果把id45执行的语句变成与id 42 43相同会出现什么情况？
这个问题与online DDL有关 ，将在《mysql锁系列之MDL元数据锁之三》中进行说明