基础材料:

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元数据锁之三》中进行说明

技术
下载桌面版
GitHub
百度网盘(提取码:draw)
Gitee
云服务器优惠
阿里云优惠券
腾讯云优惠券
华为云优惠券
站点信息
问题反馈
邮箱:ixiaoyang8@qq.com
QQ群:766591547
关注微信