background

In traditional architectures, you can use spring's @Transactional for declarative or programmatic transaction management, but if multiple data source operations are involved in our code, spring's @Transactional transaction management mechanism will fail, so we can consider using a two-phase commit solution.
Let's take mysql as an example. After version 5.0, mysql supports the XA specification, which supports distributed transactions in the form of 2PC.

mysql XA

Related sql statements

XA start 'global_id','branch_id';
update user set age=22 where id=12;
update order set amount=1000.01 where id=1234;
XA end  'global_id','branch_id';

XA prepare   'global_id','branch_id';
XA RECOVER;  -- View all currently in preparation XA affair
XA commit;-- True Commit Transaction
XA rollback;-- Rollback transaction

Java Code

Use druid to manage connection pools, which support XA
import com.alibaba.druid.pool.xa.DruidXADataSource;
import com.mysql.jdbc.jdbc2.optional.MysqlXADataSource;

import com.alibaba.druid.pool.xa.DruidXADataSource;import com.mysql.jdbc.jdbc2.optional.MysqlXid;import javax.sql.XAConnection;import javax.transaction.xa.XAResource;import javax.transaction.xa.Xid;import java.sql.Connection;import java.sql.Statement;import java.util.Properties;/**
 * @author Jam Fang  https://www.jianshu.com/u/0977ede560d4
 * @version Creation time: 2019/4/14 13:58
 */public class TwoPhaseCommitApplication {    public void multiDataSourceTest() throws Exception {        String propertyfile = "/app.properties";        Properties props = new Properties();
        props.load(getClass().getResourceAsStream(propertyfile));        //Initialize Data Source  DruidXADataSource  xaDataSource_1 = initXADataSource(props, "db1.);  //Initialize XA Connection  XAConnection xaConnection_1 = xaDataSource_1.getXAConnection(); //Initialize XA Resource  XARurce _1 = xaConnection_1.get XAResource (); \//Get a database connection * Connection connection_1 = xaConnection_1.getConnection();
        connection_1.setAutoCommit(false);        //Create XID  Xid xid_1 = new MysqlXid("globalid".getBytes(), "branch-1".getBytes(), 0);  //associated transaction start end
        xaResource_1.start(xid_1, XAResource.TMNOFLAGS);        Statement stmt = connection_1.createStatement();        String sql_1 = "INSERT INTO `order`(orderid,amount,product) values('00001','3000.00','Apple Notebook');";//"delete from test3 where pk_t=3;";
        stmt.executeUpdate(sql_1);
        xaResource_1.end(xid_1, XAResource.TMSUCCESS);        //Transaction Preparation  int result_1 = xaResource_1.prepare(xid_1);  DruidXADataSource xaDataSource_2 = initXADataSource(props,'db2.'); XAConnection_2 = xaDataSource_2.getXAConnection(); XARurce xaResource_2 = connection_2.getXAResource (); ConnectionNnection_2 = xaConnection_2.getConnection();
        connection_2.setAutoCommit(false);        Xid xid_2 = new MysqlXid("globalid".getBytes(), "branch-2".getBytes(), 0);
        xaResource_2.start(xid_2, XAResource.TMNOFLAGS);        Statement stmt2 = connection_2.createStatement();        String sql_2 = "update shipping set address='Beijing Huangpu River' where id=1;";
        stmt2.executeUpdate(sql_2);
        xaResource_2.end(xid_2, XAResource.TMSUCCESS);        int result_2 = xaResource_2.prepare(xid_2);        //if (result_1 == XAResource.XA_OK &&
                result_2 == XAResource.XA_OK) {            //All return to OK for submission
            xaResource_1.commit(xid_1, false);
            xaResource_2.commit(xid_2, false);        } else {            //Rollback transaction
            xaResource_1.rollback(xid_1);
            xaResource_2.rollback(xid_2);        }    }    DruidXADataSource initXADataSource(Properties props, String prefix) {        DruidXADataSource xaDataSource = new DruidXADataSource();
        xaDataSource.setDbType(props.getProperty(prefix + "dbtype"));
        xaDataSource.setUrl(props.getProperty(prefix + "url"));
        xaDataSource.setUsername(props.getProperty(prefix + "username"));
        xaDataSource.setPassword(props.getProperty(prefix + "password"));        return xaDataSource;    }    public static void main(String args[]) {        try {            new TwoPhaseCommitApplication().multiDataSourceTest();        } catch (Exception e) {
            e.printStackTrace();        }    }}

app.properties file

db1.dbtype=mysql
db1.url=jdbc:mysql://127.0.0.1:3306/archdemo1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
db1.username=root
db1.password=123456
db2.dbtype=mysql

db2.url=jdbc:mysql://127.0.0.1:3306/archdemo2?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
db2.username=root
db2.password=123456

Analysis

In this scenario, our code acts as TM, which is also the transaction resource coordinator, and two different data sources, mysql, which acts as the RM resource manager, judges the preparation of each transaction in our code, commits the transaction if both OK and rollback s the transaction if they are not ready.

Modify to a sql that does not execute properly to view its execution

image.png

Through breakpoint analysis, we find that the program will have an exception when it executes this sentence, that is, the SQL statement is executed before prepare, but we set the transaction not to commit automatically, so we can not see the result of sql_1 execution in the database.

Modify normal sql to add breakpoints during prepare phase

image.png

We look at transactions in the database because I'm working on a cross-database data source on a database server, so we can see two xa records

image.png

Let's move on to the commit statement, adding the first commit

image.png

At this point, you can see that the xa information for the first transaction is no longer available, that is, the first transaction branch has committed successfully.

image.png

You can see in the database that a new insert succeeded

image.png

This is when we try to modify the structure or insert a statement and we find the database locked

image.png

After we release the breakpoint, we can see that other statements execute normally, that is, xa locks the database during the commit phase. After further analysis, we find that xa locks the entire table after entering xa end, because the sql is an update statement, the whole table is locked until the xa end is committed or rolled back.

image.png

extend

We can easily extend this XA mechanism to the situation of micro-services. Each micro-service needs to provide the corresponding mechanism, and each micro-service provides the corresponding prepare interface, commit interface, rollback interface.

shortcoming

Performance issues with xA

XA performance is low.A comparison of XA transaction performance between one database and multiple databases shows that the performance is about 10 times worse.Avoid XA transactions where possible, such as writing data locally and distributing data with high-performance messaging systems.Or use technologies such as database replication.XA should only be used if these are not possible and performance is not a bottleneck

This mechanism assumes that all prepare ok transactions can commit normally

That is, after entering the prepare return ok, there may be some exceptions to two transactions during the commit phase, such as the first normal commit, but the second one fails with some exceptions.