ShardingSphere Jdbc transaction 支持问题

[ListenQ]

Question

使用环境： shardingsphere-jdbc-core: 5.4.1，mybatis-plus：3.5.3.1，jdk8

场景、问题：
dataSources:
 ds_0: 
 ds_1: 

ds_0 和 ds_1 一样的表结构
db table: t_user，t_order_0，t_order_1

没有配置 XA 或 BASE 分布式事务

@Transactional(rollbackFor = Exception.class)
public void testSave() { 
  // 保存在ds_0.t_user
   userMapper.insert(UserPo.builder().name("王二").sex(2).companyId(1).build());
  
  // 保存ds_1.t_order_1 ,下面这行执行失败，返回错误 MysqlDataTruncation: Data truncation: Data too long for column 'name' at row 1
  orderMapper.insert(OrderPo.builder().companyId(3).name("测试太太太太太他太太太太太太太太太太太太太2").number(20).build());
}

上面 testSave(), 保存t_order失败会 让t_user 回滚
ShardingSphere Jdbc 是否默认支持 分布式事务处理？

[linghengqian]

• This is explained at https://shardingsphere.apache.org/document/current/en/features/transaction/ . The default transactions used by a certain real database in the logical database do not know the success or failure of transactions in other real databases at all.

LOCAL mode is implemented based on ShardingSphere’s proxy database interfaces, that is begin/commit/rolllback. For a logical SQL, ShardingSphere starts transactions on each proxied database with the begin directive, executes the actual SQL, and performs commit/rollback. Since each data node manages its own transactions, there is no coordination and communication between them, and they do not know whether other data node transactions succeed or not. There is no loss in performance, but strong consistency and final consistency cannot be guaranteed.

• You can think of this as a normal local transaction and has nothing to do with distributed transactions.

[ListenQ]

• This is explained at https://shardingsphere.apache.org/document/current/en/features/transaction/ . The default transactions used by a certain real database in the logical database do not know the success or failure of transactions in other real databases at all.

LOCAL mode is implemented based on ShardingSphere’s proxy database interfaces, that is begin/commit/rolllback. For a logical SQL, ShardingSphere starts transactions on each proxied database with the begin directive, executes the actual SQL, and performs commit/rollback. Since each data node manages its own transactions, there is no coordination and communication between them, and they do not know whether other data node transactions succeed or not. There is no loss in performance, but strong consistency and final consistency cannot be guaranteed.

• You can think of this as a normal local transaction and has nothing to do with distributed transactions.

Is the default transaction rule of ShardingSphere JDBC local?

[ListenQ]

• This is explained at https://shardingsphere.apache.org/document/current/en/features/transaction/ . The default transactions used by a certain real database in the logical database do not know the success or failure of transactions in other real databases at all.

LOCAL mode is implemented based on ShardingSphere’s proxy database interfaces, that is begin/commit/rolllback. For a logical SQL, ShardingSphere starts transactions on each proxied database with the begin directive, executes the actual SQL, and performs commit/rollback. Since each data node manages its own transactions, there is no coordination and communication between them, and they do not know whether other data node transactions succeed or not. There is no loss in performance, but strong consistency and final consistency cannot be guaranteed.

• You can think of this as a normal local transaction and has nothing to do with distributed transactions.

Are there any demo projects related to ShardingSphere JDBC transactions?

[linghengqian]

Is the default transaction rule of ShardingSphere JDBC local?

• Yes, the following is considered the default value.

transaction:
   defaultType: LOCAL

• Obviously, the XA distributed transaction module on Jakarta EE 8 and the Seata AT mode distributed transaction module are optional modules.

Are there any demo projects related to ShardingSphere JDBC transactions?

• Check out https://github.com/apache/shardingsphere/tree/master/examples/shardingsphere-jdbc-example-generator to configure the examples/shardingsphere-jdbc-example-generator/src/main/resources/config.yaml file content to generate Example.
• Bonus topic is that the nightly CI for the Example module is broken, you can try to fix it if you're interested - I'm not a FreeMarker expert after all.
• For distributed transaction integration using ShardingSphere JDBC under GraalVM Native Image, refer to unit test in https://github.com/apache/shardingsphere/tree/ab0ed3d581059a0ff18117f30fa48db75f8f5e3b/test/native/src/test/java/org/apache/shardingsphere/test/natived/jdbc/transactions .

[ListenQ]

Is the default transaction rule of ShardingSphere JDBC local?

• Yes, the following is considered the default value.

transaction:
   defaultType: LOCAL

• Obviously, the XA distributed transaction module on Jakarta EE 8 and the Seata AT mode distributed transaction module are optional modules.

Are there any demo projects related to ShardingSphere JDBC transactions?

