Local transaction will be broken while max-connections-size-per-query greater than 1

[I-Love-China]

Bug Report

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

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

• ShardingSphere-JDBC

Expected behavior

• DriverExecutionPrepareEngine return one connection if statement in a local transaction

Actual behavior

• DriverExecutionPrepareEngine return more than one connection if max-connections-size-per-query greater than 1

Reason analyze (If you can)

• Absence of LocalTransactionManager

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

Demo project

• repo url: https://github.com/I-Love-China/shardingsphere
• branch: comment-global
• main entry: org.apache.shardingsphere.shardingspheredemo.service.HealthRecordServiceTest#test
• DDL: https://github.com/I-Love-China/shardingsphere/blob/comment-global/shardingsphere-demo/src/main/resources/sql/DDL.sql
• There will be a warning while running this unit test: expected connections: 1, actual connections: 3;

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

[FlyingZC]

Thanks for your feedback,now only the same connection is used on the same data source in the same transaction in the XA transaction, even if multiple connections are configured by max-connections-size-per-query. But local transactions lack the relevant design and guarantees.

[FlyingZC]

@strongduanmu I want to investigate the issue,please assign me.

[strongduanmu]

Assgined.

[I-Love-China]

BTW, there will be three identical connection in a distributed transaction context, is this okay, will all resources be closed properly ?

[github-actions[bot]]

Hello , this issue has not received a reply for several days. This issue is supposed to be closed.

[FlyingZC]

Hi,@I-Love-China By the pr https://github.com/apache/shardingsphere/pull/21207. It guarantees that one transaction on a dataSource use the same connection, but may cause more memory consumption, because due to the limit of the number of connections, it may cause that streaming loading cannot be used, and memory loading is used.Do you have any better suggestion for modification.

[I-Love-China]

#21207

No, TRANSACTION is most important of all, one connection to one transaction is the expected behavior. @terrymanu Do you have any suggestions, boss ？

[I-Love-China]

Hi,@I-Love-China By the pr #21207. It guarantees that one transaction on a dataSource use the same connection, but may cause more memory consumption, because due to the limit of the number of connections, it may cause that streaming loading cannot be used, and memory loading is used.Do you have any better suggestion for modification.

BTW, the default value of max-connections-size-per-query is 1.