Databases Reference
In-Depth Information
outside the database—with a two-phase commit. MySQL 5.0 and newer have partial
support for XA transactions.
An XA transaction requires a transaction coordinator, which asks all participants to
prepare to commit (phase one). When the coordinator receives a “ready” from all
participants, it tells them all to go ahead and commit. This is phase two. MySQL can
act as a participant in XA transactions, but not as a coordinator.
There are actually two kinds of XA transactions in MySQL. The MySQL server can
participate in an externally managed distributed transaction, but it also uses XA inter-
nally to coordinate storage engines and binary logging.
Internal XA Transactions
The reason for MySQL's internal use of XA transactions is the architectural separation
between the server and the storage engines. Storage engines are completely independent
from and unaware of each other, so any cross-engine transaction is distributed by nature
and requires a third party to coordinate it. That third party is the MySQL server. Were
it not for XA transactions, for example, a cross-engine transaction commit would re-
quire sequentially asking each engine involved to commit. That would introduce the
possibility of a crash after one engine had committed but before another did, which
would break the rules of transactions (recall that transactions are supposed to be all-
or-nothing operations).
If you consider the binary log to be a “storage engine” for log events, you can see why
XA transactions are necessary even when only a single transactional engine is involved.
Synchronizing a storage engine commit with “committing” an event to the binary log
is a distributed transaction, because the server—not the storage engine—handles the
binary log.
XA currently creates a performance dilemma. It has broken InnoDB's support for
group
commit
(a technique that can commit several transactions with a single I/O operation)
since MySQL 5.0, so it causes many more
fsync()
calls than it should.
12
It also causes
each transaction to require a binary log sync if binary logs are enabled and requires two
InnoDB transaction log flushes per commit instead of one. In other words, if you want
the binary log to be safely synchronized with your transactions, each transaction will
require a total of at least three
fsync()
calls. The only way to prevent this is to disable
the binary log and set
innodb_support_xa
to
0
.
13
12. At the time of writing, a lot of work has gone into fixing the group commit problem, and there are at least
three competing implementations. It remains to be seen which one ends up in the official MySQL source
code that most people will use, or which version it will be fixed in. The version available in MariaDB and
Percona Server appears to be a good solution.
13. A common misconception is that
innodb_support_xa
is only needed if you use XA transactions. This is
incorrect: it controls the internal XA transactions between the storage engine and the binary log, and if
you value your data, you need this setting to be enabled.
