CODEWITHSUNDEEP
multithreadingspring-bootspring-jdbchikaricp
Willem
Willem

Reputation: 43

Problem with database access using multithreading JDBC create/update/insert queries

I have a multi-threaded Spring Boot application which inserts a vast amount of data into a MySQL. When the application starts, a single thread is running to persist the "main" data. This works well. After the "main" data is persisted, multiple threads are started to insert data into various sub-schemas (also MySQL), suing the data persisted by the "main" (a type of replication process with complex rules).

What I observe are strange SQL errors, which do not occur when I run the application single-threaded, so I am suspecting that multi-threading is causing the issue.

None of these seem to help. I am getting errors like: - Bad SQL grammar (which is not the case, as I said, single threaded works) - empty SQL statement (which is not possible)

I hope that anyone on this forum can shed some like on this

Things I have tried: - making the DAO which I use to insert data into the sub-schema a prototype bean, so there is one distinct DAO per thread - making the NamedParameterJDBCTemplate (used in the DAO) a prototype bean - making the DataSource used by the NamedParameterJdbcTemplate a prototype bean

===>>> Configuration

// Target data source
@Bean(name = "targerDSProps")
@ConfigurationProperties(prefix = "target.server")
public DataSourceProperties targetDSProps(){
    return new DataSourceProperties();
}

@Bean(name = "targetDS")
@ConfigurationProperties(prefix = "target.server.configuration")
@Scope(BeanDefinition.SCOPE_PROTOTYPE)
public DataSource targetDS(){
    return targetDSProps().initializeDataSourceBuilder().type(HikariDataSource.class).build();
}

@Bean(name = "targetNamedParameterJdbcTemplate")
public NamedParameterJdbcTemplate targetJdbcTemplate(@Qualifier("targetDS") DataSource targetDS) {
    return new NamedParameterJdbcTemplate(targetDS);
}

===>>> The DAO

@Component
@Scope(BeanDefinition.SCOPE_PROTOTYPE)
@Slf4j
public class TargetDAO {

    @Autowired
    @Qualifier(value = "targetNamedParameterJdbcTemplate")
    private NamedParameterJdbcTemplate namedTargetJdbcTemplate;

None of these seem to help. I am getting errors like: - Bad SQL grammar (which is not the case, as I said, single threaded works) - empty SQL statement (which is not possible)

Upvotes: 0

Views: 455

Answers (1)

Willem
Willem

Reputation: 43

I found the problem. I was using an enum to dynamically build queries, forgetting that enums are static by nature. I replaced this by a map, it works now.

Upvotes: 1

Related Questions