How to implement distributed transactions in MySQL XA

Preface

There is no doubt that MySQL supports single-machine transactions well. So in a distributed system involving multiple nodes, how does MySQL implement distributed transactions? For example, when developing a business system, it accepts external requests and then accesses multiple internal systems to execute the request. During execution, we need to update the values ​​of multiple databases (D1, D2, D3) at the same time. Since the system must be consistent, the values ​​of the three databases must either be updated successfully at the same time or not updated at all. Otherwise, some instructions of the subsystem will succeed, while some instructions will not be executed. This leads to confusion in understanding the results.

So, how does MySQL achieve consistency in updating multiple MySQL databases? That's MySQL XA. MySQL implements the operation of multiple databases by supporting the two-phase commit protocol of the XA specification.

XA Protocol

When mentioning the XA specification, we have to talk about the DTP model (Distributed Transaction Processing). The XA specification stipulates the communication method between the two modules in the DTP model: the transaction manager and the resource manager. DTP is actually distributed transaction processing

The functions of each module are as follows:

• AP (Application Program): Application, defines transaction boundaries (defines the start and end of a transaction) and accesses resources within the transaction boundaries.
• RM (Resource Manger): manages shared resources and provides external access interfaces. For external programs to access shared resources such as databases. In addition, RM also has the ability to roll back transactions.
• TM (Transaction Manager): TM is the coordinator of distributed transactions. TM communicates with each RM and is responsible for managing global transactions, allocating transaction unique identifiers, monitoring transaction execution progress, and is responsible for transaction submission, rollback, failure recovery, etc.

It may be difficult to understand at first. In summary, the architecture is that applications access and use shared resources provided by the resource manager and define transaction operations through the transaction interface (TX interface) provided by the transaction manager. The transaction manager and resource management will execute the two-phase commit protocol based on the XA specification.
The XA specification process is shown in the following figure

• The application program AP initiates a transaction request to the transaction manager TM
• TM calls xa_open() to establish a session with the resource manager
• TM calls xa_start() to mark the beginning of a transaction branch
• AP accesses the resource manager RM and defines operations, such as inserting records.
• TM calls xa_end() to mark the end of the transaction branch
• TM calls xa_prepare() to notify RM to prepare for the submission of the transaction branch. In fact, it is the submission request phase of the two-phase submission.
• TM calls xa_commit() to notify RM to commit the transaction branch, which is the commit execution phase of the two-phase commit.
• TM calls xa_close to manage the session with RM.
  • These interfaces must be executed in sequence, for example, the xa_start interface must be before xa_end. In addition, it is important to note that the transaction manager only marks the transaction branch and does not execute the transaction. The transaction operation is ultimately completed by the application notifying the resource manager. In addition, let's summarize the XA interface
• xa_start: responsible for starting or resuming a transaction branch and managing the XID to the calling thread
• xa_end: responsible for canceling the relationship between the current thread and the transaction branch
• xa_prepare: responsible for asking RM whether it is ready to commit the transaction branch xa_commit: notifies RM to commit the transaction branch
• xa_rollback: notify RM to roll back the transaction branch
  

How to implement distributed transactions with MySQL XA

There are two types of XA transactions in MySQL. One is the internal XA transaction, which is mainly used to coordinate the storage engine and binary log. The other is the external transaction, which can participate in external distributed transactions (such as distributed transactions implemented by multiple databases). Here we mainly discuss external transactions.

Note: Distributed transactions can only be used in MySQL when the isolation level is set to Serializable.
MySQL's XA syntax is as follows

XA {START|BEGIN} xid [JOIN|RESUME]
XA PREPARE xid
XA END xid
XA COMMIT xid[ONE PHASE]
XA ROLLBACK xid
XA RECOVER[CONVERT XID ]

Among them, xid is the transaction ID, which uniquely represents a transaction branch. Each transaction branch has an id.
First, confirm whether the XA function is enabled.

Set the isolation level to serializable

Execution Results

First, call the "XA START 'xid'" command to put the XA transaction into the ACTIVATE state, and then execute the multiple SQL statements that make up the transaction (such as update
t1 set c1 = 'a' where id=1), that is, specifying the transaction boundary. Then call "XA END 'xid'" to put the transaction into IDLE state, which means ending the transaction boundary.

Next, for an XA transaction in the IDLE state, you can execute an "XA PREPARE" command or an "XA COMMIT...ONE PHASE" command.
PREPARE to execute the commit request phase of the two-phase commit protocol. Executing the "XA RECOVER" command will list all XA transactions in the PREPARED state. XA
COMMIT…ONE PHASE is used to prepare and commit transactions, that is, convert to the one-phase protocol and commit transactions directly.

Finally, call "XA COMMIT" to commit the transaction (or "XA ROLLBACK" to rollback the transaction). This achieves the consistency of global transactions.

From the above process, we can see that in the MySQL database distributed transaction, the role of MySQL is actually the RM in the XA transaction process, and the TM is the client connected to the MySQL server. In a distributed transaction, at least two RMs are generally involved. So when we say that MySQL supports the XA protocol, we are referring to MySQL as RM, which means that MySQL implements the functions that RM should have in the XA protocol.

This is the end of this article about understanding how MySQL XA implements distributed transactions. For more information about MySQL XA distributed transactions, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!