MENU
XA Transactions
XA transactions, also known as distributed transactions, are a type of transaction that involves multiple resource managers coordinating their actions to ensure atomicity and consistency across different databases or systems.XA stands for "eXtended Architecture" and refers to the X/Open XA specification, which defines a standard protocol for coordinating distributed transactions. The XA protocol allows applications to work with multiple databases or systems as part of a single transaction. It enables two-phase commit, a protocol used to ensure that all participating resource managers either commit or roll back the transaction together.
In MySQL, XA transactions are typically used in scenarios that require coordinating actions across multiple databases or systems — for example, updating data in different databases or systems within a single transaction, ensuring that either all changes are committed or none of them are.
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 [CONVERT XID] |
Each XA statement begins with the XA keyword, and most of them require an xid value. An xid is an XA transaction identifier: it indicates which transaction the statement applies to. xid values are supplied by the client, or generated by the MySQL server. An xid value has from one to three parts:
| xid: gtrid [, bqual [, formatID ]] |
gtrid is a global transaction identifier, bqual is a branch qualifier, and formatID is a number that identifies the format used by the gtrid and bqual values.
xid values are typically generated by the Transaction Manager (TM). Values generated by one TM must be different from values generated by other TMs. A given TM must be able to recognize its own xid values in a list of values returned by the XA RECOVER statement.
Example
Below is a simple XA transaction that inserts a row into a table as part of a global transaction.Detached Transactions
MySQL 8.0.29 adds support for XA transactions which, once prepared, are no longer connected to the originating connection. This means that they can be committed or rolled back by another connection, and that the current session can immediately begin another transaction.A system variable xa_detach_on_prepare controls whether XA transactions are detached; the default is ON, which causes all XA transactions to be detached. Use of temporary tables is disallowed for XA transactions when this is in effect.
XA START 'xatest';
INSERT INTO mytable (i) VALUES(10);
XA END 'xatest';
XA PREPARE 'xatest';
XA COMMIT 'xatest';