MySQL内幕：XA长事务是如何垄断IX锁的？

有一天，你遇到下面这样一种情况：

在修改表结构的时候，你发现会话一直卡在那里，没有动静。

查看processlist发现该线程一直在等待medata lock的释放。

这时你想到去data_locks排查该表上的加锁情况，发现果然在表上加了IX锁。

你开始根据thread_id找出究竟是什么操作造成了长时间的IX锁。

此时，你可能会遇到多种情况。

• thread_id压根没找到
• 另一种情况是 thread/sql/replica_worker线程。

select
    dl.THREAD_ID,
    row_number() over (partition by dl.THREAD_ID
order by
    dl.THREAD_ID) `row_number`,
    dl.`ENGINE`,
    dl.ENGINE_LOCK_ID,
    dl.OBJECT_SCHEMA,
    dl.OBJECT_NAME,
    dl.INDEX_NAME,
    dl.LOCK_TYPE,
    dl.LOCK_MODE,
    dl.LOCK_STATUS,
    dl.LOCK_DATA,
    t.name,
    t.type,
    t.processlist_user,
    t.processlist_host,
    t.processlist_db,
    t.processlist_command,
    t.processlist_time,
    t.processlist_state,
    t.thread_os_id
from
    performance_schema.threads t,
    performance_schema.data_locks dl
where
    t.thread_id = dl.THREAD_ID and thread_id=62\G

在这个时候，你觉得应该去瞅瞅InnoDB引擎的状态了。

果不其然，你发现有个active的事务一直处于prepared阶段，其对应的transaction 44588711恰恰是刚才那个事务的trx_id。

与此同时，你发现集群中的其他Secondary或者Read Replica节点也存在同样的情况；或许你已经猜到原因所在 -- 应该是个没提交的XA事务。

随即，你立即执行了xa recover，此时你终于找到了问题的根源。

当然，你可以根据具体情况选择回滚还是提交该事务。

当你执行命令后，该事务已经完成，对应表上的IX锁也得以释放，你可以继续进行你的表结构修改工作了。

但是有一天，你遇到了同样的情况，你便开始执行xa commit或者 xa rollback来结束该事务，但是期待的结果并没有出现，系统反而报错，提示Unknow XID，如下所示。

这该怎么办呢？你挠了挠头。

细心的你发现bqual length 不是0，此时，你可以根据 gtrid 和 bqual的长度分别计算出对应gtrid和bqual，然后将其转换成十六进制，最后再补充上formatID，然后一起提交或者回滚。

xa commit gtrid,bqual,formatID;

问题果然被解决了，但是你开始好奇这种情况到底是怎么发生的呢？

其实，一般情况下是由于XA事务进入prepare阶段后mysqld服务由于某一原因突然宕掉，尽管后续你重启了数据库服务，但是该事务始终没有提交或者回滚，这才导致该事务对应的表一直被加了元数据锁。

接下来，我们模拟下这种场景。

首先，我们启动一个XA事务，并进入Prepare阶段。

在这时，我们突然kill掉MySQL服务进程。

sudo kill -9 $(pidof mysqld)

然后，等MySQL服务正常重新启动后，上面所说的情况便出现了。

总结一下，对于MySQL中XA长事务导致的表级IX锁占用问题，我们需要：

1. 从processlist、data_locks表、innodb_trx等多个角度综合分析,准确判断是否为XA事务所致。

2. 利用xa recover语句找出准备阶段的XA事务,根据需要xa commit或xa rollback来解锁。

3. 如果遇到Unknown XID错误,需要还原出完整的gtrid、bqual和formatID后再操作。

4. 预防此类问题的根本在于避免XA事务在准备阶段后遗留,可通过设置适当的xa_commit_timeout参数或定期检查并处理,保证事务要么提交要么回滚。

5. 建议在开发过程中尽量规避XA分布式事务,除非确有必要,从源头减少此类问题。

通过以上分析和解决步骤，我们可以更深入地理解MySQL内部XA事务的执行机制，并掌握锁分析和解锁的方法,确保系统的稳定高效运行。希望这篇文章能为大家遇到类似情况时提供一些思路和参考。