XA Transactions 3. For an  IDLE  XA transaction, you can issue either an  XA PREPARE  statement or an  XA COMMIT ... ONE PHASE  statement: • XA PREPARE  puts the transaction in the  PREPARED  state. An  XA RECOVER  statement at this point includes the transaction’s  xid  value in its output, because  XA RECOVER  lists all XA transactions that are in the  PREPARED  state. • XA COMMIT ... ONE PHASE  prepares and commits the transaction. The  xid  value is not listed by XA RECOVER  because the transaction terminates. 4. For a  PREPARED  XA transaction, you can issue an  XA COMMIT  statement to commit and terminate the transaction, or  XA ROLLBACK  to roll back and terminate the transaction. Here is a simple XA transaction that inserts a row into a table as part of a global transaction: mysql>  XA START ’xatest’; Query OK, 0 rows affected (0.00 sec) mysql>  INSERT INTO mytable (i) VALUES(10); Query OK, 1 row affected (0.04 sec) mysql>  XA END ’xatest’; Query OK, 0 rows affected (0.00 sec) mysql>  XA PREPARE ’xatest’; Query OK, 0 rows affected (0.00 sec) mysql>  XA COMMIT ’xatest’; Query OK, 0 rows affected (0.00 sec) Within the context of a given client connection, XA transactions and local (non-XA) transactions are mutually exclusive. For example, if  XA START  has been issued to begin an XA transaction, a local transaction cannot be started until the XA transaction has been committed or rolled back. Conversely, if a local transaction has been started with  START TRANSACTION , no XA statements can be used until the transaction has been committed or rolled back. If an XA transaction is in the  ACTIVE  state, you cannot issue any statements that cause an implicit commit. That would violate the XA contract because you could not roll back the XA transaction. Trying to execute such a statement raises the following error: ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state Statements to which the preceding remark applies are listed at Section 13.3.3, “Statements That Cause an Implicit Commit” . 13.3.8.3 Restrictions on XA Transactions XA transaction support is limited to the  InnoDB  storage engine. For “external XA,”  a MySQL server acts as a Resource Manager and client programs act as Transaction Managers. For “Internal XA” , storage engines within a MySQL server act as RMs, and the server itself acts as a TM. Internal XA support is limited by the capabilities of individual storage engines. Internal XA is required for handling XA transactions that involve more than one storage engine. The implementation of internal XA requires that a storage engine support two-phase commit at the table handler level, and currently this is true only for  InnoDB . For  XA START , the  JOIN  and  RESUME  clauses are recognized but have no effect. For  XA END  the  SUSPEND [FOR MIGRATE]  clause is recognized but has no effect. 2738