How to Solve Cross-Database Transactions with Sharding: A Practical Guide

1. Introduction

Technical colleagues know that when table data exceeds a certain scale, we need to split tables to solve single‑table performance bottlenecks; when database load exceeds a threshold, we need to split databases to solve connection‑count and performance bottlenecks.

This article describes a scenario that satisfies the following conditions:

Sharding and storage across tables

Need to perform the same transaction on tables with different shard counts

The sharding strategy of these tables depends on the same business ID

In such cases, we want tables of different scales to be routed to the same database shard for a given business ID, eliminating the need to handle cross‑database transactions.

2. Case Study

Background

Assume there are two database instances that store a merchant's order detail table and order summary table. Both tables use a shop_id modulo sharding strategy, splitting tables at 5 million rows per table.

shop_order_detail (order detail) – daily 60 million rows

Number of shards

60 M / 5 M = 12 tables

Sharding rule shop_id % 12 Database sharding rule shop_id % 12 / 2 Tables per database

12 / 2 = 6 tables

shop_order_stat (order summary) – daily 20 million rows

Number of shards

20 M / 5 M = 4 tables

Sharding rule shop_id % 4 Database sharding rule shop_id % 4 / 2 Tables per database

4 / 2 = 2 tables

After configuration the generated tables look like the following (images illustrate the sharding distribution):

We need to perform a transaction where, after successfully inserting a new order detail, we update the order summary.

Problem

When processing an order with user_id = 3, the insert into shop_order_detail is routed to DB0, while the update to shop_order_stat is routed to DB1. The two operations cross databases, so they cannot be executed in a single transaction and the process aborts.

### Cause: ERR-CODE: [TDDL-4603][ERR_ACCROSS_DB_TRANSACTION] Transaction accross db is not supported in current transaction policy

Solution

Make shop_order_stat the mapping base table for shop_order_detail and adjust the sharding strategy of shop_order_detail so that both tables are routed to the same database for the same shop_id .

The prerequisite is that the number of tables of the target table is a multiple of the base table’s table count (e.g., 12 is 3 × 4).

Adjust the database sharding rule of shop_order_detail to match shop_order_stat. The new sharding distribution is shown below:

After the adjustment, the original mapping rule becomes invalid, causing some shop_id values to be routed to non‑existent tables. Therefore we need a combined sharding formula that maps both tables to the same database and table.

Partition Modulo Strategy

Using shop_order_stat as the reference, we treat its single‑database table count (2) as the block size and its total table count (4) as the partition size. For a shop_id in the range 0‑11 we compute:

The final shard index formula is:

Shard index = (business ID % total tables) + (business ID % total tables / block size) * tables per DB + (business ID % total tables / partition size) * block size

Applying this to the example yields the following configuration.

Configuration Example (TDDL Sharding Rule)

<bean id="shop_order_stat" class="com.taobao.tddl.rule.TableRule">
    <property name="dbNamePattern" value="{0000}"/>
    <property name="dbRuleArray" value="(#shop_id,1,4#.longValue() % 4).intdiv(2)"/>
    <property name="tbNamePattern" value="shop_order_stat_{0000}"/>
    <property name="tbRuleArray" value="#shop_id,1,4#.longValue() % 4"/>
</bean>
<bean id="shop_order_detail" class="com.taobao.tddl.rule.TableRule">
    <property name="dbNamePattern" value="{0000}"/>
    <property name="dbRuleArray" value="(#shop_id,1,4#.longValue() % 4).intdiv(2)"/>
    <property name="tbNamePattern" value="shop_order_detail_{0000}"/>
    <property name="tbRuleArray">
        <value>
            def index = #shop_id,1,12#.longValue() % 12;
            return index % 2 + (index % 4).intdiv(2) * 6 + index.intdiv(4) * 2;
        </value>
    </property>
    <property name="allowFullTableScan" value="true"/>
</bean>

Java Code Example

long shopId = 9;
int dbs = 2;
int tables = 12;
int oneDbTables = 6;
int partitionSize = 4;
int blockSize = 2;
int sharding = (int) (shopId % tables);
// target database index
int dbIndex = (int) (shopId % partitionSize / dbs);
// target table index
int tableIndex = sharding % blockSize + sharding % partitionSize / blockSize * oneDbTables + sharding / partitionSize * blockSize;

Conclusion

I am a member of the local life delivery merchant operations R&D team. Facing the multi‑table transaction closure problem in real business scenarios, I devised this solution. It has been deployed in production; anyone with a similar need can directly apply the formula and configuration.