今天巡檢時突然發現有很多鎖等待超時的情況,原以為是一個簡單的小事,一查,結果令人深思。 1. 問題現象 發現日誌中出現了大量的 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 錯誤 2. 排查過程 ...
今天巡檢時突然發現有很多鎖等待超時的情況,原以為是一個簡單的小事,一查,結果令人深思。
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事務(分散式事務)淺析
在本應用中,為了降低單點壓力,根據業務情況進行了分表分庫,將表分佈在不同的庫中(庫分佈在不同的機器上)。在這種場景下,事務的提交會變得相對複雜,因為多個節點(庫)的存在,可能存在部分節點提交失敗的情況,即事務的ACID特性需要在各個不同的資料庫實例中保證。比如更新db1庫的A表時,必須同步更新db2庫的B表,兩個更新形成一個事務,要麼都成功,要麼都失敗,起初,為了簡化應用程式在事務處理的難度,因此直接使用MySQL資料庫的分散式事務。 兩階段提交 分散式事務通常採用2PC協議,全稱Two Phase Commitment Protocol。該協議主要為瞭解決在分散式資料庫場景下,所有節點間數據一致性的問題。分散式事務通過2PC協議將提交分成兩個階段: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)階段一為準備(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事務的問題及優化的方案有什麼建議可以留言溝通。
耿小廚已開通個人微信公眾號,想進一步溝通或想瞭解其他文章的同學可以關註我