All Products
Document Center

Distributed transactions based on MySQL 5.6

Last Updated: Aug 14, 2020

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:

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

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

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


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:

  1. import org.springframework.jdbc.datasource.DataSourceTransactionManager;
  2. import org.springframework.transaction.TransactionDefinition;
  3. import javax.sql.DataSource;
  4. import java.sql.Connection;
  5. import java.sql.SQLException;
  6. import java.sql.Statement;
  7. public class DrdsTransactionManager extends DataSourceTransactionManager {
  8. public DrdsTransactionManager(DataSource dataSource) {
  9. super(dataSource);
  10. }
  11. @Override
  12. protected void prepareTransactionalConnection(Connection con, TransactionDefinition definition) throws SQLException {
  13. try (Statement stmt = con.createStatement()) {
  14. stmt.executeUpdate("SET drds_transaction_policy = '2PC'"); // In this case, a 2PC transaction is used as an example.
  15. }
  16. }
  17. }

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

  1. <bean id="drdsTransactionManager" class="">
  2. <property name="dataSource" ref="yourDataSource" />
  3. </bean>

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