Issues with MySQL XA Transaction Processing and Kunlun-storage's Solution

This article provides a detailed introduction to the most critical part of the KlustronDB financial-grade high-reliability technology system --- the KlustronDB_Klustore instance failure recovery mechanism. KlustronDB users do not need to understand the content of this article to use KlustronDB effectively; the content is mainly for readers who are interested in KlustronDB's core technology and MySQL transaction processing technology as a reference and for learning.

The community edition of MySQL does not support fault recovery for XA transactions, and therefore cannot be directly used as a Klustore instance for distributed databases. Kunlun-storage has improved and enhanced MySQL's transaction recovery mechanism, the most important of which is the addition of fault recovery for XA transactions, providing the fundamental conditions for KlustronDB cluster fault recovery. This part of the technology is an important component of the core technology system of KlustronDB.

Through the improvements and enhancements to community edition MySQL transaction failure handling described in this article, the KlustronDB cluster composed of kunlun-storage can correctly handle failures of any node in the cluster and ensure the ACID properties of all successfully committed transactions in the KlustronDB cluster, thereby helping KlustronDB achieve financial-grade high reliability.

This article first briefly introduces the KlustronDB cluster fault recovery technology, then describes the types of failures a distributed database cluster may face and the potential dangers if they are not effectively addressed. It then introduces the basics and key technologies of MySQL transaction processing, as well as MySQL fault handling and transaction recovery technologies. Finally, it focuses on a series of shortcomings and vulnerabilities in MySQL's XA transaction processing and how KlustronDB resolves these issues.

Overview of KlustronDB Fault Recovery Technology

KlustronDB Distributed Transaction Two-Phase Commit Process

For convenience of description, let's first briefly introduce the technical terms of the two-phase commit algorithm. A global transaction (GT) executes transaction branches (transaction branch, LT) across several Klustore instances (resource manager, RM), with its state and commit process managed by a global transaction manager (GTM). In MySQL, transactions that can act as distributed transaction branches are called XA transactions, and there are specific SQL statements for controlling these transactions (XA START, XA END, XA PREPARE, XA COMMIT [ONE PHASE]). Corresponding to these XA transactions are ordinary transactions, which are transactions started and committed through begin...commit, as well as autocommit CRUD statement transactions.

KlustronDB Distributed Transaction Two-Phase Commit Process Diagram

For transactions executed in KlustronDB, if multiple shards are written, the Global Transaction Manager (GTM) located at the Kluscomp instance will initiate a Two Phase Commit (2PC) when committing. The process and methods can be found in this article.

Overall, the reason for the two-phase commit is to prevent the problem of multiple RMs committing transaction branches where some parts fail. Only after the first phase (PREPARE phase) is completely successful does it proceed to the second phase (COMMIT phase). If any RM fails during the Prepare phase, the GTM will instruct all RMs of this global transaction to roll back their transaction branches. If all RMs succeed in the first phase, then the GTM will start the second phase COMMIT. If an RM or a Kluscomp instance fails during this phase, KlustronDB's cluster_mgr will follow up on all global transactions that were in the process of committing at the time of the failure—either committing everything or rolling back all their transaction branches.

Possible failures of database clusters and their hazards

In any distributed cluster composed of computer servers, server power outages or hardware and software failures may occur (hardware device failures, OS crashes, human errors, scheduled maintenance; system overload, exhaustion of internal and external storage space, exhaustion of other system resources), network failures (network partition, network congestion), and even complete data center failures (natural disasters, fiber optic or cable cuts, etc.).

For KlustronDB, all of these failures may lead to the interruption of global transaction execution or the commit process; if the failure occurs in a Klustore instance, it may also cause the commit process of ordinary transactions or XA transaction branches to be interrupted. If these process interruptions are not properly handled, they can lead to numerous problems. Specifically, failures occurring during the 2PC of a global transaction may result in:

1. The system is gradually stopping service

