Optimizing Bulk Database Updates with Manual Transactions and Multithreading in Spring/MyBatis

The author encountered a requirement to modify up to 50,000 rows in a database where batch or asynchronous updates were not supported, so an initial simple

@Test void updateStudent() { List<Student> allStudents = studentMapper.getAll(); allStudents.forEach(s -> { String newTeacher = "TNO_" + new Random().nextInt(100); s.setTeacher(newTeacher); studentMapper.update(s); }); }

loop took about 1 minute 54 seconds due to automatic transaction commits after each update.

By adding manual transaction management with

@Autowired DataSourceTransactionManager dataSourceTransactionManager; @Autowired TransactionDefinition transactionDefinition; @Test void updateStudentWithTrans() { List<Student> allStudents = studentMapper.getAll(); TransactionStatus ts = dataSourceTransactionManager.getTransaction(transactionDefinition); try { allStudents.forEach(s -> { String newTeacher = "TNO_" + new Random().nextInt(100); s.setTeacher(newTeacher); studentMapper.update(s); }); dataSourceTransactionManager.commit(ts); } catch (Throwable e) { dataSourceTransactionManager.rollback(ts); throw e; } }

the execution time dropped to roughly 24 seconds, a five‑fold improvement.

To further reduce latency, the author wrapped the update logic in a service and executed it with a fixed‑size thread pool. The test code creates

ExecutorService studentThreadPool = Executors.newFixedThreadPool(threadCount); CountDownLatch threadLatchs = new CountDownLatch(threadCount);

and distributes the data among threads. Benchmark results showed the fastest completion with 2–5 threads (≈15 s), while increasing the thread count beyond that yielded diminishing returns.

Because each thread opened its own transaction, consistency was a concern. The solution introduced two CountDownLatch objects—one for child threads and one for the main thread—to ensure all threads finish their work before the main thread triggers a final commit, with timeout handling to roll back on delays.

When the thread count exceeded the default HikariCP connection pool size, a CannotCreateTransactionException occurred. The author resolved this by increasing the pool size (e.g., spring.datasource.hikari.maximum-pool-size=100) and adjusting related pool parameters, after which higher thread counts executed successfully.

An alternative batch‑update technique using a single

UPDATE student, (SELECT 1 AS id, 'teacher_A' AS teacher UNION SELECT 2, 'teacher_A' ... ) AS new_teacher SET student.teacher = new_teacher.teacher WHERE student.id = new_teacher.id

statement was presented, noting that it requires the MySQL allowMultiQueries=true setting.

In summary, the article highlights that manual transaction control, careful multithreading (2–5 threads optimal), proper connection‑pool configuration, and, when possible, set‑based SQL updates can greatly improve bulk data‑modification performance.