CODEWITHSUNDEEP
javaspring-dataspring-jdbcspring-transactions
era
era

Reputation: 481

Multi threaded transactional inserts with spring data jdbc

I'm using NamedParameterJdbcTemplate. I need to inserts data to 5 different tables within a transaction.

The sequential execution of inserts take long time & I need to optimize the time taken for inserts.

One possible option is make all inserts parallel using threads. As far as I understood transaction is not propagate to multi threads.

How can I improve time taken for this operation within a transaction boundary ?

Upvotes: 0

Views: 880

Answers (1)

Jens Schauder
Jens Schauder

Reputation: 81882

I don't think what you are trying to do can possibly work. As far as I know a database transaction is always bound to a single connection. And the JDBC connection API is blocking, i.e. you can only execute a single statement at a time. So even when you share the Spring transaction across multiple threads you'll still execute your SQL sequential.

I therefore see the following options which might be combined available to you:

  1. Tune your database/SQL: batched inserts, disabled constraints, adding or removing indexes and so one might have a effect on the execution time.
  2. Drop the transactional constraint. If you can break your process into multiple processes you might be able to run them in parallel and actually gaining performance.
  3. Tune/parallelise the part happening in your Java application so you can do other stuff while your SQL statements are running.

To decide which approach is most promising we'd need to know more about your actual scenario.

Upvotes: 2

Related Questions