Distributed transaction series tutorial - Chapter 4 - XA solution to solve distributed transactions

1, XA solution

Xa is a distributed transaction protocol proposed by Tuxedo. Xa is roughly divided into two parts: transaction manager (TM) and resource manager (RM). The resource manager (RM) is implemented by databases, such as Oracle and DB2. These databases all implement the interface of XA specification, and TM, as the global scheduler, is responsible for the submission and rollback of each RM. XA protocol is also divided into 2PC and 3PC. This chapter discusses 2PC of XA protocol;

1.1 XA implementation of MySQL

MySQL XA is implemented based on DTP distributed transaction processing model standard and supports distributed transactions with multiple data sources.

The command is as follows:

• Start a global transaction:

xa start xid
xa start "001";

• Put the global transaction in the idle state (all RM resources are locked in this state):

xa end xid
xa start "001";

• Preparation stage:

xa prepare xid
xa prepare "001"

• Submission phase:

xa commit xid
xa commit "001";

• Do not enter two-stage direct submission:

xa commit xid one phase;
xa commit "001" one phase;

XA execution process:

Create a test table:

create table user(
	id int,
    username varchar(30)
);

insert into user values(1,'zs');

Case:

mysql> xa start '001';
Query OK, 0 rows affected (0.00 sec)

mysql> update user set username='ls';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> xa end '001';			
Query OK, 0 rows affected (0.00 sec)

mysql> xa prepare '001';
Query OK, 0 rows affected (0.00 sec)

mysql> xa commit '001';			# Release lock resource
Query OK, 0 rows affected (0.00 sec)

mysql>

Tips: in the MySQL console, we can not demonstrate that multiple branch transactions can be executed in a global transaction. In the next summary, we can use the Java client to operate multiple branch transactions in a global transaction.

1.2 Atomikos implements distributed transactions

JTA (Java Transaction API): Java Transaction API (programming interface) is an implementation of XA in Java. The underlying layer of JTA adopts 2PC (two-phase commit protocol)

There are three roles in JTA:

• Transaction manager: transaction manager

• XAResource: resource manager. Represents each data source (RM)

• XID: transaction ID. each independent data source will be assigned a transaction ID (xa start xid in front)

JTA is just a set of programming interfaces for distributed multi data sources provided by java to help us solve distributed transactions. Our specific implementation adopts atomikos. Atomikos is an open source transaction manager that provides value-added services for the Java platform

Construction works

Create order database and inventory database:

create database orders;
use orders;
CREATE TABLE `t_orders` (
  `id` varchar(30) NOT NULL,
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `t_orders`(`id`,`count`) values ('1',101);

create database store;
use store;
CREATE TABLE `t_store` (
  `id` varchar(30) NOT NULL,
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `t_store`(`id`,`count`) values ('1',100);

pom.xml:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.nc</groupId>
    <artifactId>xa_transaction</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>xa_transaction</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.17</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jta-atomikos</artifactId>
        </dependency>

    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

DataSourceConfig:

package com.lscl.config;

import com.atomikos.icatch.jta.UserTransactionManager;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.jta.JtaTransactionManager;

import java.util.Properties;

@Configuration
public class DataSourceConfig {


    /**
     * t_orders data source
     *
     * @return
     */
    @Bean(name = "ordersDS")
    @Qualifier("ordersDS")
    public AtomikosDataSourceBean ordersDS() {
        AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
        atomikosDataSourceBean.setUniqueResourceName("ordersDS");
        atomikosDataSourceBean.setXaDataSourceClassName(
                "com.mysql.jdbc.jdbc2.optional.MysqlXADataSource");
        Properties properties = new Properties();
        properties.put("URL","jdbc:mysql://localhost:3306/orders");
        properties.put("user", "root");
        properties.put("password", "admin");
        atomikosDataSourceBean.setXaProperties(properties);
        return atomikosDataSourceBean;
    }


    /**
     * t_store data source
     *
     * @return
     */
    @Bean(name = "storeDS")
    @Qualifier("storeDS")
    public AtomikosDataSourceBean storeDS() {
        AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
        atomikosDataSourceBean.setUniqueResourceName("storeDS");
        atomikosDataSourceBean.setXaDataSourceClassName(
                "com.mysql.jdbc.jdbc2.optional.MysqlXADataSource");
        Properties properties = new Properties();
        properties.put("URL", "jdbc:mysql://localhost:3306/store");
        properties.put("user", "root");
        properties.put("password", "admin");
        atomikosDataSourceBean.setXaProperties(properties);
        return atomikosDataSourceBean;
    }

    /**
     * transaction manager
     *
     * @return
     */
    @Bean
    public UserTransactionManager userTransactionManager() {
        UserTransactionManager userTransactionManager = new UserTransactionManager();
        userTransactionManager.setForceShutdown(true);
        return userTransactionManager;
    }

    /**
     * jta transactionManager
     *
     * @return
     */
    @Bean
    public JtaTransactionManager transactionManager() {
        JtaTransactionManager jtaTransactionManager = new JtaTransactionManager();
        jtaTransactionManager.setTransactionManager(userTransactionManager());
        return jtaTransactionManager;
    }

}

Controller:

package com.lscl.controller;

import com.lscl.service.OrdersService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class OrdersController {

    @Autowired
    private OrdersService ordersService;

    @RequestMapping("/addOrders/{flag}")
    public String add(@PathVariable Integer flag) throws Exception{
        ordersService.add(flag);;
        return "ok";
    }
}

Service:

package com.lscl.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
import org.springframework.stereotype.Service;

import javax.transaction.Transactional;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

@Service
public class OrdersService {

    @Autowired
    @Qualifier("ordersDS")
    private AtomikosDataSourceBean ordersDS;            //RM1

    @Autowired
    @Qualifier("storeDS")
    private AtomikosDataSourceBean storeDS;             //RM2

    @Transactional
    public void add(Integer flag) throws Exception {
        Connection ordersConn = null;
        Connection storeConn = null;

        try {
            ordersConn = ordersDS.getConnection();
            storeConn = storeDS.getConnection();

            // Order + 1
            String ordersSQL = "update t_orders set count=count+1";

            // Inventory-1
            String storeSQL = "update t_store set count=count-1";

            // Execute order + 1
            Statement ordersState = ordersConn.createStatement();		// prepare phase
            ordersState.execute(ordersSQL);

            if (flag == 500) {
                int i = 1 / 0;          // Simulation anomaly
            }

            // Execute inventory - 1
            Statement storeState = storeConn.createStatement();			// prepare phase
            storeState.execute(storeSQL);
            
            // The code has no problem and is ready to initiate the global commit phase
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            if (ordersConn != null) {
                ordersConn.close();
            }

            if (storeConn != null) {
                storeConn.close();
            }
        }
    }
}

Call diagram:

1.3 summary

1) When branch transactions are executed, RM resources will be locked. You need to wait until all RM responses are received. When the second phase is executed (commit / rollback), the RM lock will be released. It is not applicable in places with high concurrency.

2) The XA scheme depends on the support of the local database for the XA protocol. If the local database does not support the XA protocol, the third-party program (Java) will not operate. For example, many non relational databases do not support Xa.

3) MySQL is not very friendly to the XA scheme. The XA implementation of MySQL does not record the prepare phase log.