Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ MySQL 5.7 perfectly distributed transaction support     - Linux shell string interception and stitching (Linux)

- Hadoop 2.7.1 Installation and Configuration under RedHat Linux 6.5 (Server)

- Why not use the ifconfig command under RedHat Linux 5 (Linux)

- Linux desktop system using the remote server in clear text ssh password (Server)

- 22 Port weak passwords and SSH connection program of the Linux server (Linux)

- Hadoop 2.2.0 installation development environment (standalone pseudo-distributed mode) (Server)

- Use window.name + iframe cross-domain access to data Detailed (Programming)

- Ubuntu method for single-card dual-IP (Linux)

- Hibernate Performance Optimization of reusing SessionFactory (Programming)

- Linux shell script under the use of randomly generated passwords (Programming)

- Manually create Oracle Database Explanations (Database)

- General Linux interface server parameter tuning (Server)

- Recover accidentally deleted Nginx logs (Server)

- Linux C programming and Shell Programming in the development of practical tools similarities summary (Programming)

- How to modify the Ubuntu terminal user name changed to red color (Linux)

- Node.js simple interface server (Server)

- How to achieve SSH without password login (Server)

- Vim useful plugin: EasyGrep (Linux)

- Ubuntu 14.04 installed Nvidia CUDA 7.5 and build Python Theano deep learning development environment (Linux)

- Forgot Linux root password (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.
- The practical application of Oracle synonyms + dblink (Database)
- shell script: LVS start simple script (Server)
- Erase do with HTML5 Canvas and diffusion effect (Programming)
- Preview function to confirm the stop resource Oracle 12c new feature crsctl (Database)
- Use LKM change the default linux security level (Linux)
- Help you make Git Bisect (Linux)
- Linux file system management partition, format, mount - label mount (Linux)
- RHEL (RedHat) 6.3 using EPEL Yum source (Linux)
- Android and OpenCV2.4.4 build an image processing framework (2013 edition) (Linux)
- Docker Build a Java compiler environment (Linux)
- How to Install Linux kernel 4.0 on Ubuntu / CentOS (Linux)
- OpenSSL Introduction and compilation steps on Windows, Linux, Mac systems (Linux)
- Use Linux built-in firewall to improve network access control (Linux)
- 10 Linux in the passwd command examples (Linux)
- Fedora10 use Git version Configuration Management (Linux)
- The easiest 11g Active DataGuard (ADG) to establish the configuration process (Database)
- Ubuntu install video playback software SMPlayer (Linux)
- Spark parquet merge metadata problem (Server)
- Ubuntu 14.04 jdk Installation and Configuration (Linux)
- Installation Mate Desktop in FreeBSD 10.1 (Linux)
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.