XA transaction

Purpose

This statement is used for managing XA transactions (distributed transactions).

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/recover an existing XA transaction.

• START | BEGIN: indicates the statement to start an XA transaction. START and BEGIN have 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 and optionally suspend the XA transaction for migration.

• xid: the globally unique identifier of the XA transaction to end.

  • SUSPEND [FOR MIGRATE]: optional. This option is supported only for the syntax but does not take effect.

xa_prepare_stmt

XA PREPARE xid: This statement is used to notify the resource manager to prepare to commit the XA transaction.

• xid: the globally unique identifier of the XA transaction to prepare to 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 commit.

  • ONE PHASE: optional. Indicates to use one-phase commit.

xa_rollback_stmt

XA ROLLBACK xid: This statement is used to rollback an XA transaction.

• xid: the globally unique identifier of the XA transaction to rollback.

xa_recover_stmt

XA RECOVER [CONVERT XID]: This statement is used to retrieve information about prepared XA transactions.

• CONVERT XID: optional. Indicates to convert the xid format.

Examples

1. Create a table named tbl1.

   CREATE TABLE tbl1 (col1 INT, col2 INT);
   

2. Start a transaction.

   XA START 'xa_tbl1';
   

3. Insert a row of data into the tbl1 table.

   INSERT INTO tbl1 VALUES (1, 1);
   

4. End the XA transaction and suspend it for migration.

   XA END 'xa_tbl1';
   

5. Prepare the XA transaction.

   XA PREPARE 'xa_tbl1';
   

6. Commit the XA transaction.

   XA COMMIT 'xa_tbl1';
   

7. Query the data in the tbl1 table.

   SELECT * FROM tbl1;
   

   The return result is as follows:

   +------+------+
   | col1 | col2 |
   +------+------+
   |    1 |    1 |
   +------+------+
   1 row in set
   

References

• XA transactions
• Long transactions
• Hung transactions