叶老师，我做个一个分布式事务测试，你看看：
第一步：开启一个分布式事务，执行到prepare状态。
mysql> select * from t;
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 2 | bbb |
| 3 | cdc |
| 4 | ddd |
+----+------+
4 rows in set (0.00 sec)
mysql> XA start 'test002';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values(5,'fff');
Query OK, 1 row affected (0.01 sec)
mysql> delete from t where id=5;
Query OK, 1 row affected (0.00 sec)
mysql> XA END 'test002';
Query OK, 0 rows affected (0.00 sec)
mysql> xa prepare 'test002';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> quit
Bye
[root@kmt_mysql_106 ~]#
第二步：过一段时间后(约1小时)，重连mysql回滚事务
mysql>
mysql>
mysql> xa rollback 'test002';
Query OK, 0 rows affected (0.01 sec)
mysql> xa rollback 'test002';
ERROR 1397 (XAE04): XAER_NOTA: Unknown XID
mysql> xa rollback 'test002';
ERROR 1397 (XAE04): XAER_NOTA: Unknown XID
mysql> xa rollback 'test002';
ERROR 1397 (XAE04): XAER_NOTA: Unknown XID
mysql> xa rollback 'test002';
ERROR 1397 (XAE04): XAER_NOTA: Unknown XID
发现第一次是可以正常回滚的，后面反复执行会报错
解析binlog日志记录如下：
# at 1433
#230711 16:07:15 server id 7002 end_log_pos 1534 CRC32 0x539c7ee1 Query thread_id=716 exec_time=0 error_code=0
SET TIMESTAMP=1689062835/*!*/;
XA START X'74657374303032',X'',1
/*!*/;
# at 1534
#230711 16:07:15 server id 7002 end_log_pos 1583 CRC32 0xecc19bf9 Table_map: `testdb`.`t` mapped to number 3086
# at 1583
#230711 16:07:15 server id 7002 end_log_pos 1627 CRC32 0x5e30aea4 Write_rows: table id 3086 flags: STMT_END_F
### INSERT INTO `testdb`.`t`
### SET
### @1=5
### @2='fff'
# at 1627
#230711 16:07:45 server id 7002 end_log_pos 1676 CRC32 0xfd51a4a1 Table_map: `testdb`.`t` mapped to number 3086
# at 1676
#230711 16:07:45 server id 7002 end_log_pos 1720 CRC32 0xfe33e8cc Delete_rows: table id 3086 flags: STMT_END_F
### DELETE FROM `testdb`.`t`
### WHERE
### @1=5
### @2='fff'
# at 1720
#230711 16:08:02 server id 7002 end_log_pos 1819 CRC32 0xbd188570 Query thread_id=716 exec_time=0 error_code=0
SET TIMESTAMP=1689062882/*!*/;
XA END X'74657374303032',X'',1
/*!*/;
# at 1819
#230711 16:08:02 server id 7002 end_log_pos 1862 CRC32 0xa5937a30 XA PREPARE X'74657374303032',X'',1
XA PREPARE X'74657374303032',X'',1
/*!*/;
# at 1862
#230711 17:48:41 server id 7002 end_log_pos 1927 CRC32 0xbd5f80df GTID last_committed=5 sequence_number=6 rbr_only=no
SET @@SESSION.GTID_NEXT= '4c6fe017-85bf-11ed-bffe-0050569b5493:5418900'/*!*/;
# at 1927
#230711 17:48:41 server id 7002 end_log_pos 2025 CRC32 0x3f221bd9 Query thread_id=718 exec_time=0 error_code=0
SET TIMESTAMP=1689068921/*!*/;
XA ROLLBACK X'74657374303032',X'',1
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
binlog日志中有对应时间的prepare和rollback记录，没有反复rollback的记录，我想这应该是mysql分布式事务xa设计的正常流程。
现在遇到的问题是binlog中出现了反复冗余的rollback记录，我不知道是怎么来的？
1、如果rollback成功了，后续rollback应当执行失败，不会出现在binlog中；
2、如果rollback执行都失败了，那binlog中应该一条记录都不会有。
|
