XA Transactions
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 a 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 operations 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 successfull, the transaction managaer can require a second commit, which makes the operations effective.
In MariaDB, XA transactions can only be used with storage engine which support them. At least InnoDB, TokuDB and SPIDER support them.
Like regular transactions, XA transaction create metadata locks on accessed tables.
A connection cannot start more than one XA transaction at the same time. Also, it is not possible to start an XA transaction while a regular transaction exists, or vice versa.
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 xid: gtrid [, bqual [, formatID ]]
The interface to XA transaction 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, thought 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. MariaDB does not interpret in any way these components, and only uses them to identify a transaction. xid
s of active transactions must be unique.
XA END
declares that the specified ACTIVE
transaction is finished; so, it is in IDLE
state now. 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
rollbacks and terminates a 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
statements 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. Note that transactions using a 1-phase commit are never in the PREPARED
state, so they cannot be showed by XA RECOVER
.
XA RECOVER
produces four columns:
XA RECOVER; +----------+--------------+--------------+------+ | formatID | gtrid_length | bqual_length | data | +----------+--------------+--------------+------+ | 1 | 4 | 0 | test | +----------+--------------+--------------+------+
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;