Support cross-database transactions after sharding;
Operation atomicity and high data consistency in 2PC transactions;
When service is down and restarted, commit and rollback transactions can be recovered automatically;
Support use XA and non-XA connection pool together.
Unsupported
Recover committing and rolling back in other machines after the service is down.
Savepoint.
In the transaction block, the SQL execution is abnormal, and run Commit, and data remains consistent.
XA Transaction managed by XA Statement
When using XA START to open a XA Transaction, ShardingSphere will pass it to backend database directly, you have to manage this transaction by yourself;
When recover from a crush, you have to call XA RECOVER to check unfinished transaction, and choose to commit or rollback using xid. Or you can use ONE PHASE commit without PREPARE.
MySQL [(none)]> use test1 │MySQL [(none)]> use test2
Reading table information forcompletionoftableandcolumnnames│Reading table information forcompletionoftableandcolumnnames
You can turn off this feature toget a quicker startup with-A │You can turn off this feature toget a quicker startup with-A
│Database changed │Database changed
MySQL [test1]> XA START'61c052438d3eb'; │MySQL [test2]> XA START'61c0524390927';
Query OK, 0rows affected (0.030 sec) │Query OK, 0rows affected (0.009 sec)
│
MySQL [test1]>update test set val ='xatest1'where id =1; │MySQL [test2]>update test set val ='xatest2'where id =1;
Query OK, 1row affected (0.077 sec) │Query OK, 1row affected (0.010 sec)
│
MySQL [test1]> XA END'61c052438d3eb'; │MySQL [test2]> XA END'61c0524390927';
Query OK, 0rows affected (0.006 sec) │Query OK, 0rows affected (0.008 sec)
│
MySQL [test1]> XA PREPARE'61c052438d3eb'; │MySQL [test2]> XA PREPARE'61c0524390927';
Query OK, 0rows affected (0.018 sec) │Query OK, 0rows affected (0.011 sec)
│
MySQL [test1]> XA COMMIT'61c052438d3eb'; │MySQL [test2]> XA COMMIT'61c0524390927';
Query OK, 0rows affected (0.011 sec) │Query OK, 0rows affected (0.018 sec)
│
MySQL [test1]>select*from test where id =1; │MySQL [test2]>select*from test where id =1;
+----+---------+ │+----+---------+
| id | val |│| id | val |+----+---------+ │+----+---------+
|1| xatest1 |│|1| xatest2 |+----+---------+ │+----+---------+
1rowinset (0.016 sec) │1rowinset (0.129 sec)
MySQL [test1]> XA START'61c05243994c3'; │MySQL [test2]> XA START'61c052439bd7b';
Query OK, 0rows affected (0.047 sec) │Query OK, 0rows affected (0.006 sec)
│
MySQL [test1]>update test set val ='xarollback'where id =1; │MySQL [test2]>update test set val ='xarollback'where id =1;
Query OK, 1row affected (0.175 sec) │Query OK, 1row affected (0.008 sec)
│
MySQL [test1]> XA END'61c05243994c3'; │MySQL [test2]> XA END'61c052439bd7b';
Query OK, 0rows affected (0.007 sec) │Query OK, 0rows affected (0.014 sec)
│
MySQL [test1]> XA PREPARE'61c05243994c3'; │MySQL [test2]> XA PREPARE'61c052439bd7b';
Query OK, 0rows affected (0.013 sec) │Query OK, 0rows affected (0.019 sec)
│
MySQL [test1]> XA ROLLBACK'61c05243994c3'; │MySQL [test2]> XA ROLLBACK'61c052439bd7b';
Query OK, 0rows affected (0.010 sec) │Query OK, 0rows affected (0.010 sec)
│
MySQL [test1]>select*from test where id =1; │MySQL [test2]>select*from test where id =1;
+----+---------+ │+----+---------+
| id | val |│| id | val |+----+---------+ │+----+---------+
|1| xatest1 |│|1| xatest2 |+----+---------+ │+----+---------+
1rowinset (0.009 sec) │1rowinset (0.083 sec)
MySQL [test1]> XA START'61c052438d3eb';
Query OK, 0rows affected (0.030 sec)
MySQL [test1]>update test set val ='recover'where id =1;
Query OK, 1row affected (0.072 sec)
MySQL [test1]>select*from test where id =1;
+----+---------+
| id | val |+----+---------+
|1| recover |+----+---------+
1rowinset (0.039 sec)
MySQL [test1]> XA END'61c052438d3eb';
Query OK, 0rows affected (0.005 sec)
MySQL [test1]> XA PREPARE'61c052438d3eb';
Query OK, 0rows affected (0.020 sec)
MySQL [test1]> XA RECOVER;
+----------+--------------+--------------+---------------+
| formatID | gtrid_length | bqual_length |data|+----------+--------------+--------------+---------------+
|1|13|0|61c052438d3eb |+----------+--------------+--------------+---------------+
1rowinset (0.010 sec)
MySQL [test1]> XA RECOVER CONVERT XID;
+----------+--------------+--------------+------------------------------+
| formatID | gtrid_length | bqual_length |data|+----------+--------------+--------------+------------------------------+
|1|13|0|0x36316330353234333864336562 |+----------+--------------+--------------+------------------------------+
1rowinset (0.011 sec)
MySQL [test1]> XA COMMIT0x36316330353234333864336562;
Query OK, 0rows affected (0.029 sec)
MySQL [test1]> XA RECOVER;
Empty set (0.011 sec)
