Can @Transactional Rollback Across Sharded Databases? Understanding Sharding-JDBC Local Transactions

Recently a reader asked why the simple @Transactional annotation appears to roll back across databases when using Sharding-JDBC.

In a single‑node setup, a local transaction is straightforward: just annotate the method with @Transactional. However, after sharding the data across multiple databases, the transaction becomes cross‑database, raising the question of whether a local transaction can still guarantee consistency.

Local Transaction

Sharding-JDBC may give the impression that a local transaction can handle distributed operations. Consider a product table sharded by product_id into two databases (DB1 and DB2). The following pseudo‑code inserts a batch of products:

<code>@Transactional
public int insertBatch() {
    for (int i = 0; i < 10; i++) {
        insert(product);
        // ...
    }
}
</code>

Although @Transactional is used, Sharding-JDBC routes each insert to DB1 or DB2 based on the product_id shard key. The method therefore spans both databases.

When an exception is manually thrown inside the method, both databases roll back, leading many to believe that Sharding-JDBC’s local transaction can guarantee distributed rollback.

Real conclusion: Sharding-JDBC’s local transaction cannot guarantee a true distributed transaction.

Sharding-JDBC’s local transaction works in two situations:

Non‑cross‑database transactions, such as operations confined to a single database or only table‑level sharding.

Cross‑database transactions caused by logical exceptions (e.g., an exception thrown after inserting into both DB1 and DB2).

It does NOT support cross‑database transactions caused by network or hardware failures. In such cases, if one database crashes before commit, only the other database’s changes persist, resulting in data inconsistency.

Distributed transactions require a transaction coordinator and transaction logs, which local transactions lack.

Why Logical Exceptions Allow Cross‑Database Rollback

To understand this, examine Sharding-JDBC’s SQL processing flow. Each SQL statement is rewritten and split into separate statements for each data source based on the shard key, then executed on the corresponding databases.

The key class is

ShardingConnection

, which extends

java.sql.Connection

. Its

rollback

method distinguishes between local and distributed transactions:

<code>@Override
public void rollback() throws SQLException {
    //① local transaction
    if (TransactionType.LOCAL == transactionType) {
        super.rollback();
    } else {
        //② non‑local transaction
        shardingTransactionManager.rollback();
    }
}
</code>

For local transactions, the parent class’s

rollback

is invoked, which iterates over all cached connections (e.g., ds1 and ds2) and calls

Connection::rollback

on each:

<code>public void execute(final Collection<T> targets, final ForceExecuteCallback<T> callback) throws SQLException {
    Collection<SQLException> exceptions = new LinkedList<>();
    for (T each : targets) {
        try {
            callback.execute(each);
        } catch (final SQLException ex) {
            exceptions.add(ex);
        }
    }
    throwSQLExceptionIfNecessary(exceptions);
}
</code>

This means rollback is performed on every data source without recording any transaction log. Therefore, as long as there is no network or hardware failure, the rollback succeeds. If a failure occurs, some data sources may not roll back, leading to inconsistency.

Summary

Relying solely on Spring’s @Transactional cannot guarantee cross‑database distributed transactions; Sharding-JDBC’s local transaction gives a false impression of support.

Sharding-JDBC does provide limited cross‑database transaction support in three categories:

Strong‑consistency XA protocol transactions.

Flexible transactions based on Base.

Customizable distributed transaction solutions via SPI.

The article only introduces the basics; future posts will detail these three solutions.