dbeaver无法修改表数据_解决MDL锁导致无法操作数据库表的问题

背景信息

MYSQL的MDL锁，用于解决或者保证DDL操作与DML操作之间的一致性，但是在部分场景下会出现阻塞，例如执行DML操作时执行ALTER操作、存在长时间查询时执行ALTER操作等等。

表象如下：

出现 Waiting for table metadata lock 且长时间处于等待状态，并阻塞所有后续对表的操作

mysql

MDL锁出现场景

• 创建、删除索引。
• 修改表结构。
• 表维护操作（optimize table、repair table 等）。
• 删除表。
• 获取表级写锁 。

被锁原因

• 当前有对表的长时间查询。
• 显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚。
• 表上有失败的查询事务。

解决办法

• 找到魏师傅 MDL 锁的session，kill掉
• 或 kill 掉DDL语句，等待下一次运维时间变更

select 

使用有权限的用户执行`kill`即可终端问题会话，解除DML锁。

维护建议

• 在业务低峰期执行相关场景操作，例如创建索引、删除索引等。
• 开启事务自动提交autocommit。
• 设置参数lock_wait_timeout为较小值。
• 考虑使用事件来终止长时间运行的事务，比如下面的例子中会终止执行时间超过60分钟的事务。

create 

示例

场景1-有对表的长时间查询

data_million_large 当前有 800万+的数据,对表进行一次全表查询肯定是条慢SQL

mysql

场景2-事务未提交或回滚

mysql

场景3-有失败的查询事务

mysql

这个示例最难排查，咋一看没有任何阻塞的事务存在，持有MDL锁的session已处于 Sleep 状态了

查看session最后一次执行的语句

可查看session执行的最后一次语句的记录，包括错误的信息

# 

参考文档： https:// help.aliyun.com/knowled ge_detail/94566.html#concept-csn-5tt-4fb
《MySQL DBA甩锅技巧集锦-张沈波》