Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ MySQL 5.7 perfectly distributed transaction support     - Zabbix API and PHP configuration (Server)

- Based on Python: OpenCV simple image manipulation (Programming)

- Linux compression and decompression command (Linux)

- Subquery Oracle study notes (Database)

- Amazon EC2 server using Rsync + Inotify real-time synchronization (Server)

- CentOS 6.4 (64bit) install Python 2.7.5 (Linux)

- Configuring DNS process under CentOS 6.5 (Server)

- Linux common network tools: traceroute routing of scanned (Linux)

- Node.js developers must know four JavaScript concepts (Programming)

- Linux System Getting Started Learning: In RedHat Linux driver compiled Ixgbe (Linux)

- OpenSUSE / Linux network configuration (Linux)

- Some MySQL interview questions (Database)

- Linux system last command usage (Linux)

- To batch create users under Linux (Linux)

- Installation under Linux to deploy Java (Linux)

- Linux system font color change (Linux)

- Android Studio simple setup (Linux)

- Axel install plug under CentOS 5/6 acceleration yum downloads (Linux)

- Linux operating system buffer overflow attacks Countermeasures (Linux)

- Use Docker / LXC quickly launch a desktop system (Linux)

  MySQL 5.7 perfectly distributed transaction support
  Add Date : 2018-11-21      
  Two Phase Commit Protocol

Distributed Transaction usually 2PC protocol, full name of Two Phase Commitment Protocol. The agreement mainly to solve the distributed database scenario, data consistency across all nodes of the problem. In a distributed transaction environment, the transaction commit will become relatively more complicated, because there are a plurality of nodes, there may be cases of failure to submit some of the nodes, namely the need to ensure ACID properties of the transaction in each database instance. All in all, when distributed, provided they occur submit one node fails, all nodes can not be submitted, only when all nodes are able to submit the entire distributed transaction is allowed to be submitted.

Distributed Transaction by 2PC protocol will be submitted in two phases

commit / rollback
The first stage is to prepare each node asks whether a transaction can be submitted only when the situation gets all nodes "permission", the second stage in order to commit, otherwise it rollback. Note: prepare a successful business, they must all be committed.

MySQL Distributed Transaction

All along, MySQL database support distributed transactions, but can only be said to be limited support, in particular in:

Already prepare the transaction, the client exits or service down time, 2PC transaction is rolled back
After the restart the server failure to submit the corresponding Binlog be lost
The above-mentioned problems in the MySQL database for several decades, until the MySQL-5.7.7 version, the official only fixes the problem. Although InnoSQL already in the 5.5 version of this fix, but the contrast of the official rehabilitation program, we really do not so elegant. The following will explain the details of specific performance and official fix this problem, here are the use of the official MySQL-5.6.27 version (not repair) and MySQL-5.7.9 version (Fixed) for verification.

First look at the problems, we first create a table as follows:

create table t (
    id int auto_increment primary key,
    a int
) Engine = innodb;

For the above table, the following data is inserted by:

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

Through the above operation, the user creates a distributed transaction, and prepare not return an error, indicating that the distributed transaction can be committed. XA RECOVER command to view the show the following results:

mysql> XA RECOVER;
+ ---------- + -------------- + -------------- + -------- - +
| FormatID | gtrid_length | bqual_length | data |
+ ---------- + -------------- + -------------- + -------- - +
| 1 | 7 | 0 | mysql56 |
+ ---------- + -------------- + -------------- + -------- - +

If this time the user exits the client after reconnection, the command will find 2PC transaction xa recover just created gone. Prepare a successful business that is lost, do not meet the 2PC protocol specifications! ! !

The main reason is that these problems: MySQL-5.6 version of the client exits, automatically already prepare the transaction is rolled back, then MySQL Why do this? It depends on the realization of the internal MySQL, MySQL-5.7 the previous version, to prepare the transaction, MySQL is not recorded binlog (the official said that the reduction fsync, optimization plays a role). Only when the distributed transaction will be submitted to the previous operation is written binlog information, so for binlog, the distributed transactions with the ordinary affairs is no different, and operating information are stored in previously prepare IO_CACHE connection, if this time the client exits, the previous binlog information will be lost again after reconnection allowed to submit it, will cause binlog lost, resulting in a master-slave data inconsistencies, the official client exits directly to already prepare transaction We are rolled back!

