Description:
A fail or killed xa commit operation may lead to the lost of xa prepared transaction.
How to repeat:
Two session: session 1 and session 2 by using the mysql command of mysql5.7, such as "mysql -udbscale -pdbscale -h172.16.90.15 -P9997 wen"
------------------------------------------------------------------------------------------------------------------------
session 1 | session2
------------------------------------------------------------------------------------------------------------------------
create table t1 (c1 int) engine=innodb; |
xa start '1'; |
insert into t1 values (1); |
xa end '1'; |
xa prepare '1'; |
| flush tables with read lock;
xa commit '1'; # blocked
The "xa commit" operation of session1 will be blocked by the global read lock of session2;
Then ctrl-C on the session1:
mysql> xa commit '1';
^CCtrl-C -- sending "KILL QUERY 879" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql> Ctrl-C -- exit!
Aborted
Then do "xa recover" on session2:
mysql> xa recover;
Empty set (0.00 sec)
using the "show binlog events;" we can get the following output:
| mysql-bin.000001 | 3382 | Gtid | 59997 | 3447 | SET @@SESSION.GTID_NEXT= 'b2ffa4f8-bb62-11e6-8dab-c81f66de9032:9' |
| mysql-bin.000001 | 3447 | Query | 59997 | 3533 | XA START X'31',X'',1 |
| mysql-bin.000001 | 3533 | Table_map | 59997 | 3577 | table_id: 1325736 (wen.t1) |
| mysql-bin.000001 | 3577 | Write_rows | 59997 | 3617 | table_id: 1325736 flags: STMT_END_F |
| mysql-bin.000001 | 3617 | Query | 59997 | 3701 | XA END X'31',X'',1 |
| mysql-bin.000001 | 3701 | XA_prepare | 59997 | 3738 | XA PREPARE X'31',X'',1
Suggested fix:
None.
Description: A fail or killed xa commit operation may lead to the lost of xa prepared transaction. How to repeat: Two session: session 1 and session 2 by using the mysql command of mysql5.7, such as "mysql -udbscale -pdbscale -h172.16.90.15 -P9997 wen" ------------------------------------------------------------------------------------------------------------------------ session 1 | session2 ------------------------------------------------------------------------------------------------------------------------ create table t1 (c1 int) engine=innodb; | xa start '1'; | insert into t1 values (1); | xa end '1'; | xa prepare '1'; | | flush tables with read lock; xa commit '1'; # blocked The "xa commit" operation of session1 will be blocked by the global read lock of session2; Then ctrl-C on the session1: mysql> xa commit '1'; ^CCtrl-C -- sending "KILL QUERY 879" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted mysql> Ctrl-C -- exit! Aborted Then do "xa recover" on session2: mysql> xa recover; Empty set (0.00 sec) using the "show binlog events;" we can get the following output: | mysql-bin.000001 | 3382 | Gtid | 59997 | 3447 | SET @@SESSION.GTID_NEXT= 'b2ffa4f8-bb62-11e6-8dab-c81f66de9032:9' | | mysql-bin.000001 | 3447 | Query | 59997 | 3533 | XA START X'31',X'',1 | | mysql-bin.000001 | 3533 | Table_map | 59997 | 3577 | table_id: 1325736 (wen.t1) | | mysql-bin.000001 | 3577 | Write_rows | 59997 | 3617 | table_id: 1325736 flags: STMT_END_F | | mysql-bin.000001 | 3617 | Query | 59997 | 3701 | XA END X'31',X'',1 | | mysql-bin.000001 | 3701 | XA_prepare | 59997 | 3738 | XA PREPARE X'31',X'',1 Suggested fix: None.