Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ MySQL 5.7 perfectly distributed transaction support     - Start Linux ISO image directly from the hard disk (Linux)

- Hadoop safe hftp (Server)

- Spring next ActiveMQ combat (Programming)

- Spring loaded container finishes executing a method (Programming)

- Zabbix installation and configuration process (Server)

- MySQL Slave synchronization problem solving (Database)

- Linux shared libraries .so file name and Dynamic Link (Linux)

- Cryptography development environment to build under Ubuntu (Linux)

- Type Linux commands (Linux)

- Distributed File System using MogileFS (Linux)

- Linux and Windows Security Topics (Linux)

- Linux System Getting Started Learning: Debian download, installation and graphical interface (Linux)

- Kali Linux 2.0 U disk installation errors Your installation cd-rom could not be mounted (Linux)

- Copy Recovery using RMAN repository development environment (Database)

- Vim useful plugin: EasyGrep (Linux)

- Java and C / C ++ data conversion when network communication (Programming)

- Android webView URL redirects affect goBack () (Programming)

- Java 8 Lambda principle analysis (Programming)

- Linux-du and df command (Linux)

- Oracle DATABASE LINK (DBLINK) Create (Database)

  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.
- MongoDB relations, references, index query coverage (Database)
- LogStash log analysis display system (Linux)
- How to modify the Sublime in Tab four spaces (Linux)
- Ubuntu Backup and Recovery (Linux)
- Vi / Vim prompt solutions do not have permission to save time (Linux)
- Oracle Shared Server Configuration (Database)
- Hadoop connection failed or stuck processing (Server)
- CentOS source installation GitLab Chinese Version (Server)
- Git uses a basic tutorial (Linux)
- Python objects (Programming)
- Compile and install Redis and register as a system service under RedHat5.8 environment (Database)
- OpenCV cvFindCornerSubPix () to find sub-pixel Corner (Programming)
- using the ssh command to check the socket / Network Connections (Linux)
- DNF Command Tutorial (Linux)
- DupeGuru- find and remove duplicate files (Linux)
- QEMU image file used: qcow2 and raw (Linux)
- Java development environment to build under Ubuntu (Linux)
- Oracle first Automated Installation Packages (Database)
- Editor of the popular Linux Gvim (Linux)
- Puppet subcommands Introduction (Server)
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.