XA 事务

支持项

  • 支持数据分片后的跨库事务；
  • 两阶段提交保证操作的原子性和数据的强一致性；
  • 服务宕机重启后，提交/回滚中的事务可自动恢复；
  • 支持同时使用 XA 和非 XA 的连接池。

不支持项

  • 服务宕机后，在其它机器上恢复提交/回滚中的数据。

通过 XA 语句控制的分布式事务

  • 通过 XA START 可以手动开启 XA 事务，注意该事务完全由用户管理，ShardingSphere 只负责将语句转发至后端数据库；
  • 服务宕机后，需要通过 XA RECOVER 获取未提交或回滚的事务，也可以在 COMMIT 时使用 ONE PHASE 跳过 PERPARE。
  1. MySQL [(none)]> use test1                                                                               │MySQL [(none)]> use test2
  2. Reading table information for completion of table and column names                                      │Reading table information for completion of table and column names
  3. You can turn off this feature to get a quicker startup with -A                                          │You can turn off this feature to get a quicker startup with -A
  4.                                                                                                         │
  5. Database changed                                                                                        │Database changed
  6. MySQL [test1]> XA START '61c052438d3eb';                                                                │MySQL [test2]> XA START '61c0524390927';
  7. Query OK, 0 rows affected (0.030 sec)                                                                   │Query OK, 0 rows affected (0.009 sec)
  8.                                                                                                         │
  9. MySQL [test1]> update test set val = 'xatest1' where id = 1;                                            │MySQL [test2]> update test set val = 'xatest2' where id = 1;
  10. Query OK, 1 row affected (0.077 sec)                                                                    │Query OK, 1 row affected (0.010 sec)
  11.                                                                                                         │
  12. MySQL [test1]> XA END '61c052438d3eb';                                                                  │MySQL [test2]> XA END '61c0524390927';
  13. Query OK, 0 rows affected (0.006 sec)                                                                   │Query OK, 0 rows affected (0.008 sec)
  14.                                                                                                         │
  15. MySQL [test1]> XA PREPARE '61c052438d3eb';                                                              │MySQL [test2]> XA PREPARE '61c0524390927';
  16. Query OK, 0 rows affected (0.018 sec)                                                                   │Query OK, 0 rows affected (0.011 sec)
  17.                                                                                                         │
  18. MySQL [test1]> XA COMMIT '61c052438d3eb';                                                               │MySQL [test2]> XA COMMIT '61c0524390927';
  19. Query OK, 0 rows affected (0.011 sec)                                                                   │Query OK, 0 rows affected (0.018 sec)
  20.                                                                                                         │
  21. MySQL [test1]> select * from test where id = 1;                                                         │MySQL [test2]> select * from test where id = 1;
  22. +----+---------+                                                                                        │+----+---------+
  23. | id | val     |                                                                                        │| id | val     |
  24. +----+---------+                                                                                        │+----+---------+
  25. |  1 | xatest1 |                                                                                        │|  1 | xatest2 |
  26. +----+---------+                                                                                        │+----+---------+
  27. 1 row in set (0.016 sec)                                                                                │1 row in set (0.129 sec)
  29. MySQL [test1]> XA START '61c05243994c3';                                                                │MySQL [test2]> XA START '61c052439bd7b';
  30. Query OK, 0 rows affected (0.047 sec)                                                                   │Query OK, 0 rows affected (0.006 sec)
  31.                                                                                                         │
  32. MySQL [test1]> update test set val = 'xarollback' where id = 1;                                         │MySQL [test2]> update test set val = 'xarollback' where id = 1;
  33. Query OK, 1 row affected (0.175 sec)                                                                    │Query OK, 1 row affected (0.008 sec)
  34.                                                                                                         │
  35. MySQL [test1]> XA END '61c05243994c3';                                                                  │MySQL [test2]> XA END '61c052439bd7b';
  36. Query OK, 0 rows affected (0.007 sec)                                                                   │Query OK, 0 rows affected (0.014 sec)
  37.                                                                                                         │
  38. MySQL [test1]> XA PREPARE '61c05243994c3';                                                              │MySQL [test2]> XA PREPARE '61c052439bd7b';
  39. Query OK, 0 rows affected (0.013 sec)                                                                   │Query OK, 0 rows affected (0.019 sec)
  40.                                                                                                         │
  41. MySQL [test1]> XA ROLLBACK '61c05243994c3';                                                             │MySQL [test2]> XA ROLLBACK '61c052439bd7b';
  42. Query OK, 0 rows affected (0.010 sec)                                                                   │Query OK, 0 rows affected (0.010 sec)
  43.                                                                                                         │
  44. MySQL [test1]> select * from test where id = 1;                                                         │MySQL [test2]> select * from test where id = 1;
  45. +----+---------+                                                                                        │+----+---------+
  46. | id | val     |                                                                                        │| id | val     |
  47. +----+---------+                                                                                        │+----+---------+
  48. |  1 | xatest1 |                                                                                        │|  1 | xatest2 |
  49. +----+---------+                                                                                        │+----+---------+
  50. 1 row in set (0.009 sec)                                                                                │1 row in set (0.083 sec)
  52. MySQL [test1]>  XA START '61c052438d3eb';
  53. Query OK, 0 rows affected (0.030 sec)
  55. MySQL [test1]> update test set val = 'recover' where id = 1;
  56. Query OK, 1 row affected (0.072 sec)
  58. MySQL [test1]> select * from test where id = 1;
  59. +----+---------+
  60. | id | val     |
  61. +----+---------+
  62. |  1 | recover |
  63. +----+---------+
  64. 1 row in set (0.039 sec)
  66. MySQL [test1]>  XA END '61c052438d3eb';
  67. Query OK, 0 rows affected (0.005 sec)
  69. MySQL [test1]> XA PREPARE '61c052438d3eb';
  70. Query OK, 0 rows affected (0.020 sec)
  72. MySQL [test1]> XA RECOVER;
  73. +----------+--------------+--------------+---------------+
  74. | formatID | gtrid_length | bqual_length | data          |
  75. +----------+--------------+--------------+---------------+
  76. |        1 |           13 |            0 | 61c052438d3eb |
  77. +----------+--------------+--------------+---------------+
  78. 1 row in set (0.010 sec)
  80. MySQL [test1]> XA RECOVER CONVERT XID;
  81. +----------+--------------+--------------+------------------------------+
  82. | formatID | gtrid_length | bqual_length | data                         |
  83. +----------+--------------+--------------+------------------------------+
  84. |        1 |           13 |            0 | 0x36316330353234333864336562 |
  85. +----------+--------------+--------------+------------------------------+
  86. 1 row in set (0.011 sec)
  88. MySQL [test1]> XA COMMIT 0x36316330353234333864336562;
  89. Query OK, 0 rows affected (0.029 sec)
  91. MySQL [test1]> XA RECOVER;
  92. Empty set (0.011 sec)