Shulou(Shulou.com)05/31 Report--

This article mainly shows you "what are the improvements to XA support in MySQL 5.7.It is easy to understand and well organized. I hope it can help you solve your doubts. Let the editor lead you to study and learn what improvements to XA support in MySQL 5.7.What are the improvements in XA support?"

Background

XA solves the problem of preserving ACID attributes in a single transaction across distributed resources. The resources themselves can be other MySQL servers or even different database technologies. The XA standard describes the interaction between global transaction managers and local resource managers.

As mentioned in the introduction, MySQL 5. 0 introduces XA support, which increases the ability to participate in global transactions. XA supports resource managers that can access transaction resources and transaction managers that can coordinate transactions in global transactions. MySQL's XA implements the task of letting the MySQL server act as the resource manager, while the client connected to the MySQL server performs the task of the transaction manager.

XA uses a two-phase commit protocol, in which the first phase is to issue an commit request followed by the actual commit. After each branch of the global transaction completes execution, the two-phase commit protocol is started:

In the first phase, the transaction manager issues a prepare commit message to all branches involved in the global transaction. Before the resource manager confirms that it is ready to commit, it records and saves the results of the operation in preparation for the actual commit in the second phase.

In the second phase, if the transaction manager receives a definite response from all the branches involved, it notifies them to commit. However, if the answer to either branch is no, all branches are notified to perform a rollback.

A transaction manager interacts with multiple resource managers to handle a single transaction / branch in a global transaction. The following figure depicts a XA transaction involving a resource manager. The statement for a XA transaction begins with the XA keyword, the action to be performed, and the unique identifier. In the following example, the string "xatest" represents the global transaction identifier. In addition to the global transaction identifier, you can also specify the branch identifier and format ID for XA transactions. 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 the transaction and defines its global transaction identifier.

XA END specifies the end of the active transaction.

XA PREPARE prepares the COMMIT for the transaction.

XA COMMIT [ONE PHASE] COMMIT and end a transaction that has been PREPARE.

If you use the single phase option, preparation and commit are performed in a single step that ends the transaction.

XA ROLLBACK rolls back and terminates the transaction.

XA RECOVER displays information about all PREPARED transactions.

Let's look at the transitions between the states of the above XA transactions.

XA START makes the transaction active. Once all statements are executed by the active transaction, the XA_END statement is issued to put the transaction in the IDLE state. For idle transactions, you can issue XA PREPARE or XA COMMIT ONE PHASE. XA PREPARE puts the transaction in the PREPARED state. However, XA COMMIT ONE PHASE prepares and commits the transaction. For PREPARED XA transactions, an XA COMMIT commit is issued to end the transaction.

The main problem has been resolved.

Before 5.7.7, if the client connection terminates or the server exits normally, the PREPARED transaction is rolled back. When the client is killed, all transactions are rolled back. Therefore, even if the XA transaction is in the PREPARED state, it cannot resume the transaction during the XA RECOVER. Ideally, when preparing a transaction, you should be able to commit or roll back the transaction. For this case, let's take a look at the example reported in error 12161.

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

Mysql > CREATETABLEt1 (fld1INT)

QueryOK, 0 rowsaffected (0.01 sec)

Mysql > COMMIT

QueryOK, 0 rowsaffected (0.00 sec)

Mysql > XA 'test'

QueryOK, 0 rowsaffected (0.00 sec)

Mysql > INSERTINTOt1VALUES (1)

QueryOK, 1 rowaffected (0.00 sec)

Mysql > XAEND 'test'

QueryOK, 0 rowsaffected (0.00 sec)

Mysql > XA 'test'

QueryOK, 0 rowsaffected (0.00 sec)

Mysql > Killed

Nowstartanotherclientsession.

Mysql > XA 'test'

1397 (XAE04): XAER_NOTA: UnknownXID

Mysql > XARECOVER

Emptyset (0.00 sec)

Also prior to 5.7.7, if the XA transaction was in the PREPARED state and the server exited abnormally, the transaction could be resumed after the server was restarted-but the transaction was not replicated. After the server restarts, the XA transaction will still exist in the PREPARED state, but its contents cannot be recorded in the binary log. As a result, the binary logs are out of sync, resulting in data drift. Therefore, XA cannot be safely used for replication.

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

thirty

thirty-one

thirty-two

thirty-three

thirty-four

thirty-five

thirty-six

thirty-seven

thirty-eight

thirty-nine

forty

forty-one

forty-two

forty-three

forty-four

forty-five

forty-six

forty-seven

forty-eight

forty-nine

fifty

fifty-one

fifty-two

fifty-three

fifty-four

Mysql > CREATETABLEt1 (fld1INT)

QueryOK, 0 rowsaffected (0.01 sec)

Mysql > COMMIT

QueryOK, 0 rowsaffected (0.00 sec)

Mysql > XA 'test'

QueryOK, 0 rowsaffected (0.00 sec)

Mysql > INSERTINTOt1VALUES (1)

QueryOK, 1 rowaffected (0.00 sec)

Mysql > XAEND 'test'

QueryOK, 0 rowsaffected (0.00 sec)

Mysql > XA 'test'

QueryOK, 0 rowsaffected (0.00 sec)

Nowkilltheserver.

