Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ MySQL 5.7 perfectly distributed transaction support     - Oracle TDE transparent data encryption (Database)

- Three minutes to teach you to easily grasp the grep command regular expression (Linux)

- How to Install Cantata MPD 1.3.3 for Ubuntu and Derived Version Users (Linux)

- Oracle RAC node on the expulsion of the inspection process on OEL6.3 (Database)

- Detailed software to run UnixBench (Linux)

- Android Unzip the ZIP / GZIP data (based on the InflaterInputStream implementation) (Programming)

- TCP network communication Java Programming (Programming)

- C / C ++ language variable scope: local variables, global variables, file-level variables (Programming)

- Ubuntu modify DNS restart loss problem (Linux)

- Ubuntu modify locale problem solving (Linux)

- Java Graphics2D Paint (Programming)

- Linux novice common commands (Linux)

- Based Docker build stand-alone high-availability cluster Hadoop2.7.1 Spark1.7 (Server)

- Puppet Detailed centralized configuration management system (Server)

- 10 tips daily Docker (Server)

- How do you prevent other users from accessing your home directory in Linux (Linux)

- Linux, Eclipse flash back and reinstall the JDK methods (Linux)

- How dependent on Fedora, CentOS, RHEL check RPM packages (Linux)

- The new task parallel library feature in .NET 4.6 (Programming)

- How to install Ubuntu applications Device 7 (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.
- Ubuntu 14.04 install PostgreSQL 9.2 (Database)
- How to install Nginx on FreeBSD 10.2 as an Apache reverse proxy (Server)
- DataGuard Standby backup error RMAN-06820 ORA-17629 to solve (Database)
- Repair Chrome for Linux is (Linux)
- Getting Started with Linux: Learn how to install and access CentOS 7 Remote Desktop on a VPS (Server)
- PHP Performance Analysis and Experiment: Performance Micro Analysis (Programming)
- Math objects easily overlooked but very convenient method --JavaScript (Programming)
- Thinking in Java study notes - Access modifiers (Programming)
- Oracle common internal event tracking number (Database)
- Ubuntu 14.04.1 LTS compile and install the new kernel (Linux)
- Oracle 11g modify MEMORY_TARGET (Database)
- Linux installation Jetty deployment under RedHat5 8 (Linux)
- Linux user login ban (Linux)
- Integrated security administrator Linux accident management (Linux)
- Binder began to talk about inter-process communication mechanism from Android AIDL (Programming)
- Docker knowledge base (Server)
- Gitolite how to import other Git repositories (Server)
- 5 fast Node.js application performance tips (Programming)
- Ubuntu Backup and Recovery (Linux)
- How to configure a server in a MySQL Cluster (Database)
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.