Introduction
XA stands for “eXtended Architecture”, and is a standard created by The Open Group for distributed transaction processing. While MySQL 5.0 was the first version to support XA, MySQL 5.7 has removed major limitations, fixed a number of bugs, and increased overall test-case coverage.
Background
XA addresses the problem of preserving ACID properties within a single transaction across a distributed set of resources. The resources themselves could be other MySQL servers, or even different database technologies. The standard for XA describes the interaction between the global transaction manager and local resource manager.
As mentioned in the introduction, MySQL 5.0 introduced XA support, thus adding the ability to participate in global transactions. The XA support requires a resource manager which provides access to transactional resources and a transaction manager to coordinate the transactions within the global transaction. The XA implementation of MySQL enables the MySQL server to behave as a resource manager while the client connecting to the MySQL server performs the tasks of a transaction manager.
XA uses two phase commit protocol where the first phase is the request for commit followed by the actual commit. Once the individual branches of the global transaction have completed the execution, the two phase commit protocol kicks in:
- In the first phase, the transaction manager issues prepare for commit message to all the branches involved in the global transaction. Before the resource manager acknowledges that it is ready for commit, it records results of actions in a stable storage to perform the actual commit in the second phase.
- In the second phase, the transaction manager if it receives an affirmative response from all the branches involved, then they are notified to commit. However if any of the branches replies negatively, all branches are notified to rollback.
The transaction manager interacts with multiple resource managers to handle the individual transaction/branches within the global transaction. The diagram depicts an XA transaction involving one resource manager. The statements for XA transaction begin with the XA keyword, action to be performed and an unique identifier. In the below example, the string ‘xatest’ represent the global transaction identifier. In addition to the global transaction identifier, a branch identifier and Format ID can be specified for the XA transaction. The branch identifier is used to identify the local transaction and the format ID specifies the format used by the first two components.
XA START/BEGIN starts a transaction and defines its global transaction identifier.
XA END specifies the end of the active transaction.
XA PREPARE prepares the transaction for commit.
XA COMMIT [ONE PHASE] commits and terminates the transaction which was PREPARED.
In case of the ONE PHASE option, prepare and commit is performed in a single step ending transaction.
XA ROLLBACK rolls back and terminates the transaction.
XA RECOVER displays information about all the PREPARED transactions.
Let us look at the state transition for the above XA transaction.
XA START puts the transaction in an ACTIVE state. Once all the statement are executed by the active transaction, an XA_END statement is issued which puts the transaction in an IDLE state. For an IDLE transaction, either XA PREPARE or a XA COMMIT ONE PHASE can be issued. XA PREPARE puts the transaction in a PREPARED state. However XA COMMIT ONE PHASE prepares and commits the transaction. For a PREPARED XA transaction, XA COMMIT is issued to commit which ends the transaction.
Major Limitations are now lifted.
Prior to 5.7.7, a PREPARED transaction was rolled back if the client connection was terminated or when the server exited gracefully. When the client was killed, all the transactions were rolled back. Thus even though the XA transaction was in a PREPARED state, it could not recover the transaction during XA RECOVER. Ideally when a transaction is PREPARED, it should be possible to COMMIT or ROLLBACK the transaction. For this case, let us look at the example which was reported in the bug 12161.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
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 prior to 5.7.7, if a XA transaction was in a PREPARED state and the server exited abnormally, the transaction could be resumed after restart of the server – but it was not replicated. An XA transaction would still exist in the PREPARED state after server restart but the contents could not be logged in the binary log. Thus the binary log went out of sync causing data drift. Hence XA could not be used safely with replication.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
|
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) 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) |
Overcoming the above mentioned restrictions required changes in the XA transaction recovery mechanism and binary logging mechanism. This improvement was made in 5.7.7 through the implementation of work log number 7193 and 6860/ bug 12161.
- The XA recovery mechansim has been extended such that when a connection is terminated, the PREPARED XA transactions are left in the transaction cache and marked specially in InnoDB. This allows the client to RECOVER the PREPARED XA transactions and then COMMIT/ ROLLBACK.
- The XA transactions are now binlogged in two phases using two different GTIDs which allows the transactions to be interleaved. During the first phase, when XA PREPARE is issued, the transaction up until that point is logged in the binary log and can be identified by
XA_prepare_log_event.During the second phase, when XA COMMIT/ROLLBACK is issued, the second part of the transaction is written into the binary log. Since XA PREPARE is persistent, the XA transaction is not rolled back and survives the server restart or client disconnect. The client can perform XA COMMIT/ROLLBACK and the binary log remains up to date. XA transactions also works well when GTID is ON and binary log is turned OFF.
Let us look at the output of the above examples after 5.7.7:
After client disconnects:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
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 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 server restart:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
|
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) 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) |
Kudos to Andrei Elkin who implemented worklog 6860. Also thanks to Guilhem Bichot, Marko Mäkelä, Luís Soares and the replication team members for their contribution to this worklog through ideas, discussions and reviews. Kudos to Dmitry Shulga for implementing the worklogs 7155 and 7193 which also supplemented the work for 6860.
Bugs Fixed and Improvements to Testcases
Here is the list of some of the bugs fixed in 5.7 to improve the overall XA support:
BUG #12161. XA recovery and client disconnection/BUG #75204: MYSQL/INNODB FORGETS PREPARED XA TRANSACTION ON RESTART. Both were fixed by the ‘support for replication’ as mentioned above.
BUG #78695: Fixed the assert triggered while a transaction is in the prepared state and the value of the internal server variable pseudo_slave_mode is modified.
BUG #78263: Fixed the assert triggered during XA COMMIT since the internal transaction state was not reset between XA PREPARE and XA COMMIT/XA ROLLBACK as they constitute separate transactions under XA.
BUG #77392: Fixed the issue where the slave group event parser did not recognize XA ROLLBACK as a boundary.
BUG #76734: Fixed the assertion triggered during XA ROLLBACK for a read-only PREPARED transaction when the transaction body is empty and binlog enabled.
BUG #71352: Fixed the issue where an XA START after server restart with the existing xid for a PREPARED XA transaction failed to commit during XA COMMIT.
BUG #47134: Fixed the issue where the server would exit if XA support was activated by multiple storage engines.
As can be seen bug Bug#79940 and Bug#76233 the XA support is not perfect yet, but we hope to get there!
The test suite has also been expanded to uncover any potentials problems and test the XA support. The test suites and its test scripts added/modified is listed below:
binlog_tests: binlog_xa_prepared.test, gtid_next_xa.test.
binglog: binlog_gtid_next_xa.test, binlog_xa_prepared_disconnect.test, binlog_xa_trx_gtid_response_packet.test, binlog_xa_handling.test
rpl: rpl_gtids_table_disable_binlog_on_slave.test, rpl_trx_boundary_parser_warning.test, rpl_xa_survive_crash_debug.test, rpl_xa_survive_disconnect.test, rpl_xa_survive_disconnect_mixed_engines.test, rpl_trx_boundary_parser_warning.test
sys_vars: innodb_support_xa_func.test
mysql-test: xa.test, xa_prepared_binlog_off.test
Further Reading
More information about the XA standard is published by the open group and can be found here. Also the MySQL documentation is good source for information about the XA support provided by MySQL.
Conclusion
The changes introduced in MySQL 5.7 improve support for XA dramatically and makes it possible to implement XA JOIN / XA RESUME operations. We would be happy to hear from the community about the importance of extending MySQL to support these XA operations. Feel free to leave a comment here. Meanwhile we look forward to seeing its usage in distributed systems.
As always, THANK YOU for using MySQL!