暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片

MDL锁导致的几个常见的 MySQL 问题分析

数据与人 2021-02-25
4513

如何完整处理一个故障,聊聊我的思路。


技术人人都可以磨炼,但处理问题的思路和角度各有不同,希望这篇文章可以抛砖引玉。



一、常见问题


1、

SQLException: Lock wait timeout exceeded; try restarting transaction,please rollback!  


常见原因:
  • 磁盘空间满,事务无法提交成功。(磁盘满是一个很危险的操作,会引起binlog写坏) 


  • 更新事务未正常提交而产生排他锁,造成其他更新事务一直获取不到该锁而事务超时。


2、查询卡住了,更新卡住,怎么重跑都通不过。

常见原因:

  • Truncate table过程中CTRL +C 终止了。 有分片上存在truncate 事务一直存在,进而对该表的所有操作均会超时。 


  • 查询事务没有正常提交而占据共享锁时,同样会造成alter table获取不到MDL锁,而造成一直等待。 提示为:Waiting fortable metadata lock (show processlist中可查)。 



二、什么是MDL锁

MDL全称为metadata lock,即元数据锁。


MDL锁主要作用是维护表元数据的数据一致性,在表上有活动事务(显式或隐式)的时候,不可以对元数据进行写入操作。因此从MySQL5.5版本开始引入了MDL锁,来保护表的元数据信息,用于解决或者保证DDL操作与DML操作之间的一致性。


对于引入MDL,其主要解决了2个问题:


  • 一个是事务隔离问题,比如在可重复隔离级别下,会话A在2次查询期间,会话B对表结构做了修改,两次查询结果就会不一致,无法满足可重复读的要求;

  • 另外一个是数据复制的问题,比如会话A执行了多条更新语句期间,另外一个会话B做了表结构变更并且先提交,就会导致slave在重做时,先重做alter,再重做update时就会出现复制错误的现象。


元数据锁是server层的锁,表级锁,每执行一条DML、DDL语句时都会申请MDL锁,DML操作需要MDL读锁,DDL操作需要MDL写锁(MDL加锁过程是系统自动控制,无法直接干预,读读共享,读写互斥,写写互斥),申请MDL锁的操作会形成一个队列,队列中写锁获取优先级高于读锁。


一旦出现写锁等待,不但当前操作会被阻塞,同时还会阻塞后续该表的所有操作。事务一旦申请到MDL锁后,直到事务执行完才会将锁释放。(这里有种特殊情况如果事务中包含DDL操作,mysql会在DDL操作语句执行前,隐式提交commit,以保证该DDL语句操作作为一个单独的事务存在,同时也保证元数据排他锁的释放)。



三、MDL锁常见的场景及锁模式

在对表进行上述操作时,如果表上有活动事务(未提交或回滚),请求写入的会话会等待在Metadata lock wait 。

例如下面的这种情形:

常见的MDL锁模式:



四、如何优化与避免MDL锁


MDL锁一旦发生会对业务造成极大影响,因为后续所有对该表的访问都会被阻塞,造成连接积压。我们日常要尽量避免MDL锁的发生。


下面给出几点优化建议可供参考:


  • 开启metadata_locks表记录MDL锁。

  • 设置参数lock_wait_timeout为较小值,使被阻塞端主动停止。

  • 规范使用事务,及时提交事务,避免使用大事务。

  • 增强监控告警,及时发现MDL锁。

  • DDL操作及备份操作放在业务低峰期执行。

  • 少用工具开启事务进行查询,图形化工具要及时关闭。


很多时候发生数据库报错时,不一定就是数据库的问题,我们要形成这样一种意识:不要看到某个模块的问题就着急忙慌的找相关模块的负责人,我们理应具备一定的问题排查解决能力,不要只做问题的搬运工。




觉得本文有用,请转发、点赞或点击“在看”
聚焦技术与人文,分享干货,共同成长
更多内容请关注“数据与人

文章转载自数据与人,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论