Metadata Lock引起的事务阻塞

哥谭三好市民

已于 2024-04-22 16:37:20 修改

阅读量1.3k

点赞数 46

文章标签：数据库 mysql

于 2024-04-18 12:45:47 首次发布

本文链接：https://blog.youkuaiyun.com/wakarimasu/article/details/137916319

版权

文章讲述了在项目升级中，由于未提交的SQL查询导致DDL语句阻塞的问题。作者介绍了MetadataLock的作用，以及如何通过MySQL性能分析工具定位和解决此类问题，包括使用`SHOWPROCESSLIST`、`performance_schema.metadata_locks`和`innodb_trx`等表进行查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一次项目升级，需要在某张表上新增一个字段，但是这句普通的DDL却迟迟没有执行完成，排查后发现是被一句未提交的select语句给阻塞了。

在解决问题后，记录下排查过程，希望能对遇到相同问题的人提供一点帮助。

要解决上述问题，首先得了解下Metadata Lock

Metadata Lock

官方文档：https://dev.mysql.com/doc/refman/8.0/en/metadata-locking.html

想象一个场景：一个事务正在执行查询，此时另一个事务修改了表结构，那么这个事务的查询结果该怎么显示？

为了避免这种情况，MySQL引入了Metadata Lock（MDL锁），用于保护数据库的元数据（metadata），即数据库对象（如表、视图、触发器等）的结构信息，而不是数据本身。这意味着，在事务结束之前，一个会话中的事务正在使用的表不能被其他会话在 DDL 语句中使用。

MDL锁是自动获取并释放的，无需用户显示管理，但有些情况需要特殊注意——SQL执行失败，不会释放MDL锁，而是要等到事务结束

在大致了解MDL后，如果遇到此类问题，要怎样才能快速地定位到问题的原因呢？MySQL提供了很多性能分析工具，现在按照官网的说明来实操一遍。

首先创建一张表

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL
  PRIMARY KEY (`id`)
)

Client1：

begin;
select * from student where id = 3;
# 不提交

Client2：

alter TABLE student ADD COLUMN age INT

此时DDL语句阻塞
在这里插入图片描述

show processlist

在这里插入图片描述
可以看到，此时正在等待metadata lock。但要想知道MDL锁是被哪条SQL获取了，还要继续分析

通常情况，事务都会有超时机制，MDL锁的超时时间默认为1年。详见lock_wait_timeout

The metadata_locks Table

官方文档：https://dev.mysql.com/doc/refman/8.0/en/performance-schema-metadata-locks-table.html

在8.0版本中，默认开启了此功能

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

SELECT * from performance_schema.metadata_locks WHERE object_name = 'student'

在这里插入图片描述

可以看到，owner_thread_id为55的线程已经获取到了MDL读锁，而owner_thread_id为67的线程正在等待MDL写锁。

那么thread_id=55的线程做了哪些事呢？

events_statements_history表包含每个线程已执行完成的最近 N 条语句（如果尚未执行完成、或者执行过程中被打断，则显示 NULL）

官方文档：https://dev.mysql.com/doc/refman/8.0/en/performance-schema-events-statements-history-table.html

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history WHERE THREAD_ID = 55 ORDER BY EVENT_ID;

在这里插入图片描述
到这已经水落石出了，该线程手动开启了事务并且没有提交。

拓展

官方文档：https://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-examples.html

innodb_trx

innodb_trx表提供了事务的详细信息，包括事务是否在等待锁、事务开始时间、正在执行的SQL语句等。当发生事务阻塞时，可以快速定位到问题原因：

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_engine_transaction_id;
  
 SELECT
  waiting_trx_id,
  waiting_pid,
  waiting_query,
  blocking_trx_id,
  blocking_pid,
  blocking_query
FROM sys.innodb_lock_waits;