為何出現了trx_mysql_thread_id為0 的事務

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

1.  問題現象

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

2. 排查過程

發現此類情況後，挑了其中一個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;

結果集中並無對該表的任何操作，因此，很大可能是有未提交的事務了。

 # 查看事務

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事務。

3. 處理過程

因為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信息

測試能否正常更新記錄

# 發現也已正常

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

 4.  XA事務（分散式事務）淺析

mysql> XA START 'xatest';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO mytable (i) VALUES(10);
Query OK, 1 row affected (0.04 sec)

mysql> XA END 'xatest';
Query OK, 0 rows affected (0.00 sec)

mysql> XA PREPARE 'xatest';
Query OK, 0 rows affected (0.00 sec)

mysql> XA COMMIT 'xatest';
Query OK, 0 rows affected (0.00 sec)

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

XA事務的性能問題

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

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

耿小廚已開通個人微信公眾號，想進一步溝通或想瞭解其他文章的同學可以關註我