How shardingsphere can ensure transaction consistency for multiple table operations in a certain db

[ykgarfield]

Question

The shardingsphere version is 5.3.2。And I use the com.baomidou.dynamic-datasource-spring-boot-starter to dynaimc switch database, because many tables no need to sharding。

Can using shardingsphere to operate on multiple tables in a single database ensure transaction consistency for this single database operation ？For example, the following code will route to the same shard database based on a certain shard key. Can transaction consistency be ensured in this situation, or if it is necessary to configure transaction consistency for a single database.

// will route to same database
public void insertOrder(Order order, List<OrderItem> orderItems) {
    orderMapper.insert(order);
    orderItemMapper.batchSave(orderItems);
}

The above sql will operate on same database，I test the first exeucte success, the second sql execute fail，the result is the first sql orderMapper.insert(order) can not rollback。If the insertOrder method add spring @Transactional，the sql execute will not enter shardinghere logic。

How should I do it，can ensure transaction consistency for multiple table operations in a certain db。

[RaigorJiang]

Please confirm that after @Transactional is added, the SQL execution is the connection obtained from ShardingSphereDataSource, otherwise ShardingSphere cannot provide help.

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.