If a Kluscomp instance or Klustore instance fails, XA transactions in the PREPARED state may remain. Because these transactions still hold transaction locks, they can block other active transactions that have conflicting locks. These blocked active transactions may also be holding transaction locks, which can cause more active transactions to become blocked. Eventually, this leads to the database system gradually becoming unable to operate normally and provide services.

2. Some transaction branches commit, some transaction branches roll back

This leads to the loss of the data update part of the transaction, resulting in data inconsistency and corruption, which is a serious error.

3. If a Klustore instance fails during the submission of an XA transaction and cannot correctly recover these transactions, it may cause the transaction branches being committed on this Klustore instance at that time to be rolled back, which could result in some transaction branches of a global transaction being committed while others are rolled back, thereby leading to data loss or inconsistency.

Community Edition MySQL Transaction Failure Recovery Technology

Introduction to the Basics of MySQL Transaction Processing

For the convenience of the later chapters of this article, we will briefly list the basic knowledge and concepts of MySQL. If you are already familiar with them, you can skip this section.

MySQL supports multiple storage engines and interacts with them through its internally defined Handler interface. Currently, the commonly used storage engine that supports transaction processing (referred to as a transactional storage engine) is InnoDB, which is also the default storage engine for MySQL. At the same time, the MySQL community also has MyRocks, which includes RocksDB and implements the HANDLER interface for the RocksDB storage engine, allowing MySQL to use RocksDB as a transactional storage engine.

MySQL transmits data updates to slave nodes through the binlog event stream. The slave re-executes these binlog events, thereby obtaining data identical to the master node, becoming a real-time available standby node for the master. This achieves high availability (HA). The binlog system in MySQL is also treated as a storage engine and implements the HANDLER interface. However, the binlog system itself does not support transaction processing and cannot perform transaction rollback or recovery. When a binlog file reaches a certain size, MySQL automatically switches to a new binlog file, and these files are sequentially numbered in ascending order. Old and unused binlog files are deleted (purged) by DBAs to free up storage space.

With the binlog system enabled and gtid_mode=on, each MySQL transaction uses a GTID to uniquely identify it. The GTID contains the UUID of the master node and a monotonically increasing sequence number, and is automatically assigned by MySQL (this article does not discuss other edge uses of GTID). The system table mysql.gtid_executed records in detail the set of all GTIDs executed by a MySQL instance. Therefore, these three sets of transactions in a MySQL instance must be identical, which is also the MySQL data consistency condition:

1. The collection of all transactions recorded in the binlog (although most of them will later be purged)

2. The set of transactions executed in the storage engine

3. mysql.gtid_executed records the set of transactions to which the GTIDs belong

This is the goal of MySQL binlog recovery. For regular transactions, the community edition of MySQL achieves this goal, but for XA transactions, the community edition of MySQL completely ignores them and does not handle them at all. Kunlun-storage fully supports fault handling of XA transactions and can ensure that XA transactions also meet the above consistency requirements.

The technology described in this article is based on this commonly used setup, which also provides the highest level of data consistency, and it is the setup that KlustronDB requires to be used: enable binary logging, gtid_mode=on, sync_binlog=1, innodb_flush_at_trx_commit=1.

Transaction Processing Flow of the Community Edition MySQL

MySQL 3-Phase Transaction Commit Pipeline Diagram

The community edition of MySQL uses binlog for master-slave replication to achieve high availability. This article describes the process of committing transactions in MySQL asynchronous replication or semi-synchronous replication when binlog is enabled. Kunlun-storage's fullsync also follows this process.

To commit a transaction trx in MySQL, the trx's binlog needs to be written to the binlog file, and the trx's WAL logs in each storage engine need to be written to their respective transaction log files. The specific process, as shown in the figure above, is a three-stage pipeline. Before starting this three-stage transaction commit pipeline, each transaction first completes the engine prepare in its own thread, flushing its own redo log of transaction storage engines such as InnoDB/MyRocks to persistent storage devices.

