有一天，你遇到下面这样一种情况：
在修改表结构的时候，你发现会话一直卡在那里，没有动静。
查看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=62G
在这个时候，你觉得应该去瞅瞅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锁占用问题，我们需要：
-
从processlist、data_locks表、innodb_trx等多个角度综合分析,准确判断是否为XA事务所致。
-
利用xa recover语句找出准备阶段的XA事务,根据需要xa commit或xa rollback来解锁。
-
如果遇到Unknown XID错误,需要还原出完整的gtrid、bqual和formatID后再操作。
-
预防此类问题的根本在于避免XA事务在准备阶段后遗留,可通过设置适当的xa_commit_timeout参数或定期检查并处理,保证事务要么提交要么回滚。
-
建议在开发过程中尽量规避XA分布式事务,除非确有必要,从源头减少此类问题。
通过以上分析和解决步骤，我们可以更深入地理解MySQL内部XA事务的执行机制，并掌握锁分析和解锁的方法,确保系统的稳定高效运行。希望这篇文章能为大家遇到类似情况时提供一些思路和参考。
原文始发于微信公众号（小新数据库）：MySQL内幕：XA长事务是如何垄断IX锁的？
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/234867.html
