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

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 global transaction identifier. bqual is 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. xids 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

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.