shardingsphere icon indicating copy to clipboard operation
shardingsphere copied to clipboard
verified publisher icon apache

Transaction not rolled back

Open StarHuzy opened this issue 2 years ago • 3 comments

Bug Report

Using local transactions without rollback I used a single database sharding tables use local transaction mode I have configured two tables

  • product
  • order

sharding.yaml i setting defaultType: LOCAL

dataSources:
  product:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.18.128:3310/db_order
    username: root
    password: 123456
rules:
- !SHARDING
  tables:
    t_product: # 分表，逻辑表名
      # 节点表添加下初始的表,后续会在新增租户的时候新增表且刷新节点
      actualDataNodes: product.t_product,product.t_product_1,product.t_product_2
      tableStrategy: # 配置分表策略
        standard: # 用于单分片键的标准分片场景
          shardingColumn: tenant_id
          shardingAlgorithmName: real-data-inline
    t_order: # 分表，逻辑表名
      # 节点表添加下初始的表,后续会在新增租户的时候新增表且刷新节点
      actualDataNodes: product.t_order,product.t_order_1,product.t_order_2
      tableStrategy: # 配置分表策略
        standard: # 用于单分片键的标准分片场景
          shardingColumn: tenant_id
          shardingAlgorithmName: real-data-inline
  # 分片算法配置
  shardingAlgorithms:
    real-data-inline: # 分片算法名称
      type: CLASS_BASED #自定义策略
      props:
        strategy: standard
        # 包名+类名
        algorithmClassName: org.example.utils.DataShardingAlgorithm
  bindingTables:
    - t_product,t_order
- !TRANSACTION
    defaultType: LOCAL
props:
  sql-show: true

Method implementation

    @Transactional
    public Object addProductAndOrder(){
        Product product = new Product();
        product.setProductNo("TEST-11111");
        product.setTenantId(2);
        productMapper.insert(product);

        int i = 1/0;
        Order order = new Order();
        order.setOrderNo("aaaaa");
        order.setTenantId(2);
        orderMapper.insert(order);
        return "success";
    }

i setting int i = 1/0; imitate exception

2023-09-10 16:26:37.600  INFO 9244 --- [nio-8080-exec-1] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_product  ( id,
product_no,
tenant_id )  VALUES  ( ?,
?,
? )
2023-09-10 16:26:37.600  INFO 9244 --- [nio-8080-exec-1] ShardingSphere-SQL                       : Actual SQL: product ::: INSERT INTO t_product_2  ( id,
product_no,
tenant_id )  VALUES  (?, ?, ?) ::: [1700787847348391938, TEST-11111, 2]
2023-09-10 16:26:37.644 ERROR 9244 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is java.lang.ArithmeticException: / by zero] with root cause

java.lang.ArithmeticException: / by zero
	at org.example.controller.UserController.addProductAndOrder(UserController.java:65) ~[classes/:na]
	at org.example.controller.UserController$$FastClassBySpringCGLIB$$6f13626b.invoke(<generated>) ~[classes/:na]
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.3.29.jar:5.3.29]
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:793) ~[spring-aop-5.3.29.jar:5.3.29]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.29.jar:5.3.29]
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763) ~[spring-aop-5.3.29.jar:5.3.29]
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-5.3.29.jar:5.3.29]
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388) ~[spring-tx-5.3.29.jar:5.3.29]
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-5.3.29.jar:5.3.29]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.29.jar:5.3.29]
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763) ~[spring-aop-5.3.29.jar:5.3.29]
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:708) ~[spring-aop-5.3.29.jar:5.3.29]
	at org.example.controller.UserController$$EnhancerBySpringCGLIB$$23380ca2.addProductAndOrder(<generated>) ~[classes/:na]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_131]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_131]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_131]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_131]
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) ~[spring-web-5.3.29.jar:5.3.29]
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150) ~[spring-web-5.3.29.jar:5.3.29]
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1072) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:965) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:529) ~[tomcat-embed-core-9.0.78.jar:4.0.FR]
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:623) ~[tomcat-embed-core-9.0.78.jar:4.0.FR]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:209) ~[tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) ~[tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51) ~[tomcat-embed-websocket-9.0.78.jar:9.0.78]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) ~[tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) ~[tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.3.29.jar:5.3.29]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.29.jar:5.3.29]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) ~[tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) ~[tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.3.29.jar:5.3.29]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.29.jar:5.3.29]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) ~[tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) ~[tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.3.29.jar:5.3.29]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.29.jar:5.3.29]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) ~[tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) ~[tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167) ~[tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:481) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:130) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:390) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:926) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1791) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at java.lang.Thread.run(Thread.java:748) [na:1.8.0_131]

