The Ultimate Guide to High Availability with MariaDB

XA Transactions

You are viewing an old version of this article. View the current version here.

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 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 successfull, the transaction managaer 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 and SPIDER support them. For InnoDB, XA transactions can be disabled by setting the innodb_support_xa server system variable to 0. XA is currently not available with MariaDB Galera Cluster.

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 cuase an implicit COMMIT for regular transactions produce a 1400 error (SQLSTATE 'XAE09') if a XA transaction is in effect.

Syntax

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

XA END xid [SUSPEND [FOR MIGRATE]]

XA PREPARE xid

XA COMMIT xid [ONE PHASE]

XA ROLLBACK xid [FORMAT=['RAW' | 'SQL' ]]

XA RECOVER

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, 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; 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 |
+----------+--------------+--------------+------+

You can use XA RECOVER FORMAT='SQL' to get the data in a readable form suitable for XA COMMIT or XA ROLLBACK.

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;

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.