• Check out https://github.com/apache/shardingsphere/tree/master/examples/shardingsphere-jdbc-example-generator to configure the examples/shardingsphere-jdbc-example-generator/src/main/resources/config.yaml file content to generate Example.
• Bonus topic is that the nightly CI for the Example module is broken, you can try to fix it if you're interested - I'm not a FreeMarker expert after all.
• For distributed transaction integration using ShardingSphere JDBC under GraalVM Native Image, refer to unit test in https://github.com/apache/shardingsphere/tree/ab0ed3d581059a0ff18117f30fa48db75f8f5e3b/test/native/src/test/java/org/apache/shardingsphere/test/natived/jdbc/transactions .

Okay, thanks.

the following is considered the default value

transaction:
   defaultType: LOCAL

Why is transaction consistency guaranteed for multiple database operations? How to simulate that local operations on multiple data sources do not meet distributed transaction consistency?

[linghengqian]

Why is transaction consistency guaranteed for multiple database operations?

• To make a long story short, when you need the concept of transactions in your business, you need distributed transactions of distributed databases. This is a feature that is created on demand. You can read the relevant documents.

How to simulate that local operations on multiple data sources do not meet distributed transaction consistency?

• The example module does not show the relevant rollback operation, but it should not be difficult to simulate. The simplest way is that you only need to use the JTA annotation of the local transaction for the logical data source of shardingsphere or manually set setAutoCommit(), commit() and rollback() of java.sql.Connection to convert the corresponding transaction operation into a distributed transaction. The relevant integration test is located at https://github.com/apache/shardingsphere/tree/master/test .

• Documents like https://shardingsphere.apache.org/document/current/en/user-manual/shardingsphere-jdbc/special-api/transaction/seata/ mark common JTA annotations.

[ListenQ]

Use mybatis, not jpa

transaction:
  defaultType: XA
  providerType: Atomikos

springboot start
@SpringBootApplication(exclude = JtaAutoConfiguration.class)

Run project prompt error：java.lang.AbstractMethodError: com.atomikos.icatch.jta.JtaTransactionServicePlugin.beforeInit()V

java.lang.AbstractMethodError: com.atomikos.icatch.jta.JtaTransactionServicePlugin.beforeInit()V
	at com.atomikos.icatch.config.Configuration.notifyBeforeInit(Configuration.java:409)
	at com.atomikos.icatch.config.Configuration.init(Configuration.java:381)
	at com.atomikos.icatch.config.UserTransactionServiceImp.initialize(UserTransactionServiceImp.java:100)
	at com.atomikos.icatch.config.UserTransactionServiceImp.init(UserTransactionServiceImp.java:189)
	at org.apache.shardingsphere.transaction.xa.atomikos.manager.AtomikosTransactionManagerProvider.init(AtomikosTransactionManagerProvider.java:46)
	at org.apache.shardingsphere.transaction.xa.XAShardingSphereTransactionManager.init(XAShardingSphereTransactionManager.java:65)
	at org.apache.shardingsphere.transaction.ShardingSphereTransactionManagerEngine.init(ShardingSphereTransactionManagerEngine.java:55)
	at org.apache.shardingsphere.transaction.rule.TransactionRule.createTransactionManagerEngine(TransactionRule.java:81)
	at org.apache.shardingsphere.transaction.rule.TransactionRule.<init>(TransactionRule.java:63)
	at org.apache.shardingsphere.transaction.rule.builder.TransactionRuleBuilder.build(TransactionRuleBuilder.java:36)
	at org.apache.shardingsphere.transaction.rule.builder.TransactionRuleBuilder.build(TransactionRuleBuilder.java:32)
	at org.apache.shardingsphere.infra.rule.builder.global.GlobalRulesBuilder.buildRules(GlobalRulesBuilder.java:55)
	at org.apache.shardingsphere.mode.metadata.NewMetaDataContextsFactory.create(NewMetaDataContextsFactory.java:99)
	at org.apache.shardingsphere.mode.metadata.NewMetaDataContextsFactory.create(NewMetaDataContextsFactory.java:71)
	at org.apache.shardingsphere.mode.manager.standalone.NewStandaloneContextManagerBuilder.build(NewStandaloneContextManagerBuilder.java:53)
	at org.apache.shardingsphere.driver.jdbc.core.datasource.ShardingSphereDataSource.createContextManager(ShardingSphereDataSource.java:78)
	at org.apache.shardingsphere.driver.jdbc.core.datasource.ShardingSphereDataSource.<init>(ShardingSphereDataSource.java:66)
	at org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory.createDataSource(ShardingSphereDataSourceFactory.java:95)
	at org.apache.shardingsphere.driver.api.yaml.YamlShardingSphereDataSourceFactory.createDataSource(YamlShardingSphereDataSourceFactory.java:167)
	at org.apache.shardingsphere.driver.api.yaml.YamlShardingSphereDataSourceFactory.createDataSource(YamlShardingSphereDataSourceFactory.java:102)
	at org.apache.shardingsphere.driver.jdbc.core.driver.DriverDataSourceCache.createDataSource(DriverDataSourceCache.java:52)
	at org.apache.shardingsphere.driver.jdbc.core.driver.DriverDataSourceCache.lambda$get$0(DriverDataSourceCache.java:46)
	at java.util.concurrent.ConcurrentHashMap.computeIfAbsent(ConcurrentHashMap.java:1660)
	at org.apache.shardingsphere.driver.jdbc.core.driver.DriverDataSourceCache.get(DriverDataSourceCache.java:46)
	at org.apache.shardingsphere.driver.ShardingSphereDriver.connect(ShardingSphereDriver.java:53)
	at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:121)
	at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364)
	at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206)
	at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:476)
	at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561)
	at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112)
	at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:269)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.startTransaction(AbstractPlatformTransactionManager.java:400)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:595)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:382)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:762)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:707)

