Support for XA transactions is available for the
InnoDB storage engine. The MySQL 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
https://www.opengroup.org/public/pubs/catalog/c193.htm.
Limitations of the current XA implementation are described in
Section I.5, “Restrictions on XA Transactions”.
On the client side, there are no special requirements. The XA
interface to a MySQL server consists of SQL statements that
begin with the XA keyword. MySQL client
programs must be able to send SQL statements and to understand
the semantics of the XA statement interface. They do not need be
linked against a recent client library. Older client libraries
also will work.
Currently, among the MySQL Connectors, MySQL Connector/J 5.0.0
supports XA directly (by means of a class interface that handles
the XA SQL statement interface for you).
XA supports distributed transactions; that is, the ability to
allow multiple separate transactional resources to participate
in a global transaction. Transactional resources often are
RDBMSs but may be other kinds of resources.
A global transaction involves several actions that are
transactional in themselves, but that all must either complete
successfully as a group, or all be rolled back as a group. In
essence, this extends ACID properties “up a level”
so that multiple ACID transactions can be executed in concert as
components of a global operation that also has ACID properties.
(However, for a distributed transaction, you must use the
SERIALIZABLE isolation level to achieve ACID
properties. It is enough to use REPEATABLE
READ for a non-distributed transaction, but not for a
distributed transaction.)
Some examples of distributed transactions:
An application may act as an integration tool that combines
a messaging service with an RDBMS. The application makes
sure that transactions dealing with message sending,
retrieval, and processing that also involve a transactional
database all happen in a global transaction. You can think
of this as “transactional email.”
An application performs actions that involve different
database servers, such as a MySQL server and an Oracle
server (or multiple MySQL servers), where actions that
involve multiple servers must happen as part of a global
transaction, rather than as separate transactions local to
each server.
A bank keeps account information in an RDBMS and distributes
and receives money via automated teller machines (ATMs). It
is necessary to ensure that ATM actions are correctly
reflected in the accounts, but this cannot be done with the
RDBMS alone. A global transaction manager integrates the ATM
and database resources to ensure overall consistency of
financial transactions.
Applications that use global transactions involve one or more
Resource Managers and a Transaction Manager:
A Resource Manager (RM) provides access to transactional
resources. A database server is one kind of resource
manager. It must be possible to either commit or roll back
transactions managed by the RM.
A Transaction Manager (TM) coordinates the transactions that
are part of a global transaction. It communicates with the
RMs that handle each of these transactions. The individual
transactions within a global transaction are
“branches” of the global transaction. Global
transactions and their branches are identified by a naming
scheme described later.
The MySQL implementation of XA MySQL enables a MySQL server to
act as a Resource Manager that handles XA transactions within a
global transaction. A client program that connects to the MySQL
server acts as the Transaction Manager.
To carry out a global transaction, it is necessary to know which
components are involved, and bring each component to a point
when it can be committed or rolled back. Depending on what each
component reports about its ability to succeed, they must all
commit or roll back as an atomic group. That is, either all
components must commit, or all components musts roll back. To
manage a global transaction, it is necessary to take into
account that any component or the connecting network might fail.
The process for executing a global transaction uses two-phase
commit (2PC). This takes place after the actions performed by
the branches of the global transaction have been executed.
In the first phase, all branches are prepared. That is, they
are told by the TM to get ready to commit. Typically, this
means each RM that manages a branch records the actions for
the branch in stable storage. The branches indicate whether
they are able to do this, and these results are used for the
second phase.
In the second phase, the TM tells the RMs whether to commit
or roll back. If all branches indicated when they were
prepared that they will be able to commit, all branches are
told to commit. If any branch indicated when it was prepared
that it will not be able to commit, all branches are told to
roll back.
In some cases, a global transaction might use one-phase commit
(1PC). For example, when a Transaction Manager finds that a
global transaction consists of only one transactional resource
(that is, a single branch), that resource can be told to prepare
and commit at the same time.
