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