Sharding-jdbc transaction commit exception but update operation haven’t rollback #8015
Comments
|
hi， @laravelshao 3.x&4.x is out of date. |
Hi @kimmking, I want figure out why this happen, does this problem is known bug of version 3.x？ |
|
AbstractConnectionAdapter.commit will commit all cached physical connections, I guest your biz have triggered some logic to make the connection invalid, we have never received this problem before |
@cherrylzhao Thx, after checking application k8slog, we found when this exception happened, mysql throw out CommunicationsException，so maybe some druid configuration lead to this problem。 error message druid configuration |
|
hope this will help you, alibaba/druid#2299 |
@cherrylzhao thx |
|
After checking druid configuration, I haven’t find any problem. But yesterday my colleague says: Sharding-jdbc version under 3.0 has a problem which not Support batch INSERT, in my project sharding-jdbc version is 3.0.0.M1, so batch INSERT is support. But i'm wondering whether or not this batch update operation lead to this problem, and this version sharding-jdbc support or not. @cherrylzhao @kimmking |
|
Finally found out the reason, after checking sharding-jdbc release log, from version 3.0.0.M1 Support batch INSERT, like below: But for batch update operation we don't is support or not. After searching issues I found a issue about sharding jdbc do not support batch update.For batch update sharding-jdbc is not support and have no plan to support because this kind of SQL may be routed to multiple data nodes, which will cause distributed transaction problems. Using mybatis foreach like below： Actual SQL So, I change batch update operation to single update with loop. After changing, still have commit exception. |
In my druid configuration I have those params, after 180s force remove the connection, But sharding-jdbc cached physical connections, So I want to know does sharding-jdbc will be notified or not when druid remove the connection. @cherrylzhao |
Hi, if you physical connection may be removed , sharding-jdbc will not be notified .。 so you question is not support batch Insert？ |
|
From k8slog, I find when this exception(
For mysql wait_timeout time is 3600s, from above exception I find message "The last packet successfully received from the server was SHOW VARIABLES LIKE '%timeout%';
interactive_timeout 3600 ## MySQL服务器关闭交互式连接前等待的秒数
wait_timeout 3600 ## MySQL服务器关闭非交互连接之前等待的秒数I review druid DestroyTask code, from code i find three ways to remove connection:
// DruidDataSource
public class DestroyTask implements Runnable {
@Override
public void run() {
shrink(true);
if (isRemoveAbandoned()) {
removeAbandoned();
}
}
}
public void shrink(boolean checkTime) {
final List<DruidConnectionHolder> evictList = new ArrayList<DruidConnectionHolder>();
try {
lock.lockInterruptibly();
} catch (InterruptedException e) {
return;
}
try {
// 检查的数量为 = 5 - 5 = 0
final int checkCount = poolingCount - minIdle;
final long currentTimeMillis = System.currentTimeMillis();
for (int i = 0; i < poolingCount; ++i) {
DruidConnectionHolder connection = connections[i];
if (checkTime) {
if (phyTimeoutMillis > 0) {
long phyConnectTimeMillis = currentTimeMillis - connection.getTimeMillis();
if (phyConnectTimeMillis > phyTimeoutMillis) {
evictList.add(connection);
continue;
}
}
// 连接空闲时间
long idleMillis = currentTimeMillis - connection.getLastActiveTimeMillis();
// 空闲时间小于最小回收空闲时间则直接结束循环，这里有点疑惑为什么第一个小于就结束循环
// 作者回复是后进先出(https://github.com/alibaba/druid/pull/1713)
if (idleMillis < minEvictableIdleTimeMillis) {
break;
}
// 在这里有两种情况会被回收，第一种情况一直都不成立，第二种情况只有到空闲时间到7个小时才会成立
// 情况一：checkTime = true && i < 0 永远都是不成立
if (checkTime && i < checkCount) {
evictList.add(connection);
} else if (idleMillis > maxEvictableIdleTimeMillis) {
// 情况二：空闲时间大于最大回收空闲时间，我们在配置中没有配置，适用默认值(1000L * 60L * 60L * 7 ms)
evictList.add(connection);
}
} else {
if (i < checkCount) {
evictList.add(connection);
} else {
break;
}
}
}
int removeCount = evictList.size();
if (removeCount > 0) {
System.arraycopy(connections, removeCount, connections, 0, poolingCount - removeCount);
Arrays.fill(connections, poolingCount - removeCount, poolingCount, null);
poolingCount -= removeCount;
}
} finally {
lock.unlock();
}
for (DruidConnectionHolder item : evictList) {
Connection connection = item.getConnection();
JdbcUtils.close(connection);
destroyCount.incrementAndGet();
}
}Current druid cofiguration: jdbc.global.driverClassName = com.mysql.jdbc.Driver
jdbc.global.url = jdbc:mysql://......
# 配置初始化大小、最小、最大
jdbc.global.initialSize = 5
jdbc.global.minIdle = 5
jdbc.global.maxActive = 10
# 配置获取连接等待超时的时间
jdbc.global.maxWait = 60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
jdbc.global.timeBetweenEvictionRunsMillis = 60000
# 配置一个连接在池中最小生存的时间，单位是毫秒 默认十分钟
jdbc.global.minEvictableIdleTimeMillis = 300000
jdbc.global.validationQuery = SELECT 'x'
jdbc.global.testWhileIdle = true
jdbc.global.testOnBorrow = false
jdbc.global.testOnReturn = false
# 打开PSCache，并且指定每个连接上PSCache的大小，mysql设置为false
jdbc.global.poolPreparedStatements = false
jdbc.global.maxPoolPreparedStatementPerConnectionSize = 20Here I haven't configure "maxEvictableIdleTimeMillis", so maxEvictableIdleTimeMillis will use default value(1000L * 60L * 60L * 7 ms). More than mysql 1 hours wait_timeout time. So i add below configuration like below: ## 配置最大回收空闲时间
maxEvictableIdleTimeMillis = 1800000Restart application, after 1 hours still find TransactionSystemException with CommunicationsException, And testWhileIdle is not work(also means idleMillis small than maxEvictableIdleTimeMillis which i add). The first way to remove connection is configure phyTimeoutMillis, when phyConnectTimeMillis > phyTimeoutMillis will be removed. So i add physical connection timeout like below: # 配置物理连接超时时间(40分钟)
phyTimeoutMillis = 2400000Restart application, I haven't find this exception happend. And this problem is solved. But I'm confused why testWhileIdle is not work. |
I have solved this problem, first I thought this exception was belong to sharding-jdbc, actual was druid testWhileIdle is not work, when mysql physical connection timeout, for druid still think it's valid. |
|
@laravelshao There are no other questions, and we'll close this issue~ |
Which version of ShardingSphere did you use?
3.0.0.M1
Which project did you use? Sharding-JDBC or Sharding-Proxy?
Sharding-JDBC
Expected behavior
update operation success, transaction commit success, interface response success
Actual behavior
production environment update operation success(data already update&insert), Sharding-jdbc transaction commit error, service global exception handle error response fail
error detail
Reason analyze
When under update operation, there has some error, the transaction already commit success for MySQL because update data already update and insert, but sharding-jdbc occur "org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is java.sql.SQLException".
Then i copy same interface input param to development environment simulate the whole update process, which can update success and haven't transaction commit error. Also i have try exception situation by manual throw a biz exception, the update operaiton can rollback to original state.
the update operation code like below：
sharding configuration
other info
The text was updated successfully, but these errors were encountered: