@Transactional not work with sharding-jdbc 5.1.2

huang714669 commented 1 year ago

Bug Report

@Transactional not work, each update or insert operation will execute the commit action Background:

springboot version: 2.6.6
orm: mybatis-plus 3.5.2
db: mysql 5.7
local transaction

sharding configurations:

# sharding jdbc datasource configs
spring.shardingsphere.datasource.names=ds_0
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://xxx:3306/xx?allowPublicKeyRetrieval=true&useUnicode=true&useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds0.username=xx
spring.shardingsphere.datasource.ds0.password=xxx
spring.shardingsphere.datasource.ds0.maximum-pool-size=50
# sharding jdbc table rules configs
spring.sharing sphere.rules.sharding.tables.cert.actual-data-nodes=ds_0.tb,ds_0.tb${0..2},ds_0.tb${100..102},ds_0.tb${200..202},ds_0.tb${300..302}
spring.shardingsphere.rules.sharding.tables.cert.table-strategy.standard.sharding-column=ca_id
spring.shardingsphere.rules.sharding.tables.cert.table-strategy.standard.sharding-algorithm-name=cert-class-based
spring.shardingsphere.rules.sharding.tables.cert.key-generate-strategy.column=id
spring.shardingsphere.rules.sharding.tables.cert.key-generate-strategy.key-generator-name=snowflake
# sharding jdbc custom class based algorithm config
spring.shardingsphere.rules.sharding.sharding-algorithms.cert-class-based.type=CLASS_BASED
spring.shardingsphere.rules.sharding.sharding-algorithms.cert-class-based.props.strategy=standard
spring.shardingsphere.rules.sharding.sharding-algorithms.cert-class-based.props.algorithmClassName=xxx.CertShardingByRowsNoAlgorithm
# shardingsphere common props
spring.shardingsphere.props.sql-show=false
spring.shardingsphere.props.max-connections-size-per-query=5

business code

@Override
@Transactional(rollbackFor = Exception.class)
public boolean newRecord (RecordDto recordDto)  {

        recordMapper.insert(record1); // insert record1, after executing here, you can see the new record by sql query, it committed
       Boolean success =  recordMapper.insert(record2); // insert record 2, after executing here, you can see the new record by sql query, it committed as well
       success = false
        if (false) {
           // throw  exception, the transaction should be rollback, but the rollback not happen as expected
            throw new Exception("saving records failed");
        }

}

Before report a bug, make sure you have:

Searched open and closed GitHub issues.
Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot reproduce it on current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

5.1.2

org.apache.shardingsphere shardingsphere-jdbc-core-spring-boot-starter 5.1.2

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

ShardingSphere-JDBC

Expected behavior

Sharding-jdbc should work well with springboot @Transactional annatation

Actual behavior

After enable sharding-jdbca, @Transactional will not work

Reason analyze (If you can)

When not use sharding jdbc, the @Transaction can work as expected, so is sharding-jdbc caused this problem, it may change the transaction work-flow internally?

FlyingZC commented 1 year ago

I tested that transaction exceptions in version 5.3.1 can be rolled back normally, please update the version.

  @Transactional(rollbackFor = Exception.class)
  public void saveFailed() throws Exception {
      orderMapper.insert(createOrderEntity(1L, 1L));
      orderMapper.insert(createOrderEntity(2L, 2L));
      orderMapper.insert(createOrderEntity(3L, 3L));
      orderMapper.insert(createOrderEntity(4L, 4L));
      if (true) {
          throw new Exception("mock exception");
      }
  }

huang714669 commented 1 year ago

how update to 5.3.1 from 5.1.2, since i use spring-boot-starter and the latest starter version is 5.2.1

FlyingZC commented 1 year ago

how update to 5.3.1 from 5.1.2, since i use spring-boot-starter and the latest starter version is 5.2.1

@Pace2Car please have a look, thank you.

Pace2Car commented 1 year ago

Hi @huang714669l, spring-boot-starter has been removed since 5.3.0 Release, it is recommended to use ShardingSphereDriver combined with YAML configuration.You can find upgrade suggestions on the official account.

Please refer to: https://shardingsphere.apache.org/document/5.3.0/en/user-manual/shardingsphere-jdbc/yaml-config/jdbc-driver/ https://shardingsphere.apache.org/document/5.3.0/en/user-manual/shardingsphere-jdbc/yaml-config/

huang714669 commented 1 year ago

Thanks @Pace2Car for your reply, can you share more message about why starter mode are removed and not recommended and replaced with jdbc driver mode

Pace2Car commented 1 year ago

Welcome to follow the official WeChat public account, you can find detailed information in the articles published in the past. About why starter mode are removed, please refer to #22469

StarHuzy commented 1 year ago

I tested that transaction exceptions in version 5.3.1 can be rolled back normally, please update the version.
  @Transactional(rollbackFor = Exception.class)
  public void saveFailed() throws Exception {
      orderMapper.insert(createOrderEntity(1L, 1L));
      orderMapper.insert(createOrderEntity(2L, 2L));
      orderMapper.insert(createOrderEntity(3L, 3L));
      orderMapper.insert(createOrderEntity(4L, 4L));
      if (true) {
          throw new Exception("mock exception");
      }
  }
@FlyingZC
hello ! I seem to have encountered the same problem. i use version sharding-jdbc-5.3.2 ，Transaction not rolled back this is link #28401 Thank you for taking the time to resolve it

github-actions[bot] commented 7 months ago

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

terrymanu commented 3 months ago

shardingsphere-jdbc-core-spring-boot-starter is deprecated and removed in 5.3.0, please try new version and use ShardingSphere Driver.

apache / shardingsphere