[linghengqian]

• You need to provide unit tests like https://github.com/apache/shardingsphere/blob/master/test%2Fnative%2Fsrc%2Ftest%2Fjava%2Forg%2Fapache%2Fshardingsphere%2Ftest%2Fnatived%2Fjdbc%2Ftransactions%2Fxa%2FAtomikosTest.java and put it in a separate git.

• This Log tells nothing because all CI is OK. This appears to be a simple dependency version conflict.

[ListenQ]

Okay, thanks.

It's a dependency conflict，from shardingsphere-transaction-xa-core

 <dependency>
	<groupId>org.apache.shardingsphere</groupId>
	<artifactId>shardingsphere-transaction-xa-core</artifactId>
	<version>5.4.1</version>
	<exclusions>
		<exclusion>
			<artifactId>atomikos-util</artifactId>
			<groupId>com.atomikos</groupId>
		</exclusion>
		<exclusion>
			<artifactId>transactions</artifactId>
			<groupId>com.atomikos</groupId>
		</exclusion>
		<exclusion>
			<artifactId>transactions-api</artifactId>
			<groupId>com.atomikos</groupId>
		</exclusion>
	</exclusions>
</dependency>

It became normal after being eliminated.

[linghengqian]

• To be honest, the latest version of Atomikos 6.0.0 is used on the shardingsphere-transaction-xa-core side. It seems that there is no PR that needs to be done on the ShardingSphere side unless it involves Transition from Java EE to Jakarta EE #26041.

[strongduanmu]

Hi @ListenQ, can you translate this issue to English?

[ListenQ]

Yes, it's true that there's no need to do anything else, it's my project engineering, shardingsphere-transaction-xa-core is conflicting with the atomikos version in springboot.

Configure the solution in pom.xml like this：

<dependencyManagement>
<dependencies>
        <dependency>
	         <groupId>com.atomikos</groupId>
	         <artifactId>transactions-jta</artifactId>
	         <version>6.0.0</version>
        </dependency>
	<dependency>
		<groupId>com.atomikos</groupId>
		<artifactId>transactions-jdbc</artifactId>
		<version>6.0.0</version>
	</dependency>
</dependencies>
</dependencyManagement>

[ListenQ]

Hi @ListenQ, can you translate this issue to English?

Okay, which question is it?

[zhaoyinchuan]

@ListenQ @linghengqian Through program test, I also find that shardingsphere-jdbc-core5.4.1 supports distributed transaction default. I do not config any XA or Base transaction.

[linghengqian]

@ListenQ @linghengqian Through program test, I also find that shardingsphere-jdbc-core5.4.1 supports distributed transaction default. I do not config any XA or Base transaction.

• By default, you can certainly think that LOCAL is also a simple implementation of distributed transactions, because its behavior ensures that transaction operations will not be interrupted, but this is neither XA nor Seata AT. And XA distributed transactions are optional module.

[zhaoyinchuan]

@ListenQ @linghengqian Through program test, I also find that shardingsphere-jdbc-core5.4.1 supports distributed transaction default. I do not config any XA or Base transaction.

• By default, you can certainly think that LOCAL is also a simple implementation of distributed transactions, because its behavior ensures that transaction operations will not be interrupted, but this is neither XA nor Seata AT. And XA distributed transactions are optional module.

@linghengqian I'm sure that it‘s not LOCAL transaction，because two datasources have different ip and port. By the way My program uses JPA.I feel very strange about this just like @ListenQ said.

[linghengqian]

@linghengqian I'm sure that it‘s not LOCAL transaction，because two datasources have different ip and port. By the way My program uses JPA.I feel very strange about this just like @ListenQ said.

• I believe an explanation of the LOCAL unsupported items is at https://shardingsphere.apache.org/document/current/en/features/transaction/limitations/ .

LOCAL Transaction
Unsupported
Does not support the cross-database transactions caused by network or hardware crash. For example, when updating two databases in transaction, if one database crashes before commit, then only the data of the other database can commit.

• Long story short, you can check if there is an implementation class of org.apache.shardingsphere.transaction.spi.ShardingSphereTransactionManager in the context of your personal project. For this class, the only known implementations are XA and Seata AT mode.

[linghengqian]

• You can use the master branch for testing. Modify local transactions to be executed in parallel #30462 and Fixes for unjudged Seata AT transactions caused by #30462 #30474 have restricted thread-unsafe cross-database transactions in the LOCAL transaction implementation.

[github-actions]

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

[linghengqian]

• Closed due to no reply.