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

• START | BEGIN: Indicates the statement for starting an XA transaction. START and BEGIN have the same meaning.

• xid: Represents the globally unique identifier of the XA transaction.

  • JOIN | RESUME: Optional. Only the related syntax is supported, but the feature is not effective.

xa_end_stmt

XA END xid [SUSPEND [FOR MIGRATE]]: This statement is used to end an XA transaction and optionally suspend it for migration.

• xid: Represents the globally unique identifier of the XA transaction to be ended.

  • SUSPEND [FOR MIGRATE]: Optional. Only the related syntax is supported, but the feature is not effective.

xa_prepare_stmt

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

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

  • ONE PHASE: Optional. Indicates the use of one-phase commit.

xa_rollback_stmt

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

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

xa_recover_stmt

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

• CONVERT XID: Optional. Indicates the conversion of 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. View the data in the tbl1 table.

   SELECT * FROM tbl1;
   

   The result is as follows:

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

References

• XA transactions
• Long transactions
• Hung transactions