jianliu opened a new issue #11583: URL: https://github.com/apache/shardingsphere/issues/11583
## Bug Report I found a concurrency issues when exeucte transaction statement sqls. here is my sql statemtns(MySQL) ``` set autocommit=false select * from tablexx where sharding_key=? update tablexx set x=x where sharding_key=? commit ``` the client create 200 connections to the proxy and concurrently execute this transaction statements to get the TPS data As above，here is 4 statements the first is a begin statement and the LocalTransactionManager will add connectionPostProcessors to invoke the real connection`s setAutoCommit(false) once a real connection is borrow from datasource in future the last statement commit will invoke the real connection`s commit method to submit the transaction ps: statement is execute in the thread pool: UserExecutorGroup.getInstance().getExecutorService(),it is a shard pool for all commandExecutorTask ### Which version of ShardingSphere did you use? 5.0.0-RC1-SNAPSHOT ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy? ShardingSphere-Proxy ### Expected behavior Every transaction statement commit success and affect to the MySQL ### Actual behavior It is hard to find the error,according to my tests,only 0.015% probability to get the error below: Can't call commit when autocommit=true ### Reason analyze (If you can) As I analyzed , the reason has two parts 1.BackendConnection`s field connectionPostProcessors is not thread safe  although this is not the main reason,but it`s a potential issue the 4 statement will execute serially along a connection. let's imagine the 4 statement is execute in multiple threads ``` set autocommit=false -- sql1:thread1 select * from tablexx where sharding_key=? -- sql2:thread2 update tablexx set x=x where sharding_key=? -- sql3:thread3 commit -- sql4:thread4 ``` set autocommit=false will let the BackendConnection`s localTransactionManager add a connectionPostProcessors to invoke the real connection`s setAutoCommit(false) once a real connection is borrow from datasource when sql2 execute in thread 2, a real connection will be get and apply the connectionPostProcessors,but may not see the latest connectionPostProcessors ,cause the real connection never execute setAutoCommit(false) 2.CommandExecutorTask`s method run has sequence problem I think this sequence problem cause concurrency issues on a single connection,and it's dangerous  code1 will lead the client get response immediately，and client know this transaction is finished and could reuse the connection to run the next statement. code2 clear the resources under the backendConncection like clean cachedConnections and connectionPostProcessors (etc.)when transaction is finished so what happened ? because code1 flush the response in thread4,the client send next statement "set autocommit=false" for another transaction,and thread6 execute it and add a connectionPostProcessors,then thread4 run the code2,clear the resources include thread6 just created ``` set autocommit=false -- sql1:thread1 select * from tablexx where sharding_key=? -- sql2:thread2 update tablexx set x=x where sharding_key=? -- sql3:thread3 commit -- sql4:thread4 #next sql statement set autocommit=false -- sql1:thread6 select * from tablexx where sharding_key=? -- sql2:thread7 ... ``` It is a sequence problem,but make the object backendConnection be modified concurrently. ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. This is a problem that can be analyzed and concluded and hard to reproduce unless rewrite the code ,and a sleep between code1 and code2 ### Example codes for reproduce this issue (such as a github link). -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
