mysql-MDL锁
针对****生产环境上出现过一次关于MDL锁导致DDL等待锁失败而出现连接表失败，在本地进行MDL锁复现。
初始化环境，有一个test01.t1表，进行模拟MDL锁等待
|
id 14(A) |
id 15(B) |
id 16(C) |
id 17(D) |
id 18(E) |
|
|
begin； |
begin； |
alter table test01.t1 add zz varchar(10) ; |
select * from test01.t1; |
|
|
select * from test01.t1; |
select * fromtest01.t1; |
|
|
1) 开启session A;
2) 开启session B;执行begin;select * from test01.t1;
3) 开启session C;执行begin;select * from test01.t1;
4) 开启session D;执行alter table test01.t1 add zz varchar(10);
5) 开启session E;执行select * from test01.t1;
6) 在session A中执行show processlist;如下图：
图中看到了显示了几处信息：
id：为session_id，也就是processlist_id
user：该session使用什么用户登陆的mysql数据库
host：客户端登陆的ip地址（这里我都是本地登陆的）
db：连接了哪个数据库（这里我只是连接上了数据库，并没有其他操作，所以都是NULL）
command：当前session执行命令的类型
Time：处于当前命令类型持续的时间
State：当前命令类型的状态
Info：具体命令信息
注意：从上图可以看出id为17/18 state是Waiting for table metadata lock，即等待元数据锁
产生元数据锁的原因，元数据锁是server层的锁，表级锁，主要用于隔离DML和DDL操作之间的干扰。每执行一条DML、DDL语句时都会申请MDL锁，DML操作需要MDL读锁，DDL操作需要MDL写锁（MDL加锁过程是系统自动控制，无法直接干预，读读共享，读写互斥，写写互斥），申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。事务一旦申请到MDL锁后，直到事务执行完才会将锁释放。（这里有种特殊情况如果事务中包含DDL操作，mysql会在DDL操作语句执行后，隐式提交commit，以保证该DDL语句操作作为一个单独的事务存在，同时也保证元数据排他锁的释放，例如id 17的语句改为<begin;alter table testok add z varchar(10) not Null;select * from testok;>，此时一旦alter语句执行完成会马上提交事务（autocommit=1），后面的select就在本次事务之外，其执行完成后不会持有读锁）
7) 开启MDL锁监控；
mysql> UPDATE performance_schema.setup_instruments set enabled='YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
8) 通过select * from performance_schema.metadata_locks;查看具体是哪个session持有该锁；
元数据锁类型：
|
名称 |
类型 |
优先级 |
说明 |
|
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产生该锁 |
从上图可以看出：
第1-2行thread_id 40/41持有t1表的元数据读锁(lock_status为granted)
第3-5行，thread_id 42分别加了全局意向排他锁，test01数据库的意向排他锁，这是由于我们在执行alter table命令时需要额外加的锁，元数据锁也是从树状态结构一级一级加下来的，全局>数据库>表空间>表本身，保证每一个层级的操作权限。为什么DDL操作需要加这些锁，试想以下情况，执行flush tables with read lock去做一些备份的事情，如果此时执行alter table而不判断全局层的锁信息，会直接在表上尝试去加元数据排他锁（写），然而发现上面已经有了共享锁（读），则直接进入了锁等待，根据上面说的情况，会阻塞后面的查询请求。而首先尝试在全局层加意向排他锁时，发现无法获得，则在全局层就报错了，回退。而不影响表的操作。其中第5行在t1表上加了shared_upgradable共享升级锁，这个和mysql的online DDL特性有关；
第6行，thread_id 42在t1表上申请了元数据排它锁，但是是pending，就是上面看到的锁等待；
第7行，thread_id 43在t1表上申请了元数据排它锁，但是是pending；
第8行，是查询metadata_locks表产生的元数据共享锁，忽略
结论：从上面可以分析出来，thread_id=40,41的持有t1表的元数据锁(MDL锁)，而thread_id 42 43在等待这个锁；
9) 通过查看performance_schema.threads表，查看thread_id 40 41 对应的进程:
从上可以的找到thread_id 40 41 对应的processlist id 15 16 就是持有该锁的session;并且可以通过performance_schema.events_statements_current,performance_schema.events_statements_history两张表查看具体是执行哪些sql语句导致锁没有释放；
接下来就是如何处理：
1.如果session的客户端还健在的话，可以直接执行commit
2.如果session的客户端已经挂了，执行kill processlist_id；
注意：如果在生产环境上，通过show full processlist；发现有在等待MDL锁释放，但是一直却没有释放，则可以通过下面命令快速查找出，等待获得MDL锁以及持有MDL锁的processlist id
mysql> SELECT
-> a.OBJECT_SCHEMA AS locked_schema,
-> a.OBJECT_NAME AS locked_table,
-> "Metadata Lock" AS locked_type,
-> c.PROCESSLIST_ID AS waiting_processlist_id,
-> c.PROCESSLIST_TIME AS waiting_age,
-> c.PROCESSLIST_INFO AS waiting_query,
-> c.PROCESSLIST_STATE AS waiting_state,
-> d.PROCESSLIST_ID AS blocking_processlist_id,
-> d.PROCESSLIST_TIME AS blocking_age,
-> d.PROCESSLIST_INFO AS blocking_query,
-> concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
-> FROM
-> performance_schema.metadata_locks a
-> JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA=b.OBJECT_SCHEMA AND a.OBJECT_NAME=b.OBJECT_NAME AND a.lock_status='PENDING' AND b.lock_status='GRANTED' AND a.OWNER_THREAD_ID!=b.OWNER_THREAD_ID AND a.lock_type='EXCLUSIVE'
-> JOIN performance_schema.threads c ON a.OWNER_THREAD_ID=c.THREAD_ID
-> JOIN performance_schema.threads d ON b.OWNER_THREAD_ID=d.THREAD_ID;
元数据锁的监控脚本checkMDL.sh内容：
#!/bin/bash
tempFlie=/mysql/software
mysqluser='root'
mysqlpasswd='123456'
Socket='/mysql/data3306/my3306.sock'
mysql --user=${mysqluser} --password=${mysqlpasswd} --socket=${Socket} -e "SELECT a.OBJECT_SCHEMA AS locked_schema, a.OBJECT_NAME AS locked_table, 'Metadata Lock' AS locked_type, c.PROCESSLIST_ID AS waiting_processlist_id, c.PROCESSLIST_TIME AS waiting_age, c.PROCESSLIST_INFO AS waiting_query, c.PROCESSLIST_STATE AS waiting_state, d.PROCESSLIST_ID AS blocking_processlist_id, d.PROCESSLIST_TIME AS blocking_age, d.PROCESSLIST_INFO AS blocking_query, concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection FROM performance_schema.metadata_locks a
JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA=b.OBJECT_SCHEMA AND a.OBJECT_NAME=b.OBJECT_NAME AND a.lock_status='PENDING' AND b.lock_status='GRANTED' AND a.OWNER_THREAD_ID!=b.OWNER_THREAD_ID AND a.lock_type='EXCLUSIVE' JOIN performance_schema.threads c ON a.OWNER_THREAD_ID=c.THREAD_ID JOIN performance_schema.threads d ON b.OWNER_THREAD_ID=d.THREAD_ID;" |grep -v locked_schema > ${tempFlie}/temp.txt
mdl_num=`cat ${tempFlie}/temp.txt | wc -l`
if [ $mdl_num -gt 0 ];then
cat ${tempFlie}/temp.txt | awk '{print $NF}' > ${tempFlie}/mdl_processId.txt
mdl_processId=`cat ${tempFlie}/temp.txt | awk '{print $NF}' `
echo -e "存在MDL锁，持锁进程id为\n${mdl_processId}"
else
echo "不存在MDL锁"
fi
