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

 

注意：有时候短暂的pending也是正常的。建议做个双从判断，比如处于等待MDL状态10、20S了说明需要警惕了