Description\n-----------\n\nThe MariaDB XA implementation is based on the X/Open CAE document Distributed Transaction Processing: The XA Specification. This document is published by The Open Group and available at c193.htm.\n\nXA transactions are designed to allow distributed transactions, where a transaction manager (the application) controls a transaction which involves multiple resources. Such resources are usually DBMSs, but could be resources of any type. The whole set of required transactional operations is called a global transaction. Each subset of operations which involve a single resource is called a local transaction. XA used a 2-phases commit (2PC). With the first commit, the transaction manager tells each resource to prepare an effective commit, and waits for a confirm message. The changes are not still made effective at this point. If any of the resources encountered an error, the transaction manager will rollback the global transaction. If all resources communicate that the first commit is successful, the transaction manager can require a second commit, which makes the changes effective.\n\nIn MariaDB, XA transactions can only be used with storage engines that support them. At least InnoDB, TokuDB, SPIDER and MyRocks support them. XA transactions are always supported.\n\nLike regular transactions, XA transactions create metadata locks on accessed tables.\n\nXA transactions require REPEATABLE READ as a minimum isolation level. However, distributed transactions should always use SERIALIZABLE.\n\nTrying to start more than one XA transaction at the same time produces a 1400 error (SQLSTATE 'XAE09'). The same error is produced when attempting to start an XA transaction while a regular transaction is in effect. Trying to start a regular transaction while an XA transaction is in effect produces a 1399 error (SQLSTATE 'XAE07').\n\nThe statements that cause an implicit COMMIT for regular transactions produce a 1400 error (SQLSTATE 'XAE09') if a XA transaction is in effect.\n\nInternal XA vs External XA\n\nXA transactions are an overloaded term in MariaDB. If a storage engine is XA-capable, it can mean one or both of these:\n\n It supports MariaDB's internal two-phase commit API. This is transparent to the user. Sometimes this is called "internal XA", since MariaDB's internal transaction coordinator log can handle coordinating these transactions.\n It supports XA transactions, with the XA START, XA PREPARE, XA COMMIT, etc. statements. Sometimes this is called "external XA", since it requires the use of an external transaction coordinator to use this feature properly.\n\nTransaction Coordinator Log\n\nIf you have two or more XA-capable storage engines enabled, then a transaction coordinator log must be available.\n\nThere are currently two implementations of the transaction coordinator log:\n\n Binary log-based transaction coordinator log\n Memory-mapped file-based transaction coordinator log\n\nIf the binary log is enabled on a server, then the server will use the binary log-based transaction coordinator log. Otherwise, it will use the memory-mapped file-based transaction coordinator log.\n\nSee Transaction Coordinator Log for more information.\n\nSyntax\n\n``sql\nXA {START|BEGIN} xid [JOIN|RESUME]\n\nXA END xid [SUSPEND [FOR MIGRATE]]\n\nXA PREPARE xid\n\nXA COMMIT xid [ONE PHASE]\n\nXA ROLLBACK xid\n\nXA RECOVER [FORMAT=['RAW'|'SQL']]\n\nxid: gtrid [, bqual [, formatID ]]\n`\n\nThe interface to XA transactions is a set of SQL statements starting with XA. Each statement changes a transaction's state, determining which actions it can perform. A transaction which does not exist is in the NON-EXISTING state.\n\nWhen trying to execute an operation which is not allowed for the transaction's current state, an error is produced:\n\n`sql\nXA COMMIT 'test' ONE PHASE;\nERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state\n\nXA COMMIT 'test2';\nERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the  NON-EXISTING state\n`\n\nXA START\n\n`sql\nXA {START|BEGIN} xid [JOIN|RESUME]\n`\n\nXA START (or BEGIN) starts a transaction and defines its xid (a transaction identifier). The new transaction will be in ACTIVE state.\n\nThe xid can have 3 components, though only the first one is mandatory. gtrid is a quoted string representing a global transaction identifier. bqual is a quoted string representing a local transaction identifier. formatID is an unsigned integer indicating the format used for the first two components; if not specified, defaults to 1. MariaDB does not interpret in any way these components, and only uses them to identify a transaction. xids of transactions in effect must be unique.\n\nUsing the JOIN or RESUME keywords will currently cause an error to be returned.\n\n`sql\nXA START 'test' RESUME;\nERROR 1398 (XAE05): XAER_INVAL: Invalid arguments (or unsupported command)\nXA START 'test' JOIN;\nERROR 1398 (XAE05): XAER_INVAL: Invalid arguments (or unsupported command)\n`\n\nAn exception to this is that XA START xid RESUME will resume the transaction if the xid is the same as the previous xid used in XA END xid. This simply undoes the XA END and moves it from the IDLE state back into ACTIVE.\n\n`sql\nXA START 'test';\nINSERT INTO t VALUES (1,2);\nXA END 'test';\nXA START 'test' RESUME; -- This reverts the XA END and continues the transaction\nINSERT INTO t VALUES (3,4);\nXA END 'test';\nXA PREPARE 'test';\nXA COMMIT 'test';\n`\n\nXA END\n\n`sql\nXA END xid [SUSPEND [FOR MIGRATE]]\n`\n\nXA END declares that the specified ACTIVE transaction is finished and it changes its state to IDLE. SUSPEND [FOR MIGRATE] has no effect.\n\nXA PREPARE\n\n`sql\nXA PREPARE xid\n`\n\nXA PREPARE prepares an IDLE transaction for commit, changing its state to PREPARED. Prepared transactions are stored persistently and will survive disconnects and server crashes, and must be explicitly committed or rolled back.\n\nPrepared transactions were automatically rolled back on client disconnect, but were not rolled back if the server was crashed or killed. This violated XA guarantees and could have caused inconsistent data, if the transaction in question was already irrevocably committed in another XA participant.\n\nPrepared transactions are automatically rolled back on client disconnect, but are not rolled back if the server was crashed or killed. This violated XA guarantees and can cause inconsistent data, if the transaction in question was already irrevocably committed in another XA participant.\n\nXA COMMIT\n\n`sql\nXA COMMIT xid [ONE PHASE]\n`\n\nXA COMMIT definitely commits and terminates a transaction which has already been PREPARED. If the ONE PHASE clause is specified, this statements performs a 1-phase commit on an IDLE transaction.\n\nXA ROLLBACK\n\n`sql\nXA ROLLBACK xid\n`\n\nXA ROLLBACK rolls back and terminates an IDLE or PREPARED transaction.\n\nXA RECOVER\n\n`sql\nXA RECOVER [FORMAT=['RAW'|'SQL']]\n`\n\nThe XA RECOVER statement shows information about all transactions which are in the PREPARED state. It does not matter which connection created the transaction: if it has been PREPARED, it appears. But this does not mean that a connection can commit or rollback a transaction which was started by another connection. Note that transactions using a 1-phase commit are never in the PREPARED state, so they cannot be shown by XA RECOVER.\n\nXA RECOVER produces four columns:\n\n`sql\nXA RECOVER;\n+----------+--------------+--------------+------+\n| formatID | gtrid_length | bqual_length | data |\n+----------+--------------+--------------+------+\n|        1 |            4 |            0 | test |\n+----------+--------------+--------------+------+\n`\n\nYou can use XA RECOVER FORMAT='SQL' to get the data in a human readable form that can be directly copy-pasted into XA COMMIT or XA ROLLBACK. This is particularly useful for binary xid generated by some transaction coordinators.\n\nformatID is the formatID part of xid.\n\ndata are the gtrid and bqual parts of xid, concatenated.\n\ngtrid_length and bqual_length are the lengths of gtrid and bqual`, respectively.\n\nExamples\n--------\n\nXA START 'test';\n\nINSERT INTO t VALUES (1,2);\n\nXA END 'test';\n\nXA PREPARE 'test';\n\nXA COMMIT 'test';\n\nURL: https://mariadb.com/docs/server/reference/sql-statements/transactions/xa-transactions