introduce
XA, which means "eXtended Architecture", is the standard created by The Open Group for distributed transaction processing. Although MySQL 5.0 is the first version to support XA, MySQL 5.7 improves the reliability of XA support, fixes many errors, and increases overall test case coverage.
background
XA solves the problem of retaining ACID attributes in a single transaction in a distributed resource set. Resources themselves can be other MySQL servers, or even different database technologies. The XA standard describes the interaction between the global transaction manager and the local resource manager.
As mentioned above, MySQL 5.0 introduces XA support, which increases the ability to participate in global transactions. XA needs a resource manager, which provides access to transaction resources, and a transaction manager to coordinate transactions in global transactions. The XA implementation of MySQL enables MySQL servers to act as resource managers, while clients connected to MySQL servers perform transaction managers.
XA uses a two-stage submission protocol, in which the first stage is to submit the request and then the actual submission. Once the branches of the global transaction are executed, the two-phase commit protocol starts:
- In the first phase, the transaction manager publishes the ready submission message to all branches involved in the global transaction. Before the resource manager confirms that it is ready for submission, it logs the results of the operation to perform the actual submission in the second phase.
- In the second stage, the transaction manager notifies all relevant branches if they receive a positive response. However, if any branch replies in a failed manner, all branches are notified to roll back.
The transaction manager interacts with multiple resource managers to handle each transaction/branch in a global transaction. The diagram depicts XA transactions involving a resource manager. The statement of an XA transaction begins with the XA keyword, the operation to be performed, and the unique identifier. In the following example, the string'xatest'represents the global transaction identifier. In addition to global transaction identifiers, you can specify branch identifiers and format IDs for XA transactions. Branch identifiers are used to identify local transactions, and format IDs specify the formats used by the first two components.
- XA START / BEGIN starts a transaction and defines its global transaction identifier.
- XA END specifies the end of an active transaction.
- XA PREPARE prepares transactions for submission.
- XA COMMIT [ONE PHASE] commits and terminates PREPARED transactions.
- If the ONE PHASE option is available, the preparation and submission are performed in the one-step end transaction.
- XA ROLLBACK rolls back and terminates the transaction.
- XA RECOVER displays information about all PREPARED transactions.
Let's look at the state transition of the XA transaction mentioned above.
XA START puts transactions in ACTIVE state. Once all statements are executed by an active transaction, an XA_END statement is issued, leaving the transaction in an IDLE state.
For IDLE transactions, XA PREPARE or XA COMMIT ONE PHASE can be issued.
XA PREPARE places transactions in the PREPARED state. XA COMMIT ONE PHASE prepares and submits transactions.
For PREPARED XA transactions, issue XA COMMIT to commit the end of the transaction.
Major improvements
Before 5.7.7, if the client connection terminates or the server exits normally, the PREPARED transaction is rolled back. When the client is Kill, all transactions are rolled back. Therefore, even if the XA transaction is in the PREPARED state, it cannot recover the transaction during the XA RECOVER period. Ideally, when the transaction is PREPARED, you should be able to COMMIT or ROLLBACK transactions. For this case, see the following examples:
mysql> CREATE TABLE t1(fld1 INT); Query OK, 0 rows affected (0.01 sec) mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) mysql> XA START 'test'; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t1 VALUES (1); Query OK, 1 row affected (0.00 sec) mysql> XA END 'test'; Query OK, 0 rows affected (0.00 sec) mysql> XA PREPARE 'test'; Query OK, 0 rows affected (0.00 sec) mysql> Killed Now start another client session. mysql> XA COMMIT 'test'; ERROR 1397 (XAE04): XAER_NOTA: Unknown XID mysql> XA RECOVER; Empty set (0.00 sec)
Also before 5.7.7, if the XA transaction is in the PREPARED state and the server abnormally exits, the transaction can be restarted after restarting the server - but it has not been replicated. After the server restarts, XA transactions still exist in the PREPARED state, but the content cannot be recorded in the binary log. Therefore, asynchronization of binary logs results in data drift. Therefore, XA cannot be used safely for replication.
ql> CREATE TABLE t1(fld1 INT); Query OK, 0 rows affected (0.01 sec) mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) mysql> XA START 'test'; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t1 VALUES (1); Query OK, 1 row affected (0.00 sec) mysql> XA END 'test'; Query OK, 0 rows affected (0.00 sec) mysql> XA PREPARE 'test'; Query OK, 0 rows affected (0.00 sec) Now kill the server. mysql> XA RECOVER; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 1 Current database: test +----------+--------------+--------------+------+ | formatID | gtrid_length | bqual_length | data | +----------+--------------+--------------+------+ | 1 | 4 | 0 | test | +----------+--------------+--------------+------+ 1 row in set (0.02 sec) mysql> XA COMMIT 'test'; Query OK, 0 rows affected (0.02 sec) mysql> SHOW BINLOG EVENS\G; *************************** 1. row *************************** Log_name: nisha-PORTEGE-Z30-A-bin.000001 Pos: 4 Event_type: Format_desc Server_id: 1 End_log_pos: 120 Info: Server ver: 5.6.29-debug-log, Binlog ver: 4 1 row in set (0.00 sec) mysql> SELECT * FROM t1; +------+ | fld1 | +------+ | 1 | +------+ 1 row in set (0.00 sec)
To overcome these limitations, XA transaction recovery mechanism and binary logging mechanism need to be changed. This improvement was made in 5.7.7.
- XA recovery mechanism has been extended. When the connection terminates, PREPARED XA transactions will remain in the transaction cache and be marked in InnoDB. This allows clients to resume PREPARED XA transactions, followed by COMMIT / ROLLBACK.
- XA transactions now use two different GTIDs for binary recording in two phases, which allow transactions to interleave. In the first stage, when XA PREPARE is published, the transaction at that point is recorded in the binary log and can be identified by XA_prepare_log_event. In the second stage, when XA COMMIT/ROLLBACK is issued, the second part of the transaction is written to the binary log. Because XA PREPARE is persistent, XA transactions do not roll back and remain after server restart or client disconnection. The client can execute XA COMMIT / ROLLBACK and keep the binary log up to date. When the GTID is ON and the binary log is closed, the XA transaction can also work properly.
Let's look at the output of the above example after 5.7.7:
After the client disconnects:
l> CREATE TABLE t1(fld1 INT); Query OK, 0 rows affected (0.01 sec) mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) mysql> XA START 'test'; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t1 VALUES (1); Query OK, 1 row affected (0.00 sec) mysql> XA END 'test'; Query OK, 0 rows affected (0.00 sec) mysql> XA PREPARE 'test'; Query OK, 0 rows affected (0.00 sec) mysql> Killed Now start another client session. mysql> XA RECOVER; +----------+--------------+--------------+------+ | formatID | gtrid_length | bqual_length | data | +----------+--------------+--------------+------+ | 1 | 4 | 0 | test | +----------+--------------+--------------+------+ 1 row in set (0.00 sec) mysql> XA COMMIT 'test'; Query OK, 0 rows affected (0.02 sec)
After the server restarts:
ql> CREATE TABLE t1(fld1 INT);
Query OK, 0 rows affected (0.01 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> XA START 'test';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.00 sec)
mysql> XA END 'test';
Query OK, 0 rows affected (0.00 sec)
mysql> XA PREPARE 'test';
Query OK, 0 rows affected (0.00 sec)
Now kill the server.
mysql> XA RECOVER;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1
Current database: test
+----------+--------------+--------------+------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+------+
| 1 | 4 | 0 | test |
+----------+--------------+--------------+------+
1 row in set (0.02 sec)
mysql> XA COMMIT 'test';
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW BINLOG events\G;
*************************** 3. row ***************************
Log_name: nisha-PORTEGE-Z30-A-bin.000001
Pos: 154
Event_type: Anonymous_Gtid
Server_id: 0
End_log_pos: 219
Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
Log_name: nisha-PORTEGE-Z30-A-bin.000001
Pos: 219
Event_type: Query
Server_id: 0
End_log_pos: 319
Info: XA START X'74657374',X'',1
*************************** 5. row ***************************
Log_name: nisha-PORTEGE-Z30-A-bin.000001
Pos: 319
Event_type: Query
Server_id: 0
End_log_pos: 418
Info: use `test`; INSERT INTO t1 VALUES (1)
*************************** 6. row ***************************
Log_name: nisha-PORTEGE-Z30-A-bin.000001
Pos: 418
Event_type: Query
Server_id: 0
End_log_pos: 509
Info: XA END X'74657374',X'',1
*************************** 7. row ***************************
Log_name: nisha-PORTEGE-Z30-A-bin.000001
Pos: 509
Event_type: XA_prepare
Server_id: 0
End_log_pos: 549
Info: XA PREPARE X'74657374',X'',1
*************************** 8. row ***************************
Log_name: nisha-PORTEGE-Z30-A-bin.000002
Pos: 219
Event_type: Query
Server_id: 0
End_log_pos: 313
Info: XA COMMIT X'74657374',X'',1
8 rows in set (0.00 sec)
conclusion
MySQL 5.7 has greatly improved XA support and made it possible to implement XA JOIN/XA RESUME operations. Expect to see its use in distributed systems.