Deadlocks in multi DB when multi thread excute batchUpdate

[H-Jason]

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

[TherChenYang]

@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

[H-Jason]

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')");
    }
}