PolarDB:Distributed transactions based on MySQL 5.6

When to use distributed transactions

Use a distributed transaction only when a transaction spans multiple database shards. If all data in the transaction maps to the same shard, process it as a regular MySQL transaction — no additional setup is required.

Example: A funds transfer that debits account A and credits account B. If both accounts map to the same shard, a standard MySQL transaction works. If they map to different shards, you must declare the transaction as a distributed transaction.

Enable 2PC transactions

All methods use SET drds_transaction_policy = '2PC' to declare the transaction type before running your business SQL.

SET AUTOCOMMIT = 0;
SET drds_transaction_policy = '2PC';

-- Business SQL: for example, a cross-shard funds transfer
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';

COMMIT;  -- Use ROLLBACK to abort

conn.setAutoCommit(false);

try (Statement stmt = conn.createStatement()) {
    stmt.execute("SET drds_transaction_policy = '2PC'");
}

// Business SQL: cross-shard funds transfer
try (Statement stmt = conn.createStatement()) {
    stmt.execute("UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A'");
    stmt.execute("UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B'");
}

conn.commit();  // Use conn.rollback() to abort

Spring Framework integration

To use DRDS distributed transactions with Spring's @Transactional annotation, extend DataSourceTransactionManager to inject the 2PC policy at connection setup time.

Step 1: Implement a custom transaction manager

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'");
        }
    }
}

Step 2: Register the transaction manager as a Spring bean

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

Step 3: Annotate classes that require distributed transactions

@Transactional("drdsTransactionManager")
public void transferFunds(String fromAccount, String toAccount, long amount) {
    // Cross-shard business logic
}

Any class annotated with @Transactional("drdsTransactionManager") automatically applies the 2PC policy for its transactions.

Transaction control statements

The following SQL statements control distributed transactions in DRDS:

Production considerations

Explicit policy per connection: DRDS requires you to set drds_transaction_policy explicitly on each connection. If you have many transaction entry points, use the Spring integration approach to apply the policy automatically at the connection level.