How does the MySQL database implement the XA specification?

MySQL consistency log

What happens to uncommitted transactions if the MySQL database loses power?

Answer: Rely on logs.

Because before executing an operation, the database will first write the content of the operation to the file system log, and then perform the operation. When the system crashes or loses power, even if the operation is not completed, the log has been written before the operation, so we can still recover based on the log content.

In the MySQL InnoDB engine, the redo log, rollback log, and binary log are related to consistency.

redo log

Whenever an operation is performed, the relevant operation will be written to the redo log before the data is actually changed. In this way, when an unexpected power outage or other accident occurs and makes it impossible to complete subsequent tasks, you can continue to complete these changes after the system is restored.

undo log

Corresponding to the redo log, also called the undo log, it records the state of the data before the transaction starts.

When some changes fail to complete due to an unexpected situation, the state before the changes can be restored based on the undo log.

For example, transaction T1 updates data X and performs an Update operation on X, updating it from 10 to 20. The corresponding Redo log is <T1, X, 20>, and the Undo log is <T1, X, 10>.

binlog

It is a binary log maintained by the MySQL server layer. It is one of the most important logs of MySQL. It records all DDL and DML statements, including the execution time of statements, in addition to data query statements such as select and show.

Binlog is different from the redo/undo log in the InnoDB engine. Its main purpose is replication and recovery. It is used to record SQL statements that update or potentially update MySQL data and save them on disk in the form of transaction logs.

Binlog is mainly used in the master-slave replication process of MySQL. The MySQL cluster starts binlog on the Master side. The Master passes its binary log to the slave nodes, which then replay it to achieve master-slave data consistency.

You can connect to the MySQL server and view the actual binlog data using the following command:

//View the contents of the binlog file show binlog events;

//View the contents of the specified binlog file show binlog events in 'MySQL-bin.000001';

//View the binlog file being written show master status\G
 
//Get the binlog file list show binary logs;

How the XA specification is defined

XA is a distributed transaction specification proposed by the X/Open organization. The XA specification mainly defines the interface between the transaction coordinator (Transaction Manager) and the resource manager (Resource Manager).

Transaction Manager

Because XA transactions are based on a two-phase commit protocol, a coordinator is required to ensure that all transaction participants have completed the preparation work, which is the first phase of 2PC.

If the transaction coordinator receives a message that all participants are ready, it will notify all transactions that they can be committed, which is the second phase of 2PC.

The reason why a transaction coordinator needs to be introduced is that in a distributed system, two machines cannot theoretically reach a consistent state, and a single point needs to be introduced for coordination.

Resource Manager

Responsible for controlling and managing the actual resources, such as a database or a JMS queue.

Currently, mainstream databases all provide support for XA. The JMS specification, namely the Java Message Service, also defines support for transactions based on XA.

XA transaction execution process

XA transaction is an implementation of two-phase commit. According to the 2PC specification, XA divides a transaction into two phases, namely Prepare and Commit.

Prepare Phase

TM sends prepare instructions to all RMs. After receiving the instructions, RM performs operations such as data modification and logging, and then returns a message to TM indicating whether the task can be submitted or not.

If the transaction coordinator TM receives a message that all participants are ready, it will notify all transactions to commit and then enter the second phase.

Commit Phase

TM receives the prepare results from all RMs. If an RM returns that the result is unsubmittable or timed out, the TM sends a Rollback command to all RMs.

If all RMs return that the transaction can be submitted, then a Commit command is sent to all RMs to complete a transaction operation.

How MySQL implements the XA specification

There are two types of XA transactions in MySQL, internal XA and external XA. The difference is whether the transaction occurs on a single MySQL server or between multiple external nodes.

Internal XA

In MySQL's InnoDB storage engine, when binlog is enabled, MySQL will maintain both the binlog log and InnoDB's redo log. To ensure the consistency of the two logs, MySQL uses XA transactions. Because it works on a single MySQL machine, it is called internal XA.

Internal XA transactions are coordinators of binlog. When a transaction is committed, the commit information needs to be written to the binary log. In other words, the participant of binlog is MySQL itself.

External XA

External XA is a typical distributed transaction. MySQL supports SQL statements such as XA START/END/PREPARE/Commit. Distributed transactions can be completed by using these commands.

You can also check the MySQL official documentation for more XA commands.

MySQL external XA is mainly used in the database proxy layer to implement distributed transaction support for MySQL database, such as open source database middle layer, such as Taobao's TDDL, Alibaba B2B's Cobar, etc.

External XA is generally used for distributed transactions across multiple MySQL instances, which requires the application layer to act as a coordinator. For example, when we write business code, we decide whether to commit or rollback in the code and recover in case of a crash.

Xid in binlog

When a transaction is committed, an additional Xid structure is added to the internal XA that the binlog relies on. The binlog has multiple data types:

1. statement format, records are basic statements, including Commit
2. row format, records are based on rows
3. mixed format, log records use mixed format

Regardless of statement or row format, binlog will add an XID_EVENT as the end of the transaction. This event records the transaction ID, that is, Xid. When MySQL performs crash recovery, it decides how to recover based on the status of the commit in the binlog.

Binlog synchronization process

Let's take a look at the transaction submission process under binlog. The overall process is to write redo log first, then write binlog, and the successful writing of binlog is used as a sign of successful transaction submission.

When a transaction is committed:

• InnoDB enters the Prepare phase and writes/syncs redo logs, writes redo logs, writes the transaction xid to the redo logs, and does not perform any operations on binlogs.
• Write/sync binlog, write binlog log, and also write xid to binlog
• Call the InnoDB engine commit to complete the transaction and write the commit information to the redo log

If the first and second steps fail, the entire transaction is rolled back.

If the third step fails, MySQL will check whether the xid has been committed after restarting. If it has not been committed, that is, the transaction needs to be re-executed, it will perform another commit operation in the storage engine to ensure the consistency of redo log and binlog data and prevent data loss.

The actual execution also involves when the operating system cache buffer is synchronized to the file system, so MySQL supports users to customize how to flush the logs in the log buffer to the log file when committing, which is determined by the value of the variable innodb_flush_log_at_trx_Commit.

The content in the log buffer is called dirty log. If you are interested, you can check the information to learn more.

The above is the details of how MySQL database implements the XA specification. For more information about the MySQL database XA specification, please pay attention to other related articles on 123WORDPRESS.COM!