-
Notifications
You must be signed in to change notification settings - Fork 6.6k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Deadlocks in multi DB when multi thread excute batchUpdate #31038
Comments
@H-Jason Thank you very much for your feedback, Can you provide a test demo? This can help me locate the problem faster.
|
okay,It is easy to reproduce this bug;this is my demo;
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')");
}
} |
H-Jason
added a commit
to H-Jason/shardingsphere
that referenced
this issue
May 5, 2024
H-Jason
added a commit
to H-Jason/shardingsphere
that referenced
this issue
May 5, 2024
H-Jason
added a commit
to H-Jason/shardingsphere
that referenced
this issue
May 12, 2024
H-Jason
added a commit
to H-Jason/shardingsphere
that referenced
this issue
May 12, 2024
H-Jason
added a commit
to H-Jason/shardingsphere
that referenced
this issue
May 20, 2024
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale. |
H-Jason
added a commit
to H-Jason/shardingsphere
that referenced
this issue
Jun 4, 2024
H-Jason
added a commit
to H-Jason/shardingsphere
that referenced
this issue
Jun 5, 2024
H-Jason
added a commit
to H-Jason/shardingsphere
that referenced
this issue
Jun 5, 2024
6 tasks
6 tasks
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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
The text was updated successfully, but these errors were encountered: