shardingsphere
shardingsphere copied to clipboard
apache
Deadlocks in multi DB when multi thread excute batchUpdate
Bug Report
Which version of ShardingSphere did you use?
5.1.2
Which project did you use?
ShardingSphere-JDBC
Expected behavior
when i update table in multi mysql using batchUpdate in JdbcTemplate ,it can be executed normally。 i have two same table in two mysql，and allocate data based on odd and even numbers of primary key IDs; for example ,id with 1,3,5 in the first DB, and id with 2,4,6 in second db ;
` String sql = "Update mytable set name = ? where id = ?";
List<Object[]> batchArgs = new ArrayList<>();
batchArgs.add(new Object[]{"value1", idone});
batchArgs.add(new Object[]{"value3", idtwo});
int[] updateCounts = jdbcTemplate.batchUpdate(sql, batchArgs); `
It will deadlock in multi db when multi thread excuting these code;
Actual behavior
Deadlocks
Reason analyze
I think the bug happen in AbstractExecutionPrepareEngine. I update my data, ID is odd in DB1,ID is even in DB2 In this AbstractExecutionPrepareEngine ,the line 83 used LinkedHashMap instead of TreeMap,so the bug happen; As shown in the following figure
@H-Jason Thank you very much for your feedback, Can you provide a test demo? This can help me locate the problem faster.
- This problem seems more like a usage problem, where transactions on both threads fail to commit will cause a deadlock
okay,It is easy to reproduce this bug;this is my demo; It must hapen "timeout"
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [update MY_TABLE set name = ? where ID = ?]; SQL state [HYT00]; error code [50200]; Timeout trying to lock table "MY_TABLE"; SQL statement:
update MY_TABLE set name = ? where ID = ? [50200-224]
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1540)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:691)
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1034)
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1088)
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1079)
at my.test.ShardingTest.lambda$extracted$2(ShardingTest.java:50)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
at my.test.ShardingTest.extracted(ShardingTest.java:45)
at my.test.ShardingTest.lambda$main$0(ShardingTest.java:31)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: org.h2.jdbc.JdbcBatchUpdateException: Timeout trying to lock table "MY_TABLE"; SQL statement:
package my.test;
import my.h2.H2DbUtil;
import org.springframework.jdbc.support.JdbcTransactionManager;
import org.springframework.transaction.support.TransactionTemplate;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
public class ShardingTest {
public static void main(String[] args) {
JdbcTransactionManager manager1 = new JdbcTransactionManager(H2DbUtil.SHARDING_DATA_SOURCE);
manager1.setDefaultTimeout(10);
TransactionTemplate template1 = new TransactionTemplate(manager1);
ExecutorService service1 = Executors.newFixedThreadPool(10);
ExecutorService service2 = Executors.newFixedThreadPool(10);
for (int i = 0; i < 10; i++) {
service1.submit(() -> {
extracted(template1, Arrays.asList(2, 3, 4));
});
}
for (int i = 0; i < 10; i++) {
service2.submit(() -> {
extracted(template1, Arrays.asList(1, 2, 3, 4));
});
}
}
private static void extracted(TransactionTemplate template1, List<Integer> list) {
try {
int[] result = template1.execute(k -> {
List<Object[]> batchArgs = new ArrayList<>();
for (Integer each : list) {
batchArgs.add(new Object[]{"i am " + each, each});
}
return H2DbUtil.shardJdbcTemplate.batchUpdate("update MY_TABLE set name = ? where ID = ?", batchArgs);
});
System.out.println(result);
} catch (Throwable e) {
e.printStackTrace();
}
}
}
package my.h2;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory;
import org.apache.shardingsphere.infra.config.algorithm.ShardingSphereAlgorithmConfiguration;
import org.apache.shardingsphere.infra.config.props.ConfigurationPropertyKey;
import org.apache.shardingsphere.sharding.api.config.ShardingRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.rule.ShardingTableRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.strategy.keygen.KeyGenerateStrategyConfiguration;
import org.apache.shardingsphere.sharding.api.config.strategy.sharding.NoneShardingStrategyConfiguration;
import org.apache.shardingsphere.sharding.api.config.strategy.sharding.ShardingStrategyConfiguration;
import org.apache.shardingsphere.sharding.api.config.strategy.sharding.StandardShardingStrategyConfiguration;
import javax.sql.DataSource;
import java.util.*;
public class H2DbBuilder {
public static DataSource getH2DataSource(String dbName) {
DruidDataSource source = new DruidDataSource();
try {
String sourceURL = "jdbc:h2:mem:" + dbName + ";MODE=MYSQL";//H2DB mem mode
try {
Class.forName("org.h2.Driver");//HSQLDB Driver
} catch (Exception e) {
e.printStackTrace();
}
source.setUrl(sourceURL);
source.setUsername(dbName);
} catch (Exception e) {
e.printStackTrace();
}
return source;
}
public static DataSource getShardingDataSource() {
try {
ShardingRuleConfiguration ruleConfiguration = createShardingRuleConfiguration();
Map<String, DataSource> dataSourceMap = new HashMap<>();
dataSourceMap.put("DB0", H2DbUtil.DB_2);
dataSourceMap.put("DB1", H2DbUtil.DB_1);
Properties properties = new Properties();
properties.setProperty("sql-show", "true");
return ShardingSphereDataSourceFactory.createDataSource("test", dataSourceMap, Collections.singleton(ruleConfiguration), properties);
} catch (Exception e) {
e.printStackTrace();
}
return new DruidDataSource();
}
private static ShardingRuleConfiguration createShardingRuleConfiguration() {
ShardingRuleConfiguration result = new ShardingRuleConfiguration();
result.getTables().add(getMyTableRuleConfiguration());
result.setDefaultDatabaseShardingStrategy(new StandardShardingStrategyConfiguration("ID", "my"));
result.setDefaultTableShardingStrategy(new NoneShardingStrategyConfiguration());
result.getBindingTableGroups().add("MY_TABLE");
Properties props = new Properties();
props.setProperty("algorithm-expression", "DB${ID % 2}");
result.getShardingAlgorithms().put("my", new ShardingSphereAlgorithmConfiguration("INLINE", props));
return result;
}
private static ShardingTableRuleConfiguration getMyTableRuleConfiguration() {
ShardingTableRuleConfiguration result = new ShardingTableRuleConfiguration("MY_TABLE", "DB${0..1}.MY_TABLE");
return result;
}
}
package my.h2;
import my.data.DataPrepareUtil;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
public class H2DbUtil {
public static final DataSource DB_1 = H2DbBuilder.getH2DataSource("DB1");
public static JdbcTemplate templateDb1 = new JdbcTemplate(DB_1);
public static final DataSource DB_2 = H2DbBuilder.getH2DataSource("DB2");
public static JdbcTemplate templateDb2 = new JdbcTemplate(DB_2);
static {
DataPrepareUtil.prepareDate();
}
public static final DataSource SHARDING_DATA_SOURCE = H2DbBuilder.getShardingDataSource();
public static JdbcTemplate shardJdbcTemplate = new JdbcTemplate(SHARDING_DATA_SOURCE);
}
package my.data;
import my.h2.H2DbUtil;
public class DataPrepareUtil {
public static void prepareDate() {
H2DbUtil.templateDb1.execute("CREATE TABLE IF NOT EXISTS MY_TABLE (ID INT PRIMARY KEY,NAME VARCHAR(255))");
H2DbUtil.templateDb1.execute("INSERT INTO MY_TABLE VALUES(1, 'i am 1')");
H2DbUtil.templateDb1.execute("INSERT INTO MY_TABLE VALUES(3, 'i am 3')");
H2DbUtil.templateDb2.execute("CREATE TABLE IF NOT EXISTS MY_TABLE (ID INT PRIMARY KEY,NAME VARCHAR(255))");
H2DbUtil.templateDb2.execute("INSERT INTO MY_TABLE VALUES(2, 'i am 2')");
H2DbUtil.templateDb2.execute("INSERT INTO MY_TABLE VALUES(4, 'i am 4')");
}
}
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
![github-actions[bot] avatar](https://avatars.githubusercontent.com/in/15368?v=4 "Published by a pub.dev verified publisher"){kind=small}