Overview
--------

The 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
http://www.opengroup.org/public/pubs/catalog/c193.htm.

XA 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.

In MariaDB, XA transactions can only be used with storage engines that support
them. At least InnoDB, TokuDB, SPIDER and MyRocks support them. For InnoDB,
until MariaDB 10.2, XA transactions can be disabled by setting the
innodb_support_xa server system variable to 0. From MariaDB 10.3, XA
transactions are always supported.

Like regular transactions, XA transactions create metadata locks on accessed
tables.

XA transactions require REPEATABLE READ as a minimum isolation level. However,
distributed transactions should always use SERIALIZABLE.

Trying 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').

The statements that cause an implicit COMMIT for regular transactions produce
a 1400 error (SQLSTATE 'XAE09') if a XA transaction is in effect.

Internal XA vs External XA
--------------------------

XA transactions are an overloaded term in MariaDB. If a storage engine is
XA-capable, it can mean one or both of these:

* 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.

* 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.

Transaction Coordinator Log
---------------------------

If you have two or more XA-capable storage engines enabled, then a transaction
coordinator log must be available.

There are currently two implementations of the transaction coordinator log:

* Binary log-based transaction coordinator log
* Memory-mapped file-based transaction coordinator log

If 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.

See Transaction Coordinator Log for more information.

Syntax
------

XA {START|BEGIN} xid [JOIN|RESUME]

XA END xid [SUSPEND [FOR MIGRATE]]

XA PREPARE xid

XA COMMIT xid [ONE PHASE]

XA ROLLBACK xid

XA RECOVER [FORMAT=['RAW'|'SQL']]

xid: gtrid [, bqual [, formatID ]]

The 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.

XA START (or BEGIN) starts a transaction and defines its xid (a transaction
identifier). The JOIN or RESUME keywords have no effect. The new transaction
will be in ACTIVE state.

The 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.

XA END declares that the specified ACTIVE transaction is finished and it
changes its state to IDLE. SUSPEND [FOR MIGRATE] has no effect.

XA PREPARE prepares an IDLE transaction for commit, changing its state to
PREPARED. This is the first commit.

XA 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.

XA ROLLBACK rolls back and terminates an IDLE or PREPARED transaction.

XA RECOVER shows information about all PREPARED transactions.

When trying to execute an operation which is not allowed for the transaction's
current state, an error is produced:

XA COMMIT 'test' ONE PHASE;
ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global
transaction is in the  ACTIVE state

XA COMMIT 'test2';
ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global
transaction is in the  NON-EXISTING state

XA RECOVER
----------

The 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.

XA RECOVER produces four columns:

XA RECOVER;
+----------+--------------+--------------+------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+------+
|        1 |            4 |            0 | test |
+----------+--------------+--------------+------+

MariaDB starting with 10.3.3
----------------------------
You 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.

formatID is the formatID part of xid.

data are the gtrid and bqual parts of xid, concatenated.

gtrid_length and bqual_length are the lengths of gtrid and bqual, respectevely.

Examples
--------

2-phases commit:

XA START 'test';

INSERT INTO t VALUES (1,2);

XA END 'test';

XA PREPARE 'test';

XA COMMIT 'test';

1-phase commit:

XA START 'test';

INSERT INTO t VALUES (1,2);

XA END 'test';

XA COMMIT 'test' ONE PHASE;

Human-readable:

xa start '12\r34\t67\v78', 'abc\ndef', 3;

insert t1 values (40);

xa end '12\r34\t67\v78', 'abc\ndef', 3;

xa prepare '12\r34\t67\v78', 'abc\ndef', 3;

xa recover format='RAW';
+----------+--------------+--------------+--------------------+
| formatID | gtrid_length | bqual_length | data               |
+----------+--------------+--------------+--------------------+
34      67v78abc       11 |            7 | 12
def |
+----------+--------------+--------------+--------------------+

xa recover format='SQL';
+----------+--------------+--------------+-------------------------------------
---------+
| formatID | gtrid_length | bqual_length | data                               
     |
+----------+--------------+--------------+-------------------------------------
---------+
|        3 |           11 |            7 |
X'31320d3334093637763738',X'6162630a646566',3 |
+----------+--------------+--------------+-------------------------------------
---------+

xa rollback X'31320d3334093637763738',X'6162630a646566',3;

Known Issues
------------

MariaDB Galera Cluster
----------------------

MariaDB Galera Cluster does not support XA transactions.

However, MariaDB Galera Cluster builds include a built-in plugin called wsrep.
Prior to MariaDB 10.4.3, this plugin was internally considered an XA-capable
storage engine. Consequently, these MariaDB Galera Cluster builds have
multiple XA-capable storage engines by default, even if the only "real"
storage engine that supports external XA transactions enabled on these builds
by default is InnoDB. Therefore, when using one these builds MariaDB would be
forced to use a transaction coordinator log by default, which could have
performance implications.

See Transaction Coordinator Log Overview: MariaDB Galera Cluster for more
information.

URL: https://mariadb.com/kb/en/xa-transactions/