used example【sharding--jdbc--cluster-zookeeper--base-seata】，but don't rollback

[dongoewang]

Bug Report

throw new SQLException(),don't roll back
For English only, other languages will not accept.

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.4.1

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

ShardingSphere-JDBC

Expected behavior

use example ,but don't roll back

Actual behavior

Reason analyze (If you can)

may be bug

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

i used Official demo，only modified ExampleService.insertData(only append 【throw new SQLException();】)

Example codes for reproduce this issue (such as a github link).

https://github.com/dongoewang/sharding--jdbc--cluster-zookeeper--base-seata.git

[dongoewang]

[dongoewang]

before you run example，please create demo_ds_0, demo_ds_1, demo_ds_2; then you should create table 【undo_log】。
script of undo_log:
CREATE TABLE IF NOT EXISTS undo_log
(
branch_id BIGINT NOT NULL COMMENT 'branch transaction id',
xid VARCHAR(128) NOT NULL COMMENT 'global transaction id',
context VARCHAR(128) NOT NULL COMMENT 'undo_log context,such as serialization',
rollback_info LONGBLOB NOT NULL COMMENT 'rollback info',
log_status INT(11) NOT NULL COMMENT '0:normal status,1:defense status',
log_created DATETIME(6) NOT NULL COMMENT 'create datetime',
log_modified DATETIME(6) NOT NULL COMMENT 'modify datetime',
UNIQUE KEY ux_undo_log (xid, branch_id)
) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COMMENT ='AT transaction mode undo table';
ALTER TABLE undo_log ADD INDEX ix_log_created (log_created);

[linghengqian]

• There may actually be a problem with the Example module, but the fact that I'm not a freemarker expert makes it difficult for me to fix it.
• At shardingsphere-jdbc 5.4.0 AT transaction rollback not taking effect #29712 (comment) , I did unit tests for the following events. These unit tests are basically equivalent to what Example does. See https://github.com/linghengqian/shardingsphere-seata-spring-boot-test .
  • Snapshot version of Apache ShardingSphere 5.5.0
  • Seata Server 2.0 and Seata Spring Boot Starter 2.0
  • Spring Boot 3.2.2
  • Three independent Postgresql Server 16.2 instances
  • GraalVM CE For JDK21
• Are you hoping to try to fix a problem with the example module or are you looking for an example of Seata integration? This will determine whether it is necessary to continue opening the current issue. There are some duplicate issues on Seata integration.

sdk install java 21.0.2-graalce
sdk use java 21.0.2-graalce

git clone [email protected]:apache/shardingsphere.git
cd ./shardingsphere/
git reset --hard de192a30c9460bb94385c2a88e766025b211f906
./mvnw clean install -Prelease -T1C -DskipTests -Djacoco.skip=true -Dcheckstyle.skip=true -Drat.skip=true -Dmaven.javadoc.skip=true

cd ../

git clone [email protected]:linghengqian/shardingsphere-seata-spring-boot-test.git
cd ./shardingsphere-seata-spring-boot-test/
./mvnw -e -T1C clean test

[linghengqian]

• For https://github.com/linghengqian/shardingsphere-seata-spring-boot-test , to modify it to MySQL integration, you only need to introduce org.testcontainers:mysql:1.19.5 and modify the related initialization Database group SQL.
• For the current Example module, I can only point out that the CI of the Example module is currently in a broken state, which leads to many Examples that may have to be temporarily created externally.

[dongoewang]

• For https://github.com/linghengqian/shardingsphere-seata-spring-boot-test , to modify it to MySQL integration, you only need to introduce org.testcontainers:mysql:1.19.5 and modify the related initialization Database group SQL.
• For the current Example module, I can only point out that the CI of the Example module is currently in a broken state, which leads to many Examples that may have to be temporarily created externally.

thanks for reply。i am looking for an example of Seata integration (sharing sphere 5.4.0), in order to integrate sharding-sphere-jdbc(5.4.0) into our project which have bean sued Seata。

[linghengqian]

• As a committer, investigating old versions is unprofitable because there is no commit to go back to. Have you seen anything unscientific in the current master branch?

[dongoewang]

• As a committer, investigating old versions is unprofitable because there is no commit to go back to. Have you seen anything unscientific in the current master branch?

I haven't been seeing anything unscientific in the current branch.I want to ask question：when current branch occurs err，can current branch rollback?can project of following link test the rollback function ?
【https://github.com/linghengqian/shardingsphere-seata-spring-boot-test 】

[linghengqian]

• You can see an example of manual operation at https://github.com/linghengqian/shardingsphere-seata-spring-boot-test/blob/master/src/main/java/com/lingh/shardingsphereseataspringboottest/commons/repository/AddressRepository.java#L130 . t_table_does_not_exist is a table that does not exist at all.

