Purpose
This statement is used for XA transaction (distributed transaction) management.
Syntax
xa_transaction_stmt:
xa_start_stmt
| xa_end_stmt
| xa_prepare_stmt
| xa_commit_stmt
| xa_rollback_stmt
| xa_recover_stmt
xa_start_stmt:
XA {START | BEGIN} xid;
xa_end_stmt:
XA END xid [SUSPEND [FOR MIGRATE]];
xa_prepare_stmt:
XA PREPARE xid;
xa_commit_stmt:
XA COMMIT xid [ONE PHASE];
xa_rollback_stmt:
XA ROLLBACK xid;
xa_recover_stmt:
XA RECOVER [CONVERT XID];
Parameters
xa_start_stmt
XA {START | BEGIN} xid: This statement is used to start a new XA transaction or join/resume an existing XA transaction.
START | BEGIN: Indicates the start of an XA transaction statement.STARTandBEGINhave the same meaning.xid: the globally unique identifier of the XA transaction.
xa_end_stmt
XA END xid [SUSPEND [FOR MIGRATE]]: This statement is used to end an XA transaction. You can choose to suspend the XA transaction for migration.
xid: the globally unique identifier of the XA transaction to be ended.SUSPEND [FOR MIGRATE]: Optional. Only the syntax is supported; the feature does not take effect.
xa_prepare_stmt
XA PREPARE xid: This statement is used to notify the resource manager to prepare for committing an XA transaction.
xid: The globally unique identifier of the XA transaction to be prepared for commit.
xa_commit_stmt
XA COMMIT xid [ONE PHASE]: This statement is used to commit a prepared XA transaction.
xid: the globally unique identifier of the XA transaction to be committed.ONE PHASE: Optional. Specifies to use one-phase commit.
xa_rollback_stmt
XA ROLLBACK xid: This statement is used to roll back an XA transaction.
xid: The globally unique identifier of the XA transaction to be rolled back.
xa_recover_stmt
XA RECOVER [CONVERT XID]: This statement is used to obtain the prepared XA transaction information.
CONVERT XID: Optional. Indicates to convert the XID format.
Examples
Create table
tbl1.CREATE TABLE tbl1 (col1 INT, col2 INT);Start a transaction.
XA START 'xa_tbl1';Insert a row of data into table
tbl1.INSERT INTO tbl1 VALUES (1, 1);End the XA transaction and suspend it for migration.
XA END 'xa_tbl1';Prepare the XA transaction.
XA PREPARE 'xa_tbl1';Commit the XA transaction.
XA COMMIT 'xa_tbl1';View the data in table
tbl1.SELECT * FROM tbl1;The return result is as follows:
+------+------+ | col1 | col2 | +------+------+ | 1 | 1 | +------+------+ 1 row in set