You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "H-Jason (via GitHub)" <gi...@apache.org> on 2024/04/27 15:38:38 UTC
[I] Deadlocks in multi DB when multi thread excute batchUpdate [shardingsphere]
H-Jason opened a new issue, #31038:
URL: https://github.com/apache/shardingsphere/issues/31038
## 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

--
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.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Deadlocks in multi DB when multi thread excute batchUpdate [shardingsphere]
Posted by "linghengqian (via GitHub)" <gi...@apache.org>.
linghengqian closed issue #31038: Deadlocks in multi DB when multi thread excute batchUpdate
URL: https://github.com/apache/shardingsphere/issues/31038
--
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
Re: [I] Deadlocks in multi DB when multi thread excute batchUpdate [shardingsphere]
Posted by "TherChenYang (via GitHub)" <gi...@apache.org>.
TherChenYang commented on issue #31038:
URL: https://github.com/apache/shardingsphere/issues/31038#issuecomment-2081432637
@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
--
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
Re: [I] Deadlocks in multi DB when multi thread excute batchUpdate [shardingsphere]
Posted by "H-Jason (via GitHub)" <gi...@apache.org>.
H-Jason commented on issue #31038:
URL: https://github.com/apache/shardingsphere/issues/31038#issuecomment-2149263218
> There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
I try to fix 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
Re: [I] Deadlocks in multi DB when multi thread excute batchUpdate [shardingsphere]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #31038:
URL: https://github.com/apache/shardingsphere/issues/31038#issuecomment-2146022169
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
--
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
Re: [I] Deadlocks in multi DB when multi thread excute batchUpdate [shardingsphere]
Posted by "H-Jason (via GitHub)" <gi...@apache.org>.
H-Jason commented on issue #31038:
URL: https://github.com/apache/shardingsphere/issues/31038#issuecomment-2094300135
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')");
}
}
`
--
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