Connection connection = dataSource.getConnection();
        try {
            connection.setAutoCommit(false);
            connection.createStatement().executeUpdate("INSERT INTO t_address (address_id, address_name) VALUES (2024, 'address_test_2024')");
            connection.createStatement().executeUpdate("INSERT INTO t_table_does_not_exist (test_id_does_not_exist) VALUES (2024)");
            connection.commit();
        } catch (SQLException e) {
            connection.rollback();
        } finally {
            connection.setAutoCommit(true);
            connection.close();
        }
        try (
                Connection conn = dataSource.getConnection();
                ResultSet resultSet = conn.createStatement().executeQuery("SELECT * FROM t_address WHERE address_id = 2024")) {
            assertThat(resultSet.next(), is(false));
        }

• In fact, it is not limited to this operation, refer to https://shardingsphere.apache.org/document/current/en/user-manual/shardingsphere-jdbc/special-api/transaction/seata/#usage-restrictions .
• When operating ShardingSphere data sources, you need to operate real distributed transactions just like local transactions. Long story short, you should not use the Seata Java API when using ShardingSphere’s Seata integration.

1. Manually obtain the java.sql.Connection instance created from the ShardingSphere data source, and manually calling the setAutoCommit(), commit() and rollback() methods is allowed.
2. Using the Jakarta EE 8 javax.transaction.Transactional annotation on the function is allowed.
3. Using Jakarta EE 9/10’s jakarta.transaction.Transactional annotation on functions is allowed.
4. Using Spring Framework’s org.springframework.transaction.annotation.Transactional annotation on functions is allowed.
5. Using the io.seata.spring.annotation.GlobalTransactional annotation on the function is not allowed.
6. Manually create io.seata.tm.api.GlobalTransaction instance from io.seata.tm.api.GlobalTransactionContext, calling the begin(), commit() and rollback() methods of an io.seata.tm.api.GlobalTransaction instance is not allowed.

• These limitations originally came from the handling on the Seata side.

[linghengqian]

• Long story short, for various reasons, io.seata:seata-spring-boot-starter is being passed into private projects, but actually conflicts with ShardingSphere beige flags. You need to turn off Seata's auto-configuration class, like @SpringBootApplication(exclude = io.seata.spring.boot.autoconfigure.SeataAutoConfiguration.class). You also need to set seata.enable-auto-data-source-proxy to false in application.yaml.

• Just using io.seata:seata-all helps to make it easier to understand.

[dongoewang]

• Long story short, for various reasons, io.seata:seata-spring-boot-starter is being passed into private projects, but actually conflicts with ShardingSphere beige flags. You need to turn off Seata's auto-configuration class, like @SpringBootApplication(exclude = io.seata.spring.boot.autoconfigure.SeataAutoConfiguration.class). You also need to set seata.enable-auto-data-source-proxy to false in application.yaml.
• Just using io.seata:seata-all helps to make it easier to understand.

Thanks a lot.I want to ask only question:after integrating shardingsphere-seata, in order to rollback,only method is 【Manually obtain the java.sql.Connection instance created from the ShardingSphere data source, and manually calling rollback() methods】？If that's the case, it may not be very good，Because most people's habits：out-of-the-box

[linghengqian]

Thanks a lot.I want to ask only question:after integrating shardingsphere-seata, in order to rollback,only method is 【Manually obtain the java.sql.Connection instance created from the ShardingSphere data source, and manually calling rollback() methods】？

• In the context of web frameworks, you can set up rollback through JTA annotations, such as javax.transaction.Transactional, jakarta.transaction.Transactional and org.springframework.transaction.annotation.Transactional. Other JTA annotations for local transactions are also available, the only limitation is that you cannot use Seata Java APIs like io.seata.spring.annotation.GlobalTransactional. More elaboration in the context of SpringCloud+Seata1.5.2+ShardingSphereJDBC5.2.1 SeataATShardingSphereTransactionManager.commit throw null exception #22356 (comment) . Please ignore the description of @ShardingTransactionType, which is a deleted class.

但是这里要注意一个问题！！！！！！
虽然SeataATShardingTransactionManager和GlobalTransactionScanner做的事情一样，但是二者绝对不能混合使用！！！！！！
也就是说@ShardingTransactionType(TransactionType.BASE)和@GlobalTransactional绝对不能同时出现！！！！！！
看过上面SeataATShardingTransactionManager你就能明白，ShardingSphere把获取到的全局事务，放到了线程的局部变量里面了。而GlobalTransactionScanner则是采用动态代理的方式对方法进行增强。根本不能放在一起，杂交是要出问题的！
ok！最终总结一句！
只要你的项目中用到sharding-jdbc，那么你全部微服务都需要导入seata整合sharding-jdbc的jar包
而且，你的项目中千万不能再出现@GlobalTransactional注解了，用SeataATShardingTransactionManager就可以了。

• You cannot set the isolation level of a transaction because this is a limitation imposed by Seata-AT.
• The web frameworks I am talking about include but are not limited to Spring Boot OSS, Quarkus, Micronaut and Helidon. Using JTA annotations like javax.transaction.Transactional requires the existence of an IOC concept.

[dongoewang]

Thanks a lot.I want to ask only question:after integrating shardingsphere-seata, in order to rollback,only method is 【Manually obtain the java.sql.Connection instance created from the ShardingSphere data source, and manually calling rollback() methods】？

• In the context of web frameworks, you can set up rollback through JTA annotations, such as javax.transaction.Transactional, jakarta.transaction.Transactional and org.springframework.transaction.annotation.Transactional. Other JTA annotations for local transactions are also available, the only limitation is that you cannot use Seata Java APIs like io.seata.spring.annotation.GlobalTransactional. More elaboration in the context of SpringCloud+Seata1.5.2+ShardingSphereJDBC5.2.1 SeataATShardingSphereTransactionManager.commit throw null exception #22356 (comment) . Please ignore the description of @ShardingTransactionType, which is a deleted class.

但是这里要注意一个问题！！！！！！
虽然SeataATShardingTransactionManager和GlobalTransactionScanner做的事情一样，但是二者绝对不能混合使用！！！！！！
也就是说@ShardingTransactionType(TransactionType.BASE)和@GlobalTransactional绝对不能同时出现！！！！！！
看过上面SeataATShardingTransactionManager你就能明白，ShardingSphere把获取到的全局事务，放到了线程的局部变量里面了。而GlobalTransactionScanner则是采用动态代理的方式对方法进行增强。根本不能放在一起，杂交是要出问题的！
ok！最终总结一句！
只要你的项目中用到sharding-jdbc，那么你全部微服务都需要导入seata整合sharding-jdbc的jar包
而且，你的项目中千万不能再出现@GlobalTransactional注解了，用SeataATShardingTransactionManager就可以了。

• You cannot set the isolation level of a transaction because this is a limitation imposed by Seata-AT.
• The web frameworks I am talking about include but are not limited to Spring Boot OSS, Quarkus, Micronaut and Helidon. Using JTA annotations like javax.transaction.Transactional requires the existence of an IOC concept.

I have two questions，after we integrated shardingsphere-seata
(sorry,question become more.)
question 1：
for example，there are two micro service：project A ；project B
project A
@org.springframework.transaction.annotation.Transactiona
public test2(){
updateTable1（）table1 of db1
projectb.test3()
}
project b
@org.springframework.transaction.annotation.Transactiona
public test3(){
updateTable2（）//table2 of db2
throw exception;
}
when running projectA.test2(),projectb.test3 throw exception.can SeataATShardingTransactionManager notify project A to rollback?
question 2：
[只要你的项目中用到sharding-jdbc，那么你全部微服务都需要导入seata整合sharding-jdbc的jar包]:
project a use SeataATShardingTransactionManager,can other project use GlobalTransactionScanner?

[linghengqian]

project a use SeataATShardingTransactionManager,can other project use GlobalTransactionScanner?

• The answer is yes, as long as you avoid using both ShardingSphere's Seata-AT integration and Seata's Java API within the same project. It sounds like project A is using ShardingSphere's Seata-AT integration, and then project B is using Seata's TCC mode. The connection between them is only possible through project A's registry.conf and project B's application.yml, because Seata's TCC mode must use things within the Spring Framework.
• When project A uses ShardingSphere's Seata AT integration, I will not recommend that you configure the equivalent content of registry.conf in application.yaml, because this is currently an undefined behavior. You are welcome to submit in the master branch of ShardingSphere unit test.

when running projectA.test2(),projectb.test3 throw exception.can SeataATShardingTransactionManager notify project A to rollback?

• This issue may not seem like something that can be handled on the ShardingSphere side at first glance, as it definitely depends on the Seata Server. You can take a look at https://github.com/apache/shardingsphere/blob/a4632ab6140698b3e4950df4f66169caa8c65bb9/kernel/transaction/type/base/seata-at/src/main/java/org/apache/shardingsphere/transaction/base/seata/at/SeataATShardingSphereTransactionManager.java.

• Determining whether a piece of code is in an open transaction state depends on the return value of io.seata.core.context.RootContext.getXID(). This requires quoting documentation for https://seata.apache.org/zh-cn/docs/user/api/#1-%E8%BF%9C%E7%A8%8B%E8%B0%83%E7%94%A8%E4%BA%8B%E5%8A%A1%E4%B8%8A%E4%B8%8B%E6%96%87%E7%9A%84%E4%BC%A0%E6%92%AD . For the most part I will say that Seata's English documentation seems not quite ready yet and is weird to read.

1. 远程调用事务上下文的传播
   远程调用前获取当前 XID：
   String xid = RootContext.getXID();
   远程调用过程把 XID 也传递到服务提供方，在执行服务提供方的业务逻辑前，把 XID 绑定到当前应用的运行时：
   RootContext.bind(rpcXid);
2. 事务的暂停和恢复
   在一个全局事务中，如果需要某些业务逻辑不在全局事务的管辖范围内，则在调用前，把 XID 解绑：
   String unbindXid = RootContext.unbind();
   待相关业务逻辑执行完成，再把 XID 绑定回去，即可实现全局事务的恢复：
   RootContext.bind(unbindXid);

• Classification discussion.

  • 1. This means that if you are using ShardingSphere JDBC, project A may not be notified when project B throws an exception, but I am not sure if two projects use the same transaction group of Seata , whether the Seata Server side will initiate notifications to other Seata Clients in the transaction group.
  • 2. If the Seata AT mode is integrated with a ShardingSphere Proxy instance, and project A and project B initiate transaction requests to this ShardingSphere Proxy instance through local transactions in Java or Python, it seems that this problem should not exist.

• Anyway, at least I am not an Apache Seata Committer. I would rather suggest that you open an issue at https://github.com/apache/incubator-seata with documentation issues. I'm not even an original contributor to the current integration implementation of Seata AT mode, I just wrote the corresponding integration tests for it under GraalVM Native Image.

[dongoewang]

This means that if you are using ShardingSphere JDBC, project A may not be notified when project B throws an exception, but I am not sure if two projects use the same transaction group of Seata , whether the Seata Server side will initiate notifications to other Seata Clients in the transaction group.

1:two projects use the same transaction group of Seata;
2:it seems like the Seata Server side don't initiate notifications to other Seata Clients in the transaction group

If the Seata AT mode is integrated with a ShardingSphere Proxy instance, and project A and project B initiate transaction requests to this ShardingSphere Proxy instance through local transactions in Java or Python, it seems that this problem should not exist.

1:through debug, i knows the Seata AT mode is integrated with a ShardingSphere Proxy instance.
2:i don't know whether or not project A and project B initiate transaction requests to this ShardingSphere Proxy instance through local transactions in Java or Python @linghengqian

[linghengqian]

2:it seems like the Seata Server side don't initiate notifications to other Seata Clients in the transaction group

• This is a missing extension point thing as it is difficult to pass in the Seata XID obtained from RPC in the context of a class. Or if you have ideas, you can try to submit PR processing. It seems that you must manually call RootContext.bind(rpcXid); in the logical data source using ShardingSphere. Maybe there are some better extension points on Seata that can be used.

2:i don't know whether or not project A and project B initiate transaction requests to this ShardingSphere Proxy instance through local transactions in Java or Python @linghengqian

• Transactions always need to be started manually. On the Java side, you can use JTA annotations like org.springframework.transaction.annotation.Transactional or dataSource.getConnection().setAutoCommit(false). On the Python or Golang side, it seems that each ORM framework starts transactions differently.

[dongoewang]

2:it seems like the Seata Server side don't initiate notifications to other Seata Clients in the transaction group

• This is a missing extension point thing as it is difficult to pass in the Seata XID obtained from RPC in the context of a class. Or if you have ideas, you can try to submit PR processing. It seems that you must manually call RootContext.bind(rpcXid); in the logical data source using ShardingSphere. Maybe there are some better extension points on Seata that can be used.

2:i don't know whether or not project A and project B initiate transaction requests to this ShardingSphere Proxy instance through local transactions in Java or Python @linghengqian

• Transactions always need to be started manually. On the Java side, you can use JTA annotations like org.springframework.transaction.annotation.Transactional or dataSource.getConnection().setAutoCommit(false). On the Python or Golang side, it seems that each ORM framework starts transactions differently.

thanks a lot .i will try.as long as i have answer,i will write the information here.

[dongoewang]

2:it seems like the Seata Server side don't initiate notifications to other Seata Clients in the transaction group

• This is a missing extension point thing as it is difficult to pass in the Seata XID obtained from RPC in the context of a class. Or if you have ideas, you can try to submit PR processing. It seems that you must manually call RootContext.bind(rpcXid); in the logical data source using ShardingSphere. Maybe there are some better extension points on Seata that can be used.

2:i don't know whether or not project A and project B initiate transaction requests to this ShardingSphere Proxy instance through local transactions in Java or Python @linghengqian

• Transactions always need to be started manually. On the Java side, you can use JTA annotations like org.springframework.transaction.annotation.Transactional or dataSource.getConnection().setAutoCommit(false). On the Python or Golang side, it seems that each ORM framework starts transactions differently.

thanks a lot .i will try.as long as i have answer,i will write the information here.