Understanding Local Transactions in Sharding-JDBC and Their Limits for Distributed Transactions

In a recent question from a reader on a knowledge community, the author is asked why the @Transactional annotation, which is a local transaction marker, can still roll back when data is inserted across multiple Sharding-JDBC shards.

The article first reviews the basic idea of local transactions: in a single‑node environment a simple @Transactional annotation is sufficient, but once sharding introduces cross‑database operations the situation becomes more complex.

Using a product table sharded by product_id into two databases (DB1 and DB2), the author shows a pseudo‑code example of a batch insert wrapped with @Transactional. Although the method touches both databases, the author asks whether the annotation can still guarantee a rollback.

The answer is that Sharding-JDBC’s local transaction can roll back in two specific cases:

Non‑cross‑database transactions, such as operations that only involve table splitting within a single database.

Cross‑database transactions that are aborted by a logical exception (e.g., an explicit throw after all inserts succeed).

However, it cannot handle cross‑database failures caused by network or hardware issues; if one database crashes before the rollback completes, the other may already have committed, leading to data inconsistency.

To understand why logical‑exception‑driven rollbacks work, the article examines the Sharding-JDBC source code. An SQL statement is first rewritten and split into multiple data‑source specific statements based on the sharding key, then executed on each target.

The key class is ShardingConnection , which extends java.sql.Connection . Its rollback method distinguishes between local and distributed transactions:

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

For local transactions, the overridden method ultimately calls the parent class’s rollback , which uses ForceExecuteTemplate#execute() to iterate over all cached connections (e.g., ds1, ds2) and invoke Connection::rollback on each:

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

Because no transaction log is recorded, the rollback succeeds as long as all data sources are reachable; network or hardware failures can prevent a complete rollback, causing inconsistency.

The article concludes that relying solely on Spring’s @Transactional cannot guarantee distributed transactions across shards, and warns against being misled by Sharding-JDBC’s apparent support.

For true cross‑database consistency, Sharding-JDBC offers three alternatives:

Strong‑consistency XA protocol transactions.

Flexible Base‑type transactions.

Custom distributed‑transaction solutions via the SPI mechanism.

Further detailed introductions to these solutions will be covered in subsequent articles.

Readers are invited to follow the author’s “Billion‑Scale Data Sharding Practice” series for hands‑on source code and to join the author’s knowledge community for additional video tutorials and resources.