Shulou(Shulou.com)06/02 Report--

This article is to share with you about the broad application of the two-phase submission in MySQL. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Two-stage submission introduction

The full name of 2PC is Two-PhaseCommit, which translates to two-phase commit. It is the implementation idea of distributed transaction XA specification (XA specification is the interface specification between transaction middleware and database defined by X/Open DTP). CP, which satisfies CAP theory, is a strong consistent transaction. Two-phase commit divides the distributed transaction into two phases, the voting phase (Commit-Request-Phase) and the commit phase (Commit Phase). Roles are divided into:

Transaction initiator (AP): defines the transaction boundary (start and end) and manipulates resources within the transaction boundary.

Transaction coordinator (TM): responsible for managing transactions (commit, rollback), monitoring the progress of transaction execution, divided into transaction unique identification

Transaction participant (RM): operates according to the transaction Coordinator command, manages local shared resources, and records execution logs

Voting stage:

TM: sends a Prepare instruction to RM and waits for a receipt from RM (ACK)

RM: receives instructions from TM, locks resources, and performs transaction operations, but does not commit. Record the undo log and redo log, reply "Yes" if the transaction is successful, and "No" if it fails.

Submission phase:

TM: if all "yes" receipts from RM are received, send Commit to RM;. If there is RM without any receipt within the timeout period, or if RM replies "No", send Rollback to RM.

RM: eliminate the instructions sent by TM to perform Commit or Rollback operations. For Rollback operations, RM uses the revocation log recorded during the voting phase. Send a receipt "OK" to TM after the operation is completed. If you don't get the instructions, keep waiting.

-applications submitted in two phases-

In a distributed system, the data between the two processes are inconsistent due to software or hardware. One of the solutions to the problem of inconsistency is distributed transactions. for the requirement scenario of strong data consistency, two-phase commit can be satisfied.

-two-phase submission of generalized applications of binlog and redo log in MySQL-

MySQL's dual log (binlog and redo log) records use two-phase commit to ensure strong data consistency.

Binlog is recorded by the MySQL Server layer and is independent of any storage engine. Binlog mainly records operation logs, which are in three formats: Statement, Row, and Mixedlevel. The main uses of binlog are fault recovery and master-slave synchronization.

The redo log is recorded by the Innodb storage engine, and the smallest unit of the disk is slave. The record of MySQL is accessed in units, and redo log records the modification log on the slave. The main purpose of redo log is process crash recovery, which is mainly used to recover data on the sink. Binlog cannot repair the data on the sink, so redo log cannot be omitted. What happens if you don't use the two-phase commit mode: MySQL uses the WAL mechanism to ensure transaction persistence. Normally, both binlog and redo log have transaction start and end identities. If both binlog and redo log are written to disk synchronously, that is, write + fsync. During transaction execution, the disk is written every time, and the TPS is very low, so the database is not designed this way. Binlog and redo log only write memory during transaction execution, and the current link thread does not actively flush to disk. Binlog and redo log are not currently flushed to disk until the commit request is received.

If you write binlog first, then redo log. When the binlog write succeeds, the redo log does not write successfully, and the primary node goes down, which is divided into two states:

During transaction execution, because the recovery of Innodb storage engine is based on redo log, neither master nor slave has this data, so the data is consistent.

The transaction has been committed and there will be inconsistencies between the recovered data of master based on redo log and the data in slave.

If you write redo log first and then binlog. When the redo log is written successfully, the primary node goes down, which is divided into two states:

During the execution of the transaction, because the current transaction is not committed, it is resumed based on redo log, and it will not be written when it is not committed. Neither slave nor master has this data, so the data is consistent.

The transaction has been committed, the transaction of redo log has been committed, and the transaction recorded by binlog has not been committed. After the master node is restarted, the data will be written to the master node, while the slave node does not, so the data is inconsistent.

To sum up, data inconsistencies occur only when the transaction is in a committed state. In order to ensure data consistency. When committing a transaction, the Commit operations of redo log and binlog need to be in the same transaction. Because binlog and redo log are recorded by different layers, distributed transactions are needed. In order to ensure data consistency, two-phase commit meets such requirements.

As shown in the figure, you can see that there are two phases in redo log writing, the Prepare phase and the Commit phase, with Connect Client acting as the transaction initiator (AP), MySQL Server acting as the transaction coordinator (TM), and binlog and redo log playing the transaction actor (RM). Since redo log and binlog are in the same transaction, you need to have a transaction id identity, that is, Xid in the binlog file.

Let's analyze the fault recovery process based on two-phase commit. If the write redo log is in the Prepare phase, the primary node goes down (① in the figure). At this time, neither redo log nor binlog has a Commit identity. When master crashes and recovers, the transaction will be rolled back, binlog will not be written, and it will not be transferred to slave. So the master and slave data are consistent. If the binlog is written successfully, the primary node goes down (② in the figure). When recovering from a master crash, first determine the status of the redo log (write to disk when the redo log is in the prepare phase, otherwise the crash cannot be recovered). If there is no Commit identity, the status of the current transaction in the binlog will be determined by Xid. In this case, the redo log has the Commit identity (COMMIT or Xid event) and commits directly. Binlog has been written and data has been synchronized to slave. So the data from master and slave are consistent.

-particularity of MySQL second-phase submission-

Voting stage:

In the conventional two-phase commit protocol, TM sends a Prepare message to RM in a serial order.

In MySQL, Server first sends it to redo log for Prepare fsync operation (data is written to disk)

Submission phase:

In the conventional two-phase commit protocol, the Commit message sent by TM to RM is out of order, and there is no need to pay attention to the order in which RM is sent.

In the second stage of MySQL, Server first sends write + fsync to binLog to merge, and then notifies redo log to Commit.

Design advantages

One less interaction (one less network io for distributed transactions)

One less persistence operation (one less disk io)

The mechanism is called Last participant Optimization.

The above is the broad application of the two-phase submission in MySQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.