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 starts a new XA transaction or joins/restores an existing XA transaction.

• START | BEGIN: a statement that starts an XA transaction. START and BEGIN are synonyms.
• xid: the global identifier of the XA transaction.

xa_end_stmt

XA END xid [SUSPEND [FOR MIGRATE]]: This statement ends an XA transaction and can choose to suspend the XA transaction for migration.

• xid: The global unique identifier of the XA transaction to be ended.

  • SUSPEND [FOR MIGRATE]: an optional clause that is supported but has no effect.

xa_prepare_stmt

XA PREPARE xid: This statement notifies the resource manager to prepare the commit of an XA transaction.

• xid: a global identifier of the XA transaction to be prepared and committed.

xa_commit_stmt

XA COMMIT xid [ONE PHASE]: This statement is used to commit prepared XA transactions.

• xid: the global unique identifier of the XA transaction to be committed.

  • ONE PHASE: an optional keyword that specifies to use one-phase commit.

xa_rollback_stmt

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

• xid: the global unique identifier of the XA transaction to be rolled back.

xa_recover_stmt

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

• CONVERT XID: specifies whether to convert the xid format.

Example

1. Create a table named tbl1.

   CREATE TABLE tbl1 (col1 INT, col2 INT);
   

2. Start a transaction.

   XA START 'xa_tbl1';
   

3. Insert a data record into the tbl1 table.

   INSERT INTO tbl1 VALUES (1, 1);
   

4. End the XA transaction and suspend (pause) 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. View 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
• Hanging transactions