PolarDB:Distributed transactions based on MySQL 5.6

The distributed transactions described in this topic are intended for MySQL 5.6 or Distributed Relational Database Service (DRDS) of a version earlier than 5.3.4.

How it works

The XA protocol for MySQL 5.6 is not mature. Therefore, DRDS independently implements 2-Phase Commit (2PC) transaction policies for distributed transactions. When you use MySQL 5.7 or later versions, we recommend that you use XA transaction policies.

How to use

If a transaction involves multiple database shards, you must declare the current transaction as a distributed transaction. If a transaction involves only a single database shard, you do not need to enable distributed transactions. You can process the transaction as a single-database transaction in MySQL. No additional operations are required.

Perform the following steps to enable distributed transactions:

After transactions are enabled, execute SET drds_transaction_policy = '...' .

To enable 2PC transactions in the MySQL command-line client, run the following statements:

SET AUTOCOMMIT=0;
SET drds_transaction_policy = '2PC'; -- Recommended for MySQL 5.6 users.
.... -- Here, you can run your business SQL statement.
COMMIT; -- You can use ROLLBACK instead.

To enable 2PC transactions by using the Java database connectivity (JDBC) API, write the code as follows:

conn.setAutoCommit(false);
try (Statement stmt = conn.createStatement()) {
    stmt.execute("SET drds_transaction_policy = '2PC'");
}
// ... Here, you can execute your business SQL statement.
conn.commit(); // You can use rollback() instead.

FAQ

Q: How can I use DRDS distributed transactions in the Spring framework?

A: If you use the @Transactional annotation of Spring to enable transactions, you can enable DRDS distributed transactions by extending the transaction manager.

Sample code:

import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class DrdsTransactionManager extends DataSourceTransactionManager {
    public DrdsTransactionManager(DataSource dataSource) {
        super(dataSource);
    }
    @Override
    protected void prepareTransactionalConnection(Connection con, TransactionDefinition definition) throws SQLException {
        try (Statement stmt = con.createStatement()) {
            stmt.executeUpdate("SET drds_transaction_policy = '2PC'"); // In this case, a 2PC transaction is used as an example.
        }
    }
}

After that, instantiate the preceding class in the Spring configuration. You can write the code as the following example:

<bean id="drdsTransactionManager" class="my.app.DrdsTransactionManager">
     <property name="dataSource" ref="yourDataSource" />
</bean>

For classes that require DRDS distributed transactions to be enabled, you can add the @Transactional("drdsTransactionManager") annotation.