Shulou(Shulou.com)06/01 Report--

In this issue, the editor will bring you about what the XA protocol in MYSQL distribution refers to. The article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

Some people think that MYSQL distributed middleware is not that complicated, just a software that sends data by setting partition keys. In fact, hehe

First of all, what is the XA protocol? XA is a two-phase commit protocol supported by many databases and transaction monitors to ensure data integrity by coordinating access to a single transaction in multiple relational databases. XA guarantees that transaction updates are committed in all participating databases or rolled back completely from all databases to the state they were in before the transaction started.

In order to participate in XA transactions, XA resources must let the transaction manager know about itself. Once the XA resource is enlisted, the transaction manager ensures that the XA resource participates in the transaction and makes appropriate method calls to the XA resource during the lifetime of the transaction. To complete the XA transaction, all resource managers participate in a two-phase commit (2pc). A commit in a XA transaction is called a two-phase commit because there are two deliveries during the commit process.

In the first round, the transaction manager asks each resource manager if there are any problems when committing the transaction. If any resource manager objects to committing a transaction, all work done by either party on any resource involved in the XA transaction must be rolled back.

The transaction manager calls the rollback () method for each enlisted XA resource. If no resource manager objects to commit, the second round will involve the transaction manager actually calling commit () for each enlisted XA resource. This process guarantees the ACID (atomicity, consistency, isolation, and persistence) properties of transactions that can span multiple resources.

Come here, you have been talking for a long time, what is the relationship between these? the middleware of DBLE distributed intermediate database is done by using MYSQL's general XA protocol. If you don't know DBLE, please have a look at Baidu.

Therefore, XA protocol is a very important protocol for sub-database and sub-table.

Here are some steps for the XA protocol

1 XA START

2 Some SQL statement

3 XA END

4 XA Prepare

5 XA Commit or Rollback

Let's take a look.

For example, if one of the three physical data databases restarts when the middleware releases the data, what if it is in MYSQL version 5.6, it is very likely that the data will be lost? if we don't understand the above, let's get this straight.

The middleware has sent xa prepare 'xid1' statements to sub-library 1, sub-library 2 and sub-library 3, and received a successful reply.

The middleware sent the'xa commit 'xid1' statement to the sublibrary 1 and 3 and successfully returned

When the middleware sends'xa commit 'xid1' to sublibrary 2, the network is disconnected, or the database instance of sublibrary 2 is kill.

When the network is restored (when the relevant Session has exited) or after the database instance is restarted (or switched to the standby database), the transaction that has been XA prepare has been rolled back. When the middleware XA commit 'xid1' is sent, the database instance cannot find the xa transaction of xid1 at all.

The above process leads to the inconsistency of distributed transactions: sub-library 1: 3 commits the transaction, sub-library 2 rolls back the transaction, and the whole transaction is half committed and half rolled back.

So students using MYSQL5.6 you should note that there is no strict persistence of xa prepare in MYSQL5.6, when Session is disconnected, database CRASH and other cases these transactions will be rolled back, and a master database equipped with SemiSync transactions will not be sent to the SLAVE library, when the master database switches to standby database, these transactions will be lost.

So the safe use of middleware must be if you use MYSQL.5.7, distributed MYSQL can have a security guarantee.

Let's do an experiment.

Then the machine suddenly crashed. If it is a general matter, it must have disappeared. The following picture shows that the machine has been rebooted and the data has not been inserted.

And then we're in commit.

The data will be back. In this way, distributed middleware can ensure that after a problem occurs in a physical library, the data can be recovered after it becomes normal.

XA RECOVER

Xa recover; is a transaction that can be seen in prepare state, as shown in the figure above

At the same time, there are related XA records in BINLOG, so even switching will not lead to the loss of distributed transactions.

So, to say that distributed middleware is a saying that anyone can do data distribution through data fragmentation keys, I can only express hehe.

This is what the XA protocol in MYSQL distribution refers to. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are 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.