MySQL database distributed transaction xa detailed

Introduction to XA transactions

The basis of an XA transaction is a two-phase commit protocol. There is a need for a coordinator of the transaction to ensure that all the transaction participants have completed the preparatory work (phase I). If the coordinator receives a message that all participants are prepared, they will be notified that all transactions can be submitted (phase II). MySQL plays the role of the participant in this XA transaction, not the Coordinator (transaction manager).

The XA transaction for MySQL is divided into internal xa and external XA. External XA can participate in external distributed transactions, requiring the application layer to intervene as the coordinator; the internal XA transaction is used for cross-engine transactions under the same instance, and Binlog as the coordinator, such as when a storage engine commits, the commit information needs to be written to the binary log, which is a distributed internal XA transaction, Only the participants in the binary log are MySQL itself. Mysql plays the role of a participant in the XA transaction, not the coordinator.

MySQL XA Transaction Basic syntax

XA {start| BEGIN} XID [join| RESUME] starts an XA transaction (XID must be a unique value; [join| RESUME] words are not supported)

Xa end XID [SUSPEND [for MIGRATE]] end an XA transaction ([SUSPEND [for MIGRATE]] words are not supported)

XA PREPARE XID Preparation

Xa commit XID [one PHASE] commit XA transaction

Xa ROLLBACK xid rolling back XA transactions

Xa RECOVER view all XA transactions in the Prepare phase

Transaction identifier XID

XID is a transaction identifier that is provided by the client or is generated by a MySQL server.

The XID format is generally 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 Gtrid and bqual values. According to the syntax, bqual and FormatID are optional. If not given, the default bqual value is '. If not given, the default Fromatid value is 1.

XA transaction status Progress process

1. Use XA start to start an XA transaction and set it to the active state.

2. For an active XA transaction, publish the SQL statement that constitutes the transaction, and then publish an XA end statement, and the XA end puts the transaction into an idle state.

3. For an idle XA transaction, publish an XA prepare statement or an XA COMMIT ... One phase statement: The former sets the transaction to the prepare state, at which point the output of the XA RECOVER statement contains the XID value of the transaction (the XA RECOVER statement lists all XA transactions in the prepare state); the latter is used to prepare and commit transactions and not be XA Recover is listed because the transaction has been terminated.

4. For a prepare XA transaction, you can publish an XA commit statement to commit and terminate the transaction, or publish an XA ROLLBACK to roll back and terminate the transaction.

Simple XA transaction operation flow

[Plain]View PlainCopyprint?

mysql> XA START ' xatest ';

Query OK, 0 rows Affected (0.00 sec)

Mysql> INSERT into Test (Name,tel) VALUES (' 123 ', ' 123 ');

Query OK, 1 row Affected (0.00 sec)

mysql> XA END ' xatest ';

Query OK, 0 rows Affected (0.00 sec)

mysql> XA PREPARE ' xatest ';

Query OK, 0 rows Affected (0.00 sec)

Mysql>

Mysql>

mysql> XA COMMIT ' xatest ';

Query OK, 0 rows Affected (0.00 sec)

X A RECOVER Introduction

Xa RECOVER lists all XA transactions in the Prepare state:

[Python]View PlainCopyprint?

1. MYSQL> XA RECOVER;  
2. +----------+-- ------------+--------------+--------+  
3. | formatid | gtrid_length  | bqual_length | data   |  
4. +----------+--------------+ + -------------+--------+  
5. |        1 |             6 |            0 | XA1000 |  
6. +----------+-------------- +--------------+--------+  
7. 1 row  in set  (0.00 sec)   

Comments:

1. FormatID is the FormatID part of the transaction XID.

2. Gtrid_length is the length of the gtrid portion of the XID, in bytes.

3. Bqual_length is the length of the bqual portion of the XID, in bytes.

4. Data is the concatenation of the Gtrid and bqual parts of the XID.

In a client environment, XA transactions and local (non-XA) transactions are mutually exclusive, and if XA start has been published to open a transaction, the local transaction will not be started, knowing that the XA transaction is committed or rolled back, and conversely, if the start is already used Transaction initiates a local transaction, the XA statement cannot be used until the transaction is committed or rolled back, and the XA transaction is only supported by the INNODB storage engine.

If the MySQL server goes down when the XA transaction reaches the prepare state, when the server restarts, the server rolls back any outstanding XA transactions, even if the transaction has reached the prepare state, and if the client connection terminates and the server continues to run, the server rolls back any outstanding XA transactions. Even if the transaction has reached the prepared state.

MySQL database distributed transaction xa detailed