Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ MySQL 5.7 perfectly distributed transaction support     - Create Your Own Docker base image in two ways (Linux)

- Android View event delivery (Programming)

- Git Experience Sharing - Using a remote repository (Linux)

- OpenGL Superb Learning Notes - GLSL language foundation (Programming)

- Ubuntu How to install screen recording tool Simple Screen Recorder 0.3.1 (Linux)

- MySQL log from the library than the main library (Database)

- Stucts2 values on the page and how the attribute values corresponding to the Action (Programming)

- Linux operating system set up to effectively prevent ARP attacks (Linux)

- How to migrate MySQL to MariaDB under linux (Database)

- Linux Network Programming - non-blocking program (Programming)

- SYN attack hacker attack and defense of the basic principles and prevention technology (Linux)

- RHEL6.4 one key installation Redmine (Linux)

- Spring3 + SpringMVC + Hibernate4 full annotation environment configuration (Server)

- Two programs HAProxy + Heartbeat (note, the secondary server vip, normal HAProxy boot method) (Server)

- Safety testing Unix and Linux server entry succinctly (Linux)

- Actual SSH port forwarding (Linux)

- Android in the coordinate system and a method to obtain the coordinates (Programming)

- Relationship between Linux permissions with the command (Linux)

- CentOS install expect (Linux)

- CentOS 6.5 install Firefox (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.
- Availability Hadoop platform - Oozie Workflow (Server)
- Nginx configuration support f4v video format player (Server)
- Editor of the popular Linux Gvim (Linux)
- LaTeX Getting Started Tutorial (Linux)
- Source install Python3.4 on CentOS (Linux)
- Java string concatenation techniques (StringBuilder tips) (Programming)
- installation and configuration of the PHP environment (Apache2) under Linux (Server)
- UUID in Java (Programming)
- Elixir: the future of programming languages (Programming)
- Let the terminal under Mac OS X as like Linux has displayed a variety of colors (Linux)
- In addition to wget and curl, what better alternatives (Linux)
- The formatted Linux hard drive and mount (Linux)
- Ubuntu 32 compile Android 4.0.4 Problems (Linux)
- Spring REST Exception Handling (Programming)
- Ubuntu 12.04 installation NVIDIA GTX750 graphics driver (Linux)
- CentOS7 installation GAMIT and GMT (Linux)
- Zabbix monitoring different versions of RAID installation and monitoring and MySQL master-slave monitor (Server)
- ogg Oracle to SQL Server 2005 to achieve synchronization (Database)
- Apache site default home page settings (Server)
- To compiler and install MariaDB-10.0.20 under CentOS 6.6 (Database)
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.