Mysql > XARECOVER

2006 (HY000): MySQLserverhasgoneaway

Noconnection. Tryingto reconnect...

Connectionid: 1

Currentdatabase: test

+-+

| | formatID | gtrid_length | bqual_length | data | |

+-+

| | 1 | 4 | 0 | test |

+-+

1 rowin set (0.02 sec)

Mysql > XA 'test'

QueryOK, 0 rowsaffected (0.02 sec)

Mysql > SHOWBINLOGEVENS\ 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: Serverver: 5.6.29-debug-log, Binlogver: 4

1 rowin set (0.00 sec)

Mysql > SELECT * FROMt1

+-+

| | fld1 |

+-+

| | 1 |

+-+

1 rowin 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:

To overcome the above limitations, changes need to be made to the XA transaction recovery mechanism and the binary logging mechanism. Improvements were made in 5.7.7 by executing work log numbers 7193 and 6860 / bug 12161.

The XA recovery mechanism has been extended to keep PREPARED XA transactions in the transaction cache and specially marked in InnoDB when the connection is terminated. This allows the client to resume the PREPARED XA transaction and then execute COMMIT / ROLLBACK.

Now, you can use two different GTID to binary record XA transactions in two phases, so that transactions can be interlaced. In the first phase, when XA PREPARE is issued, transactions up to that point are recorded in the binary log and can be identified in the following ways: XA_prepare_log_event. In the second phase, 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 are not rolled back and can continue to exist after the server restarts or the client disconnects. The client can execute XA COMMIT / ROLLBACK, and the binary log is kept up to date. XA transactions also work well when GTID is set to ON and binary logs are set to OFF.

Let's look at the output of the above example after 5.7.7:

After the client is disconnected:

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

thirty

thirty-one

thirty-two

Mysql > CREATETABLEt1 (fld1INT)

QueryOK, 0 rowsaffected (0.01 sec)

Mysql > COMMIT

QueryOK, 0 rowsaffected (0.00 sec)

Mysql > XA 'test'

QueryOK, 0 rowsaffected (0.00 sec)

Mysql > INSERTINTOt1VALUES (1)

QueryOK, 1 rowaffected (0.00 sec)

Mysql > XAEND 'test'

QueryOK, 0 rowsaffected (0.00 sec)

Mysql > XA 'test'

QueryOK, 0 rowsaffected (0.00 sec)

Mysql > Killed

Nowstartanotherclientsession.

Mysql > XARECOVER

+-+

| | formatID | gtrid_length | bqual_length | data | |

+-+

| | 1 | 4 | 0 | test |

+-+

1 rowin set (0.00 sec)

Mysql > XA 'test'

QueryOK, 0 rowsaffected (0.02 sec)

After the server restarts:

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

thirty

thirty-one

thirty-two

thirty-three

thirty-four

thirty-five

thirty-six

thirty-seven

thirty-eight

thirty-nine

forty

forty-one

forty-two

forty-three

forty-four

forty-five

forty-six

forty-seven

forty-eight

forty-nine

fifty

fifty-one

fifty-two

fifty-three

fifty-four

fifty-five

fifty-six

fifty-seven

fifty-eight

fifty-nine

sixty

sixty-one

sixty-two

sixty-three

sixty-four

sixty-five

sixty-six

sixty-seven

sixty-eight

sixty-nine

seventy

seventy-one

seventy-two

seventy-three

seventy-four

seventy-five

seventy-six

seventy-seven

seventy-eight

seventy-nine

eighty

eighty-one

Mysql > CREATETABLEt1 (fld1INT)

QueryOK, 0 rowsaffected (0.01 sec)

Mysql > COMMIT

QueryOK, 0 rowsaffected (0.00 sec)

Mysql > XA 'test'

QueryOK, 0 rowsaffected (0.00 sec)

Mysql > INSERTINTOt1VALUES (1)

QueryOK, 1 rowaffected (0.00 sec)

Mysql > XAEND 'test'

QueryOK, 0 rowsaffected (0.00 sec)

Mysql > XA 'test'

QueryOK, 0 rowsaffected (0.00 sec)

Nowkilltheserver.

Mysql > XARECOVER

2006 (HY000): MySQLserverhasgoneaway

Noconnection. Tryingto reconnect...

Connectionid: 1

Currentdatabase: test

+-+

| | formatID | gtrid_length | bqual_length | data | |

+-+

| | 1 | 4 | 0 | test |

+-+

1 rowin set (0.02 sec)

Mysql > XA 'test'

QueryOK, 0 rowsaffected (0.02 sec)

Mysql > SHOWBINLOGevents\ 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: @ @ 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 '74657374

* * 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`; INSERTINTOt1VALUES (1)

* 6. Row * *

Log_name: nisha-PORTEGE-Z30-A-bin.000001

Pos: 418

Event_type: Query

Server_id: 0

End_log_pos: 509

Info: XAEND '74657374

* 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 '74657374

* * 8. Row *

Log_name: nisha-PORTEGE-Z30-A-bin.000002

Pos: 219

Event_type: Server_id: 0

End_log_pos: 313

Info: XA '74657374

8 rowsin set (0.00 sec)

These are all the contents of the article "what are the improvements to XA support in MySQL 5.7.Thank you for reading!" I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.