為何出現了trx_mysql_thread_id為0 的事務檢視正在運作的sql檢視事務殺sql會話SELECT concat('kill ',trx_mysql_thread_id,";")t_sql FROM information_schema.INNODB_TRX；

為何出現了trx_mysql_thread_id為0 的事務

今天巡檢時突然發現有很多鎖等待逾時的情況，原以為是一個簡單的小事，一查，結果令人深思。

1. 問題現象

發現日志中出現了大量的 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 錯誤

1. 排查過程

發現此類情況後，挑了其中一個SQL腳本手動運作了一下，發現同樣報此錯誤

mysql> UPDATE tbname SET column_name = 2 WHERE col_id= '25945fa285904ea59cd92a73a3850ceb' AND aYear = 2018 AND aMonth = 5;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

出現此情況，第一反應是檢視是否有未送出的事務或有其他的SQL運作時也需要對該條記錄進行寫操作。

檢視正在運作的sql

select * from information_schema.processlist where info is not null;

結果集中并無對該表的任何操作，是以，很大可能是有未送出的事務了。

1

2

3

檢視事務

SELECT *FROM information_schema.INNODB_TRX；

　　結果中确實存在大量事務，此時原本以為已經查到問題，直接将對應為送出的事務殺掉即可（已與相關人員确認可以殺）

于是把腳本準備好，準備大開殺戒

殺sql會話

SELECT concat('kill ',trx_mysql_thread_id,";")t_sql FROM information_schema.INNODB_TRX；

　　但是仔細一看，trx_mysql_thread_id全部都是0

經确認，trx_mysql_thread_id=0 的事務全部為XA事務。

1. 處理過程

因為trx_mysql_thread_id=0 的事務無法通過kill trx_mysql_thread_id 的方式處理，是以，需要復原這些XA事務。

檢視XA事務資訊

複制代碼

mysql> xa recover;

| formatID | gtrid_length | bqual_length | data |

| 1096044365 | 20 | 9 | tm156393736565426841tm1333009 |

| 1096044365 | 20 | 9 | tm156393708714926372tm1332251 |

| 1096044365 | 20 | 9 | tm156393726166726646tm1332693 |

...

43 rows in set (0.00 sec)

拼接生成XA事務復原腳本

XA事務復原指令的格式：

xa rollback 'left(data,gtrid_length)'，'substr(data,gtrid_length+1,bqual_length)', formatID;

以上查出來的資訊拼接結果為（以下舉其中一個為例）

xa rollback 'tm156393736565426841'，'tm1333009',1096044365;

執行復原腳本

mysql> xa rollback 'tm156393736565426841','tm1333009', 1096044365;

Query OK, 0 rows affected (0.00 sec)

檢查是否還存在未送出的XA事務

發現已經無正在執行事務

XA資訊

測試能否正常更新記錄

發現也已正常

再檢查各日志，此類鎖等待問題也未出現。

1. XA事務（分布式事務）淺析

在本應用中，為了降低單點壓力，根據業務情況進行了分表分庫，将表分布在不同的庫中（庫分布在不同的機器上）。在這種場景下，事務的送出會變得相對複雜，因為多個節點（庫）的存在，可能存在部分節點送出失敗的情況，即事務的ACID特性需要在各個不同的資料庫執行個體中保證。比如更新db1庫的A表時，必須同步更新db2庫的B表，兩個更新形成一個事務，要麼都成功，要麼都失敗，起初，為了簡化應用程式在事務處理的難度，是以直接使用MySQL資料庫的分布式事務。

兩階段送出

分布式事務通常采用2PC協定，全稱Two Phase Commitment Protocol。該協定主要為了解決在分布式資料庫場景下，所有節點間資料一緻性的問題。分布式事務通過2PC協定将送出分成兩個階段：

mysql> XA START 'xatest';

mysql> INSERT INTO mytable (i) VALUES(10);

Query OK, 1 row affected (0.04 sec)

mysql> XA END 'xatest';

mysql> XA PREPARE 'xatest';

mysql> XA COMMIT 'xatest';

階段一為準備（prepare）階段。即所有的參與者準備執行事務并鎖住需要的資源。參與者ready時，向transaction manager報告已準備就緒。

階段二為送出階段（commit）。當transaction manager确認所有參與者都ready後，向所有參與者發送commit指令。

如下圖所示：

因為XA 事務是基于兩階段送出協定的，是以需要有一個事務協調者（transaction manager）來保證所有的事務參與者都完成了準備工作(第一階段)。如果事務協調者（transaction manager）收到所有參與者都準備好的消息，就會通知所有的事務都可以送出了（第二階段）。MySQL 在這個XA事務中扮演的是參與者的角色，而不是事務協調者（transaction manager）。

XA事務的性能問題

XA的性能很低。一個資料庫的事務和多個資料庫間的XA事務性能對比可發現，性能差10倍左右。是以要盡量避免XA事務，例如可以将資料寫入本地，用高性能的消息系統分發資料。或使用資料庫複制等技術。隻有在這些都無法實作，且性能不是瓶頸時才應該使用XA。并發高的情況下不建議使用，可以借助redis或其他方法來改造。

關于XA事務的問題及優化的方案有什麼建議可以留言溝通。

原文位址

https://www.cnblogs.com/gjc592/archive/2019/07/24/11240811.html