Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

batchUpdate inserts one row at a time on MySQL

ErrorjdbcTemplate.batchUpdate() takes seconds for a few thousand rows

Short answer

By default MySQL Connector/J sends each statement of a JDBC batch separately, so batchUpdate costs one round trip per row. Add rewriteBatchedStatements=true to the JDBC URL and the driver rewrites batched INSERT statements into multi-row inserts. Also give Spring a sensible batch size.

  • Applies to Spring Framework 6, MySQL 8.4, MySQL Connector/J 9, Java 21
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Print the JDBC URL your app really uses and look for the property. Then count the INSERT statements the server receives for a batch of 1000 rows.

    grep -rn "jdbc:mysql" src/main/resources/
  2. 2Match what you see, then jump to its fix

    1. 1The URL has no rewriteBatchedStatements parameterrewriteBatchedStatements is still falseFix
    2. 2The property is on but UPDATE batches stay slowThe statements are UPDATE or mixed, not INSERTFix
    3. 3Memory grows during the call, or the server rejects the packet as too largeOne huge batch instead of sized chunksFix

What does the error look like?

  • jdbcTemplate.batchUpdate() takes seconds for a few thousand rows
  • MySQL general log shows one INSERT per row, not one multi-row INSERT

Cause 1 of 3

rewriteBatchedStatements is still false

1Check

The property is off by default. With it off, executeBatch() does not combine the rows.

SET GLOBAL general_log = 'ON';
-- run the batch, then count INSERT lines in the log, then switch it off again
SET GLOBAL general_log = 'OFF';

2Fix

Turn it on in the connection URL. Then the driver can use multi-queries and rewrite batched prepared INSERT and REPLACE statements into multi-values statements when executeBatch() is called.

# application.properties
spring.datasource.url=jdbc:mysql://localhost:3306/app?rewriteBatchedStatements=true

3Verify

Rerun the batch and time it; the general log now shows far fewer INSERT statements.

SELECT COUNT(*) FROM my_table;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

The statements are UPDATE or mixed, not INSERT

1Check

The documentation names INSERT and REPLACE prepared statements for rewriting into multi-values form. Other statement types go as multi-queries at best, so gains are smaller.

grep -rn "batchUpdate" src/main/java/

2Fix

For big updates, load the new values into a staging table with a batched INSERT, then run one set-based UPDATE ... JOIN.

INSERT INTO price_stage (id, price) VALUES (?, ?);
UPDATE product p JOIN price_stage s ON s.id = p.id SET p.price = s.price;

3Verify

Compare run time of the old loop and the two-step version on the same data.

SELECT COUNT(*) FROM price_stage;

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

One huge batch instead of sized chunks

1Check

The overload with a batchSize argument splits a collection into several JDBC batches, and returns int[][]. The plain overload sends the whole list as one batch.

grep -rn "batchUpdate(" src/main/java/

2Fix

Use the overload that takes a batch size and a ParameterizedPreparedStatementSetter, with a few hundred to a few thousand rows per chunk.

jdbcTemplate.batchUpdate(
    "INSERT INTO account (id, name) VALUES (?, ?)",
    accounts, 1000,
    (ps, a) -> { ps.setLong(1, a.id()); ps.setString(2, a.name()); });

3Verify

Watch heap use while the batch runs; it should stay flat.

jcmd <pid> GC.heap_info

Confirmed by docs.spring.io

Did this fix it?

How do I stop it coming back?

  • Put rewriteBatchedStatements=true in the base datasource URL of every service that bulk-writes to MySQL.
  • Remember the documented side effects: with ON DUPLICATE KEY UPDATE you get SUCCESS_NO_INFO instead of per-row counts.
  • Never build plain Statement batches from user input with this flag on; the docs warn about SQL injection.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Databases

MySQL refuses to delete a row because other rows point to it

SQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails

Error 1451 means at least one child row still references the parent row you are deleting or changing.

3 causes, each with a fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.