Official practice, seemingly doing very beautiful, a little sacrifice standardized things, at least to ensure the consistency of data from the master. But it is not, if the user has after prepare before the client exits, MySQL downtime occurs, what will happen this time?

Down, down before the operation of the transaction does not disconnect the MySQL connection in a distributed transaction after successfully prepare, this time already prepare the transaction will not be rolled back, so after restarting the MySQL engine layer by recover mechanisms to restore the transaction. Of course Binlog the transaction has been lost in downtime during this time, if you go to submit will result from inconsistent master data that the authors did not record Binlog, from which data is lost. So in this case, the official is generally recommended direct transaction rollback already prepare.

These are MySQL-5.7 in the previous version of MySQL problems on distributed transaction, then MySQL-5.7 version of the official what had been done to improve? This can be obtained from the official WL # 6860 description of some of the information, we do not practice in a manner no right to speak, from the specific method of operation improvement under MySQL-5.7 up analysis of:

The above table or in the same structure of the same operation as follows:

mysql> XA START 'mysql57';
mysql> INSERT INTO t VALUES (1,1);
mysql> XA END 'mysql57';
mysql> XA PREPARE 'mysql57'

This time, we have to see the Binlog under the Master through mysqlbinlog

Compare also under Slave Relay log on

By the above operation, obviously found after prepare, from XA START to operate between XA PREPARE are recorded to the Master of Binlog, and then spread through the replication relationship on Slave. That began to MySQL-5.7, MySQL for distributed transactions, prepare in time to complete the write operation Binlog, called XA_prepare_log_event by new types of event to achieve, which is the main difference with the previous version (the previous version prepare without writing Binlog)

Of course, this alone is not enough, because we know that by Slave SQL thread to playback Relay log information due to prepare the transaction can block the entire session, and playback SQL thread is only one (without considering parallel playback), the SQL thread will not because of being blocked prepare phase of a distributed transaction, causing the entire SQL thread playback problems? This is exactly the second issue official to be addressed: how to make SQL thread when playback distributed transaction to prepare the stage, do not block behind event playback? In fact, this is also very simple to achieve (in xa.cc::applier_reset_xa_trans), as long as the SQL thread playback time to prepare, can be treated similar to the client disconnected (the cache associated with the connection handle from the SQL thread) . Finally, on the Slave server, the user can be found by XA RECOVER command the following information:

mysql> XA RECOVER;
+ ---------- + -------------- + -------------- + -------- - +
| FormatID | gtrid_length | bqual_length | data |
+ ---------- + -------------- + -------------- + -------- - +
| 1 | 7 | 0 | mysql57 |
+ ---------- + -------------- + -------------- + -------- - +

As for the above transaction when submitted, generally wait until the XA COMMIT 'mysql57' after the Master, the slave will also be submitted.

to sum up

In summary, MySQL 5.7 support for distributed transactions to be perfect, and a decades-long bug has been fixed, and thus one more reason to upgrade to MySQL-5.7 version.
- DRBD daily management (Server)
- CentOS use wget (Linux)
- Linux Command - ps: a snapshot of the current process (Linux)
- ORA-08102 errors (Database)
- Java is simple to read and write HDFS Demo (Programming)
- OpenWRT environment to build (Linux)
- Java Collections Framework interfaces map (Programming)
- Linux network security backdoor technology and advanced skill practice (Linux)
- PostgreSQL vacuum principle of a function and parameters (Database)
- Java call by value and by reference (Programming)
- Oracle to read and modify the data block process (Database)
- Oracle 12C with multi-column index (Database)
- DataGuard the MRP can not start to analyze and solve problems (Database)
- Docker command Detailed (Linux)
- Dalvik heap memory management and recycling (Linux)
- Oracle partition table data migration, process management automation (Database)
- Installation Sublime Text 3 (Build 3065) text editor in Ubuntu (Linux)
- Let Linux operating system more secure (Linux)
- Android Application Development: an argument between Activity (Programming)
- Depth understanding of JavaScript new mechanism (Programming)
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.