Astute readers may have already noticed that MySQL also performs a two-phase commit when committing transactions: it first prepares, and then commits after writing the binlog in the three-phase commit pipeline described below. The reason for this approach is that the binlog is essentially another storage engine in MySQL; it stores each transaction's data updates in the form of binlog events, and MySQL needs to keep the binlog consistent with the transaction sets in real transaction storage engines like InnoDB.

This requires the transaction storage engine to support two-phase commit. Currently, InnoDB and RocksDB support two-phase commit and can be used as MySQL's transaction storage engines.

The Three-Phase Pipeline of MySQL Transaction Commit

The replication logic of a MySQL standby server requires that all binlog events of each transaction be written to the binlog file in the order they occur, and that the binlog events of the next transaction are written only after the current transaction is completed. Since MySQL 5.7 started supporting GTID-based replication, the order of transactions in the binlog needs to match their GTID sequence. To achieve this order guarantee with maximum concurrent performance, MySQL implements a three-phase commit pipeline technology to commit a large number of concurrent transaction groups.

1. Flush Stage

First, a number of concurrently submitted transactions enter the flush queue, and then acquire GTIDs in the order they appear in the queue. These GTIDs describe the commit order of the transactions and uniquely identify a transaction in a binlog within a MySQL master-slave replication cluster (corresponding to the storage shard in KlustronDB) (strictly speaking, it is called an event group, the difference is minor and does not affect understanding, which will be explained in detail at the end). They are also the basis for parallel replication on the slave. Then, the leader thread of the queue flushes the binlog events of each transaction in the flush queue (which were cached in its session before flushing) into the current binlog file sequentially. This ensures that the order of transactions in the binlog file strictly corresponds to the order of their GTIDs.

2. Sync Stage

After the flush stage is completed, the transactions in the flush queue as a whole leave the flush queue and enter the sync queue. The now-empty flush queue can accept the next batch of transactions. Usually, for a heavily loaded MySQL instance, some transactions have already been waiting to enter the flush queue at this time. Once they enter the flush queue, they start the aforementioned flush stage. As for the transactions that enter the sync queue, their leader thread performs one task on their behalf — syncing the binlog file that has been written into the binlog — syncing the contents written during the flush stage from the operating system's page cache to the disk, so that the binlog of this batch of transactions is permanently stored on this server.

After completing the sync phase, the transactions in the sync queue enter the engine commit phase.

3. Engine Commit Stage

As mentioned above, before starting the three-stage pipeline, the first thing these transactions in the submission process do is perform engine prepare. At this time, these transactions are still in the PREPARED state in the transaction storage engine, so the engine commit stage completes the commit of these transactions in the transaction storage engine (InnoDB or MyRocks).

There are two methods for committing: entering the commit queue for the leader thread to commit each transaction sequentially, or having each transaction complete the commit independently in its own worker thread. The choice between these two methods is controlled by the system variable binlog_order_commits.

The advantage of having the leader thread commit sequentially (binlog_order_commits=true) is that it can achieve extreme consistency—the order in which each transaction is committed, that is, the order in which other transactions can see the data updates of these committing transactions, is exactly the same as the order in which they appear in the binlog. However, the downside is relatively lower performance, because these transactions are committed sequentially by a single thread, without leveraging the concurrency capabilities of multi-core CPUs and high-performance SSDs.

Each worker thread independently commits (binlog_order_commits=false), which has exactly the opposite effect, allowing full use of concurrency and better performance; however, the downside is that the actual order in which transactions are committed is not strictly the same as the order in which they appear in the binlog file. Nevertheless, for the vast majority of scenarios, this difference in order is not an issue. Therefore, in the KlustronDB kunlun-storage configuration file template, we always set binlog_order_commits=false.

Errors that may be caused by MySQL instance failures

If there is no reliable transaction recovery technology, a MySQL instance failure may result in a transaction T committed on the primary node being absent in the binlog but present in InnoDB, or present in the binlog but absent in InnoDB. This would cause the data on the replica to differ from that on the primary node (data inconsistency between primary and replica nodes). This kind of primary-replica data inconsistency may lead to situations where a data update operation that executes correctly on the primary node fails when executed by the replica during replication — for example, because a row that needs to be updated or deleted does not exist at all on the replica, or a row that needs to be inserted already exists on the replica. This can cause replication to get stuck on the replica, preventing it from continuing to replicate updates from the primary node, thereby causing the MySQL primary-replica replication cluster to lose its high availability.

When using GTID mode (gtid_mode=on), MySQL compares the GTID sets executed on the master and slave nodes when establishing a master-slave connection. These are recorded as Gtid_set_master and Gtid_set_slave respectively. MySQL requires that Gtid_set_slave is a subset of Gtid_set_master; otherwise, it refuses to establish the master-slave replication relationship. Therefore, the above-mentioned mismatch situation will prevent the establishment of the master-slave replication relationship, leading to the cluster losing high availability, and after a master node failure, the system will lose data and stop servicing.

Therefore, during the startup of the MySQL server, it is necessary to align the binlog with the transaction storage engine to achieve the aforementioned 'MySQL data consistency condition'. We call this process binlog recovery.

For regular transactions (begin...commit transactions, or autocommit statement transactions), the community edition of MySQL can correctly complete binlog recovery; for XA transactions, the community edition of MySQL does not correctly implement XA PREPARE, nor does it perform any binlog recovery work, which leads to a series of problems. The following text details these two parts, as well as the KlustronDB team's improvements and enhancements in handling MySQL XA transactions.

Binlog recovery process for regular transactions in the Community Edition of MySQL

When a Klustore instance fails, any active transactions that have not been prepared will be fully rolled back by the transaction storage engine during mysqld startup, and they will not appear in the binlog files at all. Therefore, these transactions will not cause any problems. If a transaction has completed the entire commit process at the time of failure, the storage engine will definitely recover this transaction correctly, without any additional handling.

For those transactions that are being submitted, they may be in the engine prepare phase, flush phase, sync phase, or commit phase. If a transaction has completed the sync phase, or has completed the flush phase and the server OS has not restarted, then its binlog will definitely exist during fault recovery, and this transaction can be committed; otherwise, it must be rolled back. This is the transaction recovery work that needs to be done during the binlog recovery phase.

For ordinary operations, the community edition of MySQL can correctly perform binlog recovery. The method is: obtain the set of GTIDs S0 for all transactions that occurred in all binlog files before the last binlog file (marked as LB) from the prev_gtids_log_event of the last binlog file, then scan LB to get the set of GTIDs S1 for all transactions in this file, and then calculate the set of GTIDs S for all transactions executed in this MySQL instance as S = S0 ∪ S1.

Then for each transaction storage engine (InnoDB, MyRocks) TXN_SE,

1. Obtain the set txns_prepared of all transactions in the prepared state recovered from TXN_SE

2. For each transaction txn in txns_prepared, if txn.gtid is in S (indicating that txn's binlog exists in the binlog file), then commit txn; otherwise, roll back txn.

This ensures that for every txn in TXN_SE that is in the PREPARED state, if the txn does not exist in the binlog, it will be rolled back in TXN_SE; if the txn exists in the binlog, it will be committed in TXN_SE. In this way, after mysqld starts, all recovered ordinary transactions achieve the 'MySQL data consistency condition.'

Defects of XA Transaction Processing in the Community Edition of MySQL and Their Fixes in kunlun-storage

For XA transactions, the community edition of MySQL has a series of issues, which are detailed in this section. Considering the aforementioned serious potential risks, we need to improve and enhance MySQL's error handling and fault recovery capabilities for XA transaction processing, so that kunlun-storage can correctly recover XA transaction branches. This way, at the KlustronDB cluster level, KlustronDB can correctly recover the global transactions that were being committed at the time of the failure. The most critical part of recovery is that the Klustore instances must correctly recover their local XA transaction branches.

XA PREAPRE wrote binlog at the wrong timing

Processing flow of XA PREPARE and XA COMMIT in the community edition of MySQL

The workflow of executing XA PREPARE in the community edition of MySQL is shown in the above figure. It first flushes & syncs the binlog in the above three-stage commit pipeline, and then executes engine prepare.

The problem with this approach is that if, after flushing and syncing the binlog but before the engine has finished preparing, all the binlog events of this XA transaction xa_txn1 have already been fully transmitted to the standby server, and then the primary mysqld exits due to various software or hardware failures, after mysqld restarts, xa_txn1 does not exist in transaction storage engines like InnoDB, but xa_txn1 exists in the primary server's binlog and also exists on the standby node. In other words, the primary and standby data become inconsistent, and the 'MySQL data consistency conditions' are also not met, so this is a serious error.

The solution is to adjust the timing of flushing & syncing the binlog in XA PREPARE. After all transactional storage engines have completed prepare, then flush & sync the binlog of the XA transaction. However, after adjusting the order in this way, a new problem arises — the clone function no longer works.

The execution process of XA PREPARE in KlustronDB_storage and support for the clone feature

Kunlun-storage does not rely on the clone feature, because clone is only supported by InnoDB, and other storage engines are not supported at the moment. Kunlun-storage still uses XtraBackup for physical backups. However, we have decided to improve InnoDB clone's support for XA transactions to ensure the completeness of kunlun-storage's functionality.

Clone requires that, without transferring the binlog, the gtid set stored in the mysql.gtid_executed table of the new node is equivalent to the gtid set of all transactions executed by this MySQL instance. That is:

For any transaction t, t in InnoDB <=> t.gtid is in the mysql.gtid_executed table

This is also one of the conditions in the 'MySQL data consistency conditions'.

To this end, MySQL's approach is to store the GTID of the transaction in InnoDB's undo log during transaction commit, and then the background thread gtid_persistor can subsequently flush this GTID to the mysql.gtid_executed table. The new instance obtained by Clone has all the valid undo logs of the source instance, so its gtid_persistor can merge the GTID into the mysql.gtid_executed table, thereby satisfying the 'MySQL data consistency conditions.' For this reason, InnoDB's purge thread will only purge a transaction's undo log after the GTID of the transaction has been flushed to the mysql.gtid_executed table.

After adjusting the internal execution order of XA PREPARE, meaning that the engine prepare is executed first, followed by binlog flush & sync, the transaction GTID has not yet been generated when executing the InnoDB prepare step, because the GTID can only be generated after entering the flush queue of the three-phase pipeline. Therefore, in kunlun-storage, when executing an XA transaction's InnoDB prepare, we do not write the GTID into its undo log. Instead, after entering the flush queue and obtaining the GTID, we write the GTID into each transaction's InnoDB undo log one by one. Finally, after the sync phase is completed, the GTID is handed over to the gtid_persistor to be merged into the mysql.gtid_executed table (the reason for this step will be explained later). As shown in the figure below.

Schematic diagram of the execution process of XA PREPARE in kunlun-storage

The community version of MySQL does not record XA transactions in the PREPARED state in the binlog

In MySQL, the two phases of an XA transaction are recorded in the binlog as two different binlog event groups (BEG), and each BEG has a unique GTID. Even the GTIDs of the two BEGs from the same XA transaction are different. Moreover, these two BEGs may be distributed across two different binlog files. This means that if we want to find the binlogs of all XA transactions in the PREPARED state, we have to traverse all binlog events in all binlog files, which brings huge I/O and computational load. Furthermore, if old binlog files have been purged, we may not find their binlogs and, according to the algorithm described in the previous section, mistakenly roll back XA transactions that should have remained in the PREPARED state.

The method kunlun-storage uses to solve this issue is to record the set of XA transaction IDs that are in the PREPARED state in the current instance in the prev_gtid_log_event at the beginning of each binlog file. In this way, during binlog recovery, we only need to open the last binlog file. Using the set of XA transaction IDs X0 recorded in the prev_gtid_log_event of that file, together with the set X1 composed of all XA_PREPARE_log_event XA transaction IDs found in the last binlog file, we obtain the set X_prepared. While scanning the last binlog file, kunlun-storage also records the XA transaction ID sets of all XA COMMIT and XA ROLLBACK events to X_committed and X_aborted, respectively. These three sets are used in the binlog recovery process of kunlun-storage.

Kunlun-storage's fault recovery method for XA transactions

First, during InnoDB startup recovery, if an XA transaction requires a GTID but its undo log does not have a GTID, the transaction will be rolled back.

During binlog recovery, when recovering each transaction returned in the prepared state by the storage engine, the following recovery work needs to be done for XA transaction xa_txn1:

Use the XA transaction ID of xa_txn1 (hereinafter referred to as xa_txn1.xa_txn_id) to search for the set X_prepared. If it is not found (that is, the first-phase binlog of xa_txn1 does not exist in the binlog file), which is equivalent to not finding its xa_txn1.gtid in the set S described above, then xa_txn1 has not completed XA PREPARE. However, unlike a regular transaction, an XA transaction can be committed in either two phases or one phase, with the method of commitment determined by GTM, as detailed in the previous chapters. Therefore, if xa_txn1 is a one-phase commit transaction, the binlog recovery process will check whether xa_txn1.xa_txn_id is in X_committed. If it is, xa_txn1 will be committed. In other cases, xa_txn1 has neither completed XA prepare nor completed one-phase commit binlog flush & sync; it has only completed the engine prepare operation of these two actions, so xa_txn1 will be rolled back.

After the sync phase is completed, the XA PREPARE of xa_txn1 will definitely be executed, because once sync is completed, this BEG will definitely exist in the binlog and in transaction storage engines like InnoDB (even though it may be rolled back in the second phase). Therefore, after the sync phase is completed, handing the GTID over to the gtid_persistor can ensure that each GTID in the mysql.gtid_executed table definitely belongs to a transaction that has been executed in the storage engine and existed in the binlog as an event group.

If xa_txn1.xa_txn_id is found in X_committed, then commit xa_txn1; otherwise, if xa_txn1.xa_txn_id is found in X_aborted, then roll back xa_txn1. Otherwise, keep xa_txn1 in the PREPARED state. These transactions will be subsequently handled (commit or rollback) by KlustronDB's cluster_mgr.

The reason why the second phase BEG of xa_txn1 is found but xa_txn1 remains in the PREPARED state is that both the community version of MySQL and Kunlun-storage execute XA COMMIT and XA ROLLBACK by first performing a binlog flush & sync, and then executing the engine commit. This method is correct, but if the MySQL instance fails after the binlog is written but before the engine commit is completed, upon restart, the second phase binlog of xa_txn1 will be found in the binlog file, but xa_txn1 will still be in the PREPARED state. The community version of MySQL cannot recover XA transactions, but Kunlun-storage can recover them correctly.

Summary

After the above-mentioned improvements and enhancements to the transaction fault handling of the community edition MySQL, a KlustronDB cluster composed of kunlun-storage can correctly handle failures of any node in the cluster and ensure the ACID properties of all successfully committed transactions in the KlustronDB cluster. For a

2*N+1

For a shard of a node, the KlustronDB cluster can ensure that data is not lost or corrupted and continue to provide data read and write services even if N Klustore instances fail simultaneously. The XA transaction fault handling capability of kunlun-storage is an important pillar of KlustronDB's financial-grade high-reliability technology system, a crucial part of KlustronDB's core technology system, and also a significant enhancement and expansion of the community edition of MySQL.