You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2021/07/30 08:25:47 UTC
[GitHub] [shardingsphere] jianliu opened a new issue #11583: transaction statement commit error when using shared thead pool for all connections and local transaction manager
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jianliu commented on issue #11583: transaction statement commit error while using shared thread pool and local transaction manager
Posted by GitBox <gi...@apache.org>.
jianliu commented on issue #11583:
URL: https://github.com/apache/shardingsphere/issues/11583#issuecomment-898443415
I have make a mistake when analyz the reason,because of missing an important info,between pre tps and next tps.
```
#first statements
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
context.flush
close resources because there is no transaction yet after commit
#the second sql by framework (hikari)
set autocommit=true-- sql1:thread10 this sql is request by datasource framework when connection is close(back to pool)
context.flush
close resources because there is no transaction at all
#the thrid statement
set autocommit=false -- sql1:thread6
select * from tablexx where sharding_key=? -- sql2:thread7
```
Here is the logs:
The second sql (setAutoCommit(true) between the first and the thrid statement send by framework cause this erorr,because it removes the third sql(setAutoCommit:false) `s connectionPostProcessor,and lead this error finally
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero closed issue #11583: transaction statement commit error while using shared thread pool and local transaction manager
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #11583:
URL: https://github.com/apache/shardingsphere/issues/11583
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jianliu commented on issue #11583: transaction statement commit error while using shared thread pool and local transaction manager
Posted by GitBox <gi...@apache.org>.
jianliu commented on issue #11583:
URL: https://github.com/apache/shardingsphere/issues/11583#issuecomment-889735513
I`d like to fix this,and I have test it
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org