MySQL锁等待超时问题的排查与解决

Leighteen

已于 2025-04-30 11:32:30 修改

阅读量1.3k

点赞数 33

分类专栏： mysql 文章标签： mysql 数据库

于 2025-03-27 16:58:51 首次发布

本文链接：https://blog.csdn.net/Treeers/article/details/146565549

版权

mysql 专栏收录该内容

4 篇文章

订阅专栏

MySQL锁等待超时问题的排查与解决

问题背景

最近在开发过程中，我们的应用程序突然开始报错：1205 - Lock wait timeout exceeded; try restarting transaction。这个错误导致用户无法执行某些数据库操作，影响了系统的正常使用。

作为开发人员，我需要快速定位并解决这个问题。本文将分享我是如何排查和解决这个MySQL锁等待超时问题的过程。

问题现象

当用户尝试执行某个数据库操作（例如删除记录）时，系统报错：

1205 - Lock wait timeout exceeded; try restarting transaction

在这里插入图片描述

这个错误表明当前事务在等待获取锁时超过了MySQL配置的超时时间（默认为50秒）。可是我目前只是在本地环境进行接口测试，所有的测试都是单线程操作的，怎么会导致死锁呢？

后续经过我尝试重启项目，以及恢复我改动的接口重新测试，发现还是有这类问题的发生，于是我想到了什么，这或许不是我代码导致的问题，而是数据库层面出现了问题。接下来我开始着重排查数据库存在的问题。

问题排查

步骤1：查看数据库事务状态

首先，我需要了解当前数据库中的事务状态，特别是长时间运行的事务：

SELECT * FROM information_schema.innodb_trx;

执行结果：

"trx_id" "trx_state" "trx_started" "trx_requested_lock_id" "trx_wait_started" "trx_weight" "trx_mysql_thread_id" "trx_query" "trx_operation_state" "trx_tables_in_use" "trx_tables_locked" "trx_lock_structs" "trx_lock_memory_bytes" "trx_rows_locked" "trx_rows_modified" "trx_concurrency_tickets" "trx_isolation_level" "trx_unique_checks" "trx_foreign_key_checks" "trx_last_foreign_key_error" "trx_adaptive_hash_latched" "trx_adaptive_hash_timeout" "trx_is_read_only" "trx_autocommit_non_locking"
"120706744" "RUNNING" "27/3/2025 15:28:51" "17" "202705" "0" "4" "8" "1136" "5" "9" "0" "REPEATABLE READ" "1" "1" "0" "0" "0" "0"
"421259478658352" "RUNNING" "27/3/2025 16:06:41" "0" "202825" "0" "0" "0" "1136" "0" "0" "0" "REPEATABLE READ" "1" "1" "0" "0" "0" "0"

在这里插入图片描述

从结果中，我发现了一个长时间运行的事务（ID为120706744），它已经持有了一些锁（5行被锁定，9行被修改）。这引起了我的注意，因为这个事务的开始时间明显早于我开始测试的时间。

这时我开始怀疑，是否有一个遗留的事务一直没有提交或回滚？这可能是导致我的测试操作无法获取锁的原因。

步骤2：查看进程列表

接下来，我查看MySQL的进程列表，了解更多关于这些事务的信息：

SHOW FULL PROCESSLIST;

通过进程列表，我可以看到线程ID为202705的进程正在执行一个长时间运行的事务，并且可能持有我们需要的锁。更奇怪的是，这个进程似乎不是由我当前的应用程序创建的，因为我的应用程序刚刚重启过。

步骤3：分析锁等待情况

为了进一步了解锁等待情况，我查询InnoDB的状态：

SHOW ENGINE INNODB STATUS;

在输出结果的TRANSACTIONS部分，我可以看到当前事务的详细信息，包括锁等待和锁持有情况。我注意到有一个事务持有了我需要访问的表的锁，而且这个事务已经运行了很长时间。

步骤4：确定问题根源

通过以上分析，我确定问题的根源是：

有一个长时间运行的事务（ID为120706744，线程ID为202705）一直没有提交或回滚
这个事务持有了某些表的锁，导致其他事务无法获取所需的锁
当新事务尝试获取锁时，等待超过了MySQL配置的超时时间（默认50秒），从而抛出锁等待超时错误

进一步回想，我意识到这个长时间运行的事务很可能是之前测试时运行到一半的事务。当时我可能中断了测试过程，或者系统出现了异常，导致事务没有正确结束，一直占用着锁资源。

这解释了为什么即使是单线程测试，即使重启应用程序，我仍然遇到锁等待超时的问题——因为问题不在应用程序代码，而在数据库中有一个未完成的事务一直持有锁。

解决方案

方案1：终止长时间运行的事务

既然找到了问题的根源，最直接的解决方法是终止那个长时间运行的事务，释放它持有的锁：

-- 终止线程ID为202705的事务
KILL 202705;

执行后，我再次查询事务状态，确认该事务已被终止：

SELECT FROM information_schema.innodb_trx WHERE trx_id = '120706744';

查询结果为空，说明事务已成功终止。我立即测试之前失败的操作，这次可以正常执行了！这证实了我的猜测：问题确实是由一个遗留的、未完成的事务导致的。

方案2：优化应用程序的事务管理

虽然问题已经解决，但为了防止类似问题再次发生，我对应用程序的事务管理进行了优化：

设置合理的事务超时时间：在应用程序中设置合理的事务超时时间，避免事务长时间运行。
分离读写操作：将读操作和写操作分离到不同的事务中，减少事务的执行时间。
异步处理耗时操作：将耗时操作移到事务外异步处理，避免长事务。
使用乐观锁：对于高并发场景，考虑使用乐观锁替代悲观锁，减少锁冲突。
实现事务监控机制：定期监控长时间运行的事务，及时发现和处理问题。

方案3：优化数据库配置

我还对数据库配置进行了优化：

-- 设置锁等待超时时间（默认50秒）
SET GLOBAL innodb_lock_wait_timeout = 30;
-- 设置事务隔离级别为READ COMMITTED（减少锁冲突）
SET GLOBAL transaction_isolation = 'READ-COMMITTED';
-- 启用死锁检测
SET GLOBAL innodb_deadlock_detect = ON;

方案4：实现自动监控和清理机制

为了及时发现和处理长时间运行的事务，我实现了自动监控和清理机制：

@Scheduled(fixedRate = 300000) // 每5分钟执行一次
public void monitorLongRunningTransactions() {
	// 查找运行超过5分钟的事务
	List<Map<String, Object>> longRunningTxs = jdbcTemplate.queryForList(
		"SELECT trx_id, trx_mysql_thread_id, trx_started, " +
		"TIMESTAMPDIFF(SECOND, trx_started, NOW()) as seconds_running " +
		"FROM information_schema.innodb_trx " +
		"WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 300"
	);
	// 记录并可选择性地终止这些事务
	for (Map<String, Object> tx : longRunningTxs) {
		log.warn("发现长时间运行的事务: {}", tx);
		// 可以选择自动终止这些事务
	}
}