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

This article focuses on "MySQL 5.7 distributed transaction support example analysis", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the example analysis of MySQL 5.7distributed transaction support.

Distributed transactions usually use 2PC protocol, the full name is Two Phase Commitment Protocol. The main purpose of this protocol is to solve the problem of data consistency among all nodes in the distributed database scenario. In the distributed transaction environment, the transaction commit will become relatively complex, because of the existence of multiple nodes, there may be some node commit failure, that is, the ACID characteristics of the transaction need to be guaranteed in each database instance. In a word, in distributed commit, as long as one node fails to commit, all nodes cannot commit, and only when all nodes can commit, the whole distributed transaction is allowed to commit.

The distributed transaction divides the commit into two phases through the 2PC protocol

Prepare

Commit/rollback

The first phase of prepare is only used to ask whether each node transaction can be committed, and the second phase of commit can only be carried out with the "permission" of all nodes, otherwise it will be rollback. It is important to note that all transactions that are successful in prepare must be committed.

MySQL distributed transaction

For a long time, MySQL databases support distributed transactions, but only limited, as shown in:

For transactions that have been prepare, 2PC transactions will be rolled back when the client exits or the service goes down.

After the server failure restart submission, the corresponding Binlog is lost

The problem existed in the MySQL database for decades and was not officially fixed until the MySQL-5.7.7 version. Although InnoSQL has already been fixed in version 5.5, we are really not that elegant compared to the official fix. The specific performance of the problem and the official fix method are described in detail below, which are verified by the official MySQL-5.6.27 version (not fixed) and MySQL-5.7.9 version (fixed) respectively.

Let's take a look at the existing problems. Let's first create a table as follows:

Create table t (id int auto_increment primary key, an int) engine=innodb

For the above table, insert data by doing the following:

Mysql > XA START 'mysql56';mysql > INSERT INTO t VALUES (1); mysql > XA END' mysql56';mysql > XA PREPARE 'mysql56'

With the above operation, the user creates a distributed transaction, and prepare does not return an error, indicating that the distributed transaction can be committed. View through the command XA RECOVER and display the following results:

Mysql > XA RECOVER +-+ | formatID | gtrid_length | bqual_length | data | +-+ | 1 | | 7 | 0 | mysql56 | +-+ |

If the user exits the client and reconnects at this time, you will find that the 2PC transaction you just created is missing through the command xa recover. That is, the successful transaction of prepare is lost and does not conform to the 2PC protocol specification!

The main reason for the above problems is that when the client exits, the MySQL-5.6 version automatically rolls back the transactions that are already prepare, so why does MySQL do this? This mainly depends on the internal implementation of MySQL, previous versions of MySQL-5.7, MySQL does not record binlog for prepare transactions (officially, it reduces fsync, which plays an optimization role). Only when the distributed transaction commits will the previous operation be written into the binlog information, so for binlog, the distributed transaction is no different from the ordinary transaction, while the previous operation information of prepare is saved in the connected IO_CACHE. If the client exits at this time, the previous binlog information will be lost, and if it is allowed to be committed after reconnection, the Binlog will be lost, resulting in inconsistency between master and slave data. So when the client exits, the official rolls back all the transactions that have already prepare!

The official approach seems to have done a good job, sacrificing a little bit of standardization, at least ensuring the consistency of master-slave data. But in fact, this is not the case. What happens if the MySQL goes down after the user has prepare and before the client exits?

MySQL goes down after a successful prepare of a distributed transaction, and the connection to operate the transaction is not disconnected before the downtime. At this time, the transaction that is already prepare will not be rolled back, so after the MySQL is restarted, the engine layer can resume the transaction through the recover mechanism. Of course, the Binlog of the transaction has been lost during the downtime. At this time, if you commit, it will cause inconsistency between master and slave data, that is, the Binlog is not recorded in the commit, and the data is lost from the. So in this case, it is generally recommended to roll back transactions that are already prepare.

These are the various problems with distributed transactions in the previous version of MySQL-5.7, MySQL, so what official improvements have been made in the MySQL-5.7 version? We can get some information from the official WL#6860 description. With the attitude that there is no practice, there is no right to speak, we will analyze the improvement methods of MySQL-5.7 from the specific operation:

Do the same with the same table structure above as follows:

Mysql > XA START 'mysql57';mysql > INSERT INTO t VALUES (1Power1); mysql > XA END' mysql57';mysql > XA PREPARE 'mysql57' through the above operations, it is obvious that after prepare, the operations from XA START to XA PREPARE are recorded in the Binlog of Master, and then transmitted to Slave through the replication relationship. That is to say, at the beginning of MySQL-5.7, MySQL completes the operation of writing Binlog for distributed transactions at the time of prepare, by adding a new method called

Of course, this alone is not enough, because we know that Slave plays back and forth Relay log information through SQL thread. Because the transaction of prepare can block the entire session, and there is only one SQL thread for playback (without considering parallel playback), will SQL thread cause problems with the entire SQL thread playback because it is blocked by the prepare phase of distributed transactions? This is the second problem that officials have to solve: how can SQL thread not block subsequent event playback when it is played back to the prepare phase of a distributed transaction? In fact, this implementation is also very simple (in xa.cc::applier_reset_xa_trans), as long as the SQL thread is played back to prepare, it is similar to the disconnection of the client (detaching the connection handle of the relevant cache from the SQL thread). Finally, on the Slave server, the user can find the following information through the command XA RECOVER:

Mysql > XA RECOVER +-+ | formatID | gtrid_length | bqual_length | data | +-+ | 1 | | 7 | 0 | mysql57 | +-+ |

As for when the above transaction will be committed, usually after XA COMMIT 'mysql57' is performed on Master, it will also be committed on slave.

At this point, I believe you have a deeper understanding of "MySQL 5.7distributed transaction support example Analysis". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.