Routed target `XXXX` does not exist, available targets are `[ds0, ds1]`.

Bug Report

Which version of ShardingSphere did you use?

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core</artifactId>
            <version>5.4.1</version>
        </dependency>

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-JDBC

Expected behavior

Successful routing and sharding in multithreaded situations

Actual behavior

When I want to enhance datax(DataX is an open-source tool for offline synchronization of heterogeneous data sources from Alibaba), the following error occurs, but it is successful occasionally

org.apache.shardingsphere.sharding.exception.algorithm.sharding.ShardingRouteAlgorithmException: Routed target `ds0` does not exist, available targets are `[cmp_contract_0, cmp_contract_2, cmp_contract_4, cmp_contract_6]`.
    at org.apache.shardingsphere.sharding.route.strategy.type.standard.StandardShardingStrategy.doSharding(StandardShardingStrategy.java:80)
    at org.apache.shardingsphere.sharding.route.strategy.type.standard.StandardShardingStrategy.doSharding(StandardShardingStrategy.java:64)
    at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.routeTables(ShardingStandardRoutingEngine.java:273)
    at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.route0(ShardingStandardRoutingEngine.java:252)
    at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.routeByShardingConditionsWithCondition(ShardingStandardRoutingEngine.java:142)
    at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.routeByShardingConditions(ShardingStandardRoutingEngine.java:135)
    at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.getDataNodes(ShardingStandardRoutingEngine.java:102)
    at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.route(ShardingStandardRoutingEngine.java:84)
    at org.apache.shardingsphere.sharding.route.engine.ShardingSQLRouter.createRouteContext0(ShardingSQLRouter.java:71)
    at org.apache.shardingsphere.sharding.route.engine.ShardingSQLRouter.createRouteContext(ShardingSQLRouter.java:59)
    at org.apache.shardingsphere.sharding.route.engine.ShardingSQLRouter.createRouteContext(ShardingSQLRouter.java:47)
    at org.apache.shardingsphere.infra.route.engine.impl.PartialSQLRouteExecutor.route(PartialSQLRouteExecutor.java:69)
    at org.apache.shardingsphere.infra.route.engine.SQLRouteEngine.route(SQLRouteEngine.java:57)
    at org.apache.shardingsphere.infra.connection.kernel.KernelProcessor.route(KernelProcessor.java:60)
    at org.apache.shardingsphere.infra.connection.kernel.KernelProcessor.generateExecutionContext(KernelProcessor.java:51)
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.createExecutionContext(ShardingSpherePreparedStatement.java:554)
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.addBatch(ShardingSpherePreparedStatement.java:642)

Reason analyze (If you can)

DataX is based on multi-threaded task execution. I think it is caused by thread synchronization issues.

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

This is the code for getting the data source

public static DataSource newInstance(File configDir) throws SQLException, ReflectiveOperationException, IOException {
        File yamlFile = ShardingConfigLoader.loadYamlFile(configDir);
        return YamlShardingSphereDataSourceFactory.createDataSource(yamlFile);
    }

This is the code for datax batch insert

protected void doBatchInsert(Connection connection, List<Record> buffer)
                throws SQLException {
            PreparedStatement preparedStatement = null;
            try {
                connection.setAutoCommit(false);
                preparedStatement = connection
                        .prepareStatement(this.writeRecordSql);

                for (Record record : buffer) {
                    preparedStatement = fillPreparedStatement(
                            preparedStatement, record);
                    preparedStatement.addBatch();
                }
                preparedStatement.executeBatch();
                connection.commit();
            } catch (SQLException e) {
                LOG.warn("回滚此次写入, 采用每次写入一行方式提交. 因为:" + e.getMessage());
                connection.rollback();
                doOneInsert(connection, buffer);
            } catch (Exception e) {
                throw DataXException.asDataXException(
                        DBUtilErrorCode.WRITE_DATA_ERROR, e);
            } finally {
                DBUtil.closeDBResources(preparedStatement, null);
            }
        }

The corresponding implementation of PreparedStatement is ShardingSpherePreparedStatement. Here is a debug screenshot This is my config file

dataSources:
  ds0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/db_lab_orders_0?useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
    username: root
    password: root
  ds1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/db_lab_orders_1?useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
    username: root
    password: root

rules:
  - !SHARDING
    tables:
      cmp_contract:
        actualDataNodes: ds0.cmp_contract_$->{[0,2,4,6]},ds1.cmp_contract_$->{[1,3,5,7]}

        databaseStrategy:
          standard:
            shardingColumn: ID
            shardingAlgorithmName: database_inline

        tableStrategy:
          standard:
            shardingColumn: ID
            shardingAlgorithmName: table_inline

    shardingAlgorithms:
      database_inline:
        type: INLINE
        props:
          algorithm-expression: ds${ID % 2}
      table_inline:
        type: INLINE
        props:
          algorithm-expression: cmp_contract_${ID % 8}

apache / shardingsphere