How to Choose Between Local and Global Transactions with Sharding-JDBC?

[WXingSong]

Check Ahead

• I have searched the issues of this repository and believe that this is not a duplicate.

• I am willing to try to implement this feature myself.

Why you need it?

After integrating Sharding-JDBC with Seata, how can we choose between using local transactions and global transactions based on the requirements?

You can take a look at apache/shardingsphere#37283 roughly.

In summary, when Sharding-JDBC and Seata are used together, all @Transactional annotations are taken over by Seata and executed as global transactions (e.g., writing to undo_log, acquiring locks, etc.). However, in practice, global transactions are only needed for cross-service calls, while transactions within a single service only need to be local. How can I implement this requirement?

I have tried setting Sharding-JDBC to use local transactions and using Seata’s @GlobalTransactional separately for global transactions, but in practice, the following error occurs:

org.springframework.dao.InvalidDataAccessApiUsageException: Error getting generated key or setting result to parameter object. Cause: java.sql.SQLFeatureNotSupportedException: isAfterLast
; isAfterLast
      at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:106)
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107)
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:116)
      at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:95)
      at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:347)
      at jdk.proxy2/jdk.proxy2.$Proxy190.insert(Unknown Source)
      at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:224)
      at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:59)
      at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:156)
      at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:93)
      at jdk.proxy2/jdk.proxy2.$Proxy197.insert(Unknown Source)
      at cn.iocoder.yudao.module.humanity.service.TestServiceImpl.test(TestServiceImpl.java:23)
      at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
      at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.base/java.lang.reflect.Method.invoke(Method.java:568)
      at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:360)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
      at org.apache.seata.spring.annotation.AdapterInvocationWrapper.proceed(AdapterInvocationWrapper.java:55)
      at org.apache.seata.integration.tx.api.interceptor.handler.GlobalTransactionalInterceptorHandler$2.execute(GlobalTransactionalInterceptorHandler.java:186)
      at org.apache.seata.tm.api.TransactionalTemplate.execute(TransactionalTemplate.java:130)
      at org.apache.seata.integration.tx.api.interceptor.handler.GlobalTransactionalInterceptorHandler.handleGlobalTransaction(GlobalTransactionalInterceptorHandler.java:183)
      at org.apache.seata.integration.tx.api.interceptor.handler.GlobalTransactionalInterceptorHandler.doInvoke(GlobalTransactionalInterceptorHandler.java:155)
      at org.apache.seata.integration.tx.api.interceptor.handler.AbstractProxyInvocationHandler.invoke(AbstractProxyInvocationHandler.java:43)
      at org.apache.seata.spring.annotation.AdapterSpringSeataInterceptor.invoke(AdapterSpringSeataInterceptor.java:44)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
      at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:728)
      at cn.iocoder.yudao.module.humanity.service.TestServiceImpl$$SpringCGLIB$$0.test(<generated>)

How it could be?

No response

Other related information

No response

[john1337]

使用shardingsphere-jdbc，不要用shardingsphere的at模块，使用seata的就可以

[WXingSong]

使用shardingsphere-jdbc，不要用shardingsphere的at模块，使用seata的就可以

我使用的是sharding-jdbc,试过不用把sharding的事务改成本地LOCAL模式，或者去掉这个配置。全局事务回滚的时候会报上名创建主键的异常，实际上在数据库和sharding的日志输出是已经生成主键了。

[linghengqian]

As a ShardingSphere committer, I offer two approaches from the ShardingSphere JDBC perspective regarding org.apache.shardingsphere:shardingsphere-transaction-base-seata-at. Of course, I don't rule out the possibility of better handling methods on the Seata side.

1. One approach is to create two ShardingSphere logical databases: one using transaction.defaultType=BASE and transaction.providerType=Seata, and the other using transaction.defaultType=LOCAL. In fact, the local transaction for the logical database is equivalent to the distributed transaction for the real database.

2. If you'd like to test the branch at Support for executing DistSQL for ShardingSphere JDBC logical databases shardingsphere#37778 , you can dynamically modify the transaction model of a single logical database. Moreover, this can indeed largely guarantee the safety of cross-thread transactions, unless there is a physical power outage.

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class ExampleUtils {
    void test() throws SQLException {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:shardingsphere:classpath:demo.yaml");
        config.setDriverClassName("org.apache.shardingsphere.driver.ShardingSphereDriver");
        try (HikariDataSource dataSource = new HikariDataSource(config);
             Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement()) {
            statement.execute("""
                    alter transaction rule(
                       default="LOCAL", type(name="")
                     )
                    """);
            statement.execute("INSERT INTO t_order (user_id, order_type, address_id, status) VALUES (1, 1, 1, 'INSERT_TEST')");
            statement.execute("""
                    alter transaction rule(
                       default="BASE", type(name="Seata")
                     )
                    """);
            statement.execute("INSERT INTO t_order (user_id, order_type, address_id, status) VALUES (2, 2, 2, 'INSERT_TEST')");
        }
    }
}

• I suspect the second method is closer to what you're looking for, as it introduces something like https://github.com/baomidou/dynamic-datasource , but without a Java API, only SQL. Furthermore, I wouldn't encourage you to use ShardingSphere JDBC's internal Java API, which undergoes significant changes with each version. Support for executing DistSQL for ShardingSphere JDBC logical databases shardingsphere#37778 is just a preceding PR in another large group of PRs.
• Incidentally, you can only prevent cross-logic error logs related to Seata if the commits involved in When destroying RmNettyRemotingClient, all TableMetaRefreshHolder should actively close the process #7523 on the Seata side are applied within the ShardingSphere.