Shulou(Shulou.com)06/03 Report--

Background

In traditional architecture, spring's @ Transactional can be used for declarative or programmatic transaction management, but if multiple data source operations are involved in our code, we will find that spring's @ Transactional transaction management mechanism will fail, in which case we can consider using a two-phase commit solution.

Let's take mysql as an example. Mysql supports the XA specification after version 5.0, that is, supporting distributed transactions in the form of 2PC.

Distributed transaction

Distributed transactions are used to ensure data consistency between different nodes in a distributed system. There are many kinds of distributed transaction implementation, the most representative is the XA distributed transaction protocol proposed by Oracle Tuxedo system.

Mysql XA

Related sql statement

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 prepared XA transactions XA commit;-- actual commit transaction XA rollback;-- rollback transaction Java code

Use druid to manage connection pooling, which supports 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: 13:58 on 2019-4-14 * / public class TwoPhaseCommitApplication {public void multiDataSourceTest () throws Exception {String propertyfile = "/ app.properties"; Properties props = new Properties (); props.load (getClass (). GetResourceAsStream (propertyfile)); / / initialize the data source DruidXADataSource xaDataSource_1 = initXADataSource (props, "db1.") / / initialize XA connection XAConnection xaConnection_1 = xaDataSource_1.getXAConnection (); / / initialize XA resource XAResource xaResource_1 = xaConnection_1.getXAResource (); / / obtain 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); / / related 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. / / "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 xaConnection_2 = xaDataSource_2.getXAConnection (); XAResource xaResource_2 = xaConnection_2.getXAResource () Connection connection_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) / / XA transaction preparation phase if (result_1 = = XAResource.XA_OK & & result_2 = = XAResource.XA_OK) {/ / if both return OK, commit phase 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=mysqldb1.url=jdbc:mysql://127.0.0.1:3306/archdemo1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghaidb1.username=rootdb1.password=123456db2.dbtype=mysqldb2.url=jdbc:mysql://127.0.0.1:3306/archdemo2?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghaidb2.username=rootdb2.password=123456 analysis

In this scheme, our code acts as the TM, that is, the transaction resource coordinator, while two different data sources, mysql, act as the role of RM resource management, judging the preparation of each transaction in our code, committing the transaction if all OK, and rollback transaction if not ready.

Change it to a sql that cannot be executed properly to view its execution process.

Image.png

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

Modify the normal sql and add breakpoints in the prepare phase

Image.png

We look at the transaction in the database, because I do it on a database server as a cross-database data source, so we can see two xa records

Image.png

Let's proceed to the commit statement and add the first commit

Image.png

At this time, you can find that the xa information of the first transaction is gone, that is, the first transaction branch has been committed successfully.

Image.png

You can see that a new piece of data has been successfully inserted in the database

Image.png

When we try to modify the structure or insert a statement, we will find that the database is locked.

Image.png

After we release the breakpoint, we can see that other statements are executed normally, that is to say, 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 a update statement, the whole table is locked until the transaction is committed or rolled back.

Image.png

Extend

We can easily extend this XA mechanism to micro-services, and we need each micro-service to provide corresponding mechanisms, and each micro-service provides corresponding prepare interface, commit interface and rollback interface.

Performance problems of xA

The performance of XA is very low. Comparing the performance of one database transaction with that of XA transactions between multiple databases, we can find that the performance is about 10 times worse. Therefore, XA transactions should be avoided as much as possible, for example, data can be written locally and distributed with high-performance messaging systems. Or use technologies such as database replication. XA should be used only if none of this is possible and performance is not a bottleneck.

This mechanism assumes that all prepare ok transactions can be commit normally.

That is, after entering prepare and returning ok, some abnormal conditions may occur in the two transactions during the execution phase of commit. For example, the first transaction commits normally, but the second transaction fails with some exception.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.