image image

The execution results are shown above，Transaction not rolled back， How can I configure to solve the above problem？

very Thank you.

Which version of ShardingSphere did you use?

shardingsphere-jdbc 5.3.2 + JDK 17 + spring-boot 2.7.14

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-JDBC

StarHuzy avatar Sep 10 '23 08:09 StarHuzy

@StarHuzy Hi, 5.3.2 has e2e coverage for transaction rollback, so there will be no rollback problem. I also tested manually and the problem did not occur. Specifically, you may need to provide a complete demo that can reproduce the problem to further locate the problem.

FlyingZC avatar Sep 12 '23 02:09 FlyingZC

@FlyingZC
Sorry for the slow response , This is my demo ! https://github.com/StarHuzy/sharging-jdbc In this case, I used two transaction modes - LOCAL - XA/Atomikos in short LOCAL This mode transaction is invalid ， The transaction in this XA/Atomikos mode has been rolled back

I didn't find the Spring @Transactional annotation in your demo. You can manually test the ShardingSphere transaction functionality using the JDBC API to see if it works properly. Alternatively, you can refer to this demo: https://github.com/FlyingZC/shardingsphere-transaction-spring

StarHuzy avatar Sep 13 '23 01:09 StarHuzy

• Understanding

  • In a single-datasource sharding setup, you insert first, then throw an exception to expect LOCAL transactions (defaultType=LOCAL) to roll back, but the record is not rolled back; the same flow rolls back under XA/Atomikos.
  • The sample repo sharging-jdbc contains no @Transactional, and sharding.yaml sets defaultType: XA while the code uses DataSourceTransactionManager.

Root Cause

  • No Spring transaction boundary: without @Transactional (or manual setAutoCommit(false)/rollback), MySQL auto-commits each statement, so it looks like “LOCAL transaction is invalid.”
  • Transaction type and manager mismatch: sharding.yaml is XA, but you use a local transaction manager. To test LOCAL, set defaultType: LOCAL and ensure @Transactional is active.
  • ShardingSphere 5.3.2 already has e2e coverage for local transaction rollback; no similar rollback bug is known.

Analysis

  • In LOCAL mode, ShardingSphere delegates to the underlying datasource; you must annotate service methods with @Transactional (or manage JDBC transactions manually), otherwise each write auto-commits.
  • If you want XA, specify @ShardingTransactionType(TransactionType.XA) or TransactionTypeHolder.set(TransactionType.XA) before the call, and use a JTA JtaTransactionManager (e.g., Atomikos). Otherwise it falls back to local transactions.
  • Official transaction doc (Spring + local/XA config): https://shardingsphere.apache.org/document/current/en/user-manual/shardingsphere-jdbc/usage/transaction/

Conclusion

  • This is a usage/configuration issue rather than a ShardingSphere bug. Recommendations:
    1. For local transactions, set defaultType: LOCAL in sharding.yaml and ensure the tables use InnoDB.
    2. Add @Transactional to service methods that include multiple writes, e.g.:
         @Service
         public class OrderService {
             @Transactional
             public void addOrderThenFail() {
                 orderMapper.insert(...);
                 int x = 1 / 0; // simulate exception
                 orderItemMapper.insert(...);
             }
         }

  3. Ensure Spring’s transaction manager is based on the ShardingSphere DataSource.
  • If it still doesn’t roll back, please share: a minimal repro including @Transactional, your current sharding.yaml, SHOW CREATE TABLE outputs, and SQL logs around the exception so we can dig deeper.

terrymanu avatar Dec 06 '25 03:12 terrymanu

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

github-actions[bot] avatar Dec 15 '25 23:12 github-actions[bot]