MariaDB 11.0.2下InnoDB事务无法结束问题求助

我正在使用MariaDB 11.0.2版本，遇到无法对数据库相关行执行任何操作的问题，事务始终无法结束。尝试在谷歌查找解决方案，但要么方案过时，要么无有效结果，恳请协助解决。

以下是InnoDB监控输出：

=====================================
2023-07-16 10:24:13 0x7ff7f81a9640 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 47 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 0 srv_active, 6794 srv_idle
srv_master_thread log flush and writes: 6794
----------
SEMAPHORES
----------
------------
TRANSACTIONS
------------
Trx id counter 3054
Purge done for trx's n:o < 3054 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 2668, ACTIVE (PREPARED) 6797 sec recovered trx
2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
--------
FILE I/O
--------
Pending flushes (fsync): 0
3302 OS file reads, 29 OS file writes, 30 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
---
LOG
---
Log sequence number 67754925
Log flushed up to   67754925
Pages flushed up to 67701222
Last checkpoint at  67701206
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 167772160
Dictionary memory allocated 1001072
Buffer pool size   8064
Free buffers       4706
Database pages     3358
Old database pages 1259
Modified db pages  69
Percent of dirty pages(LRU & free pages): 0.856
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3218, created 140, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3358, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 read views open inside InnoDB
state: sleeping
----------------------------
END OF INNODB MONITOR OUTPUT
============================

问题分析 #

从监控输出可明确：ID为2668的事务处于**ACTIVE (PREPARED)**状态，已持续6797秒，且持有1行锁。这种状态是两阶段提交的中间状态，通常因数据库异常重启、应用崩溃或分布式事务未完成收尾导致，是阻塞行操作的直接原因。

解决步骤 #

1. 排查事务详情 #

执行以下SQL获取事务关联的表、锁信息及等待情况：

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_id = 2668;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS WHERE trx_id = 2668;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS WHERE blocking_trx_id = 2668;

2. 手动提交/回滚事务 #

尝试直接收尾该PREPARED事务：

-- 先尝试提交
COMMIT TRANSACTION 2668;

-- 提交失败则尝试回滚
ROLLBACK TRANSACTION 2668;

3. 强制终止关联线程 #

若手动收尾无效，找到事务对应的数据库线程并杀死：

-- 查询线程ID
SELECT trx_mysql_thread_id FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_id = 2668;

-- 替换[线程ID]后执行
KILL [线程ID];

4. 重启数据库（终极方案） #

如果以上操作均无效，可重启MariaDB服务。重启前务必做好数据备份，避免意外数据丢失。

预防建议 #

• 检查应用事务逻辑，确保异常场景下事务能正常提交或回滚，避免遗留PREPARED状态事务。
• 若使用分布式事务，确认事务协调器能处理异常，完成两阶段提交的收尾流程。
• 定期监控数据库中的长时间运行事务和PREPARED状态事务，及时处理异常。

内容的提问来源于stack exchange，提问作者Avora