tristaZero commented on issue #8736:
URL: https://github.com/apache/shardingsphere/issues/8736#issuecomment-755057420
Hi, community,
After some transaction tests on MySQL and a discussion with @xunyard, here
comes the conclusion.
1. We recommend Java developers considering `jdbc:mysql:loadbalance` (i.e.,
LoadBalancedConnectionProxy) to do query load-balance on ShardingProxy instead
of `F5`.
2. You are suggested to set `autocommit` = `on` to avoid unexpected
behaviors when loading balance.
3. My tests and debugging of ShardingSphere convinced me it is highly
possible there is a bug in `TransactionBackendHandlerFactory` when
``autocommit` = `off`,
```java
if (tclStatement instanceof SetAutoCommitStatement) {
if (((SetAutoCommitStatement) tclStatement).isAutoCommit()) {
return
backendConnection.getTransactionStatus().isInTransaction() ? new
TransactionBackendHandler(TransactionOperationType.COMMIT, backendConnection) :
new SkipBackendHandler();
}
return new
TransactionBackendHandler(TransactionOperationType.BEGIN, backendConnection);
}
```
When users execute set` autocommit=off`, ShardingSphere will return
`TransactionBackendHandler(TransactionOperationType.BEGIN, backendConnection)`.
However, MySQL has a different behavior with it. Plus, after setting`
autocommit=off`, MySQL still allows users to use another database, but
ShardingSphere doesn't keep up with it. (Line 93 in `BackendConnection`).
Besides, it stays the same with MySQL when setting `autocommit` again after
the first one (Implicit `commit`).
I have no idea about `XA` transaction, so @yu199195 could you give more
detail about these cases?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
[email protected]
