Boosting Massive Data Updates in Spring Boot: Manual Transactions, Multithreading, and UNION Batch Tricks

Introduction

In a business scenario we need to modify up to 50,000 rows in a MySQL table using Spring Boot and MyBatis, but the system does not support batch or asynchronous updates, so a simple for‑loop is used, which takes too long.

Loop without manual transaction

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

The loop takes about 1 min 54 s because each update is committed automatically.

Adding manual transaction

@Autowired
private DataSourceTransactionManager dataSourceTransactionManager;
@Autowired
private TransactionDefinition transactionDefinition;

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

Execution time drops to about 24 s, roughly five times faster.

Multithreaded update with CountDownLatch

We wrap the update logic in a service and launch several threads, each handling a partition of the data. A CountDownLatch synchronises the threads and the main thread.

@Test
void updateStudentWithThreads() {
    List<Student> allStudents = studentMapper.getAll();
    int threadCount = 100;
    int dataPartionLength = (allStudents.size() + threadCount - 1) / threadCount;
    ExecutorService pool = Executors.newFixedThreadPool(threadCount);
    CountDownLatch latch = new CountDownLatch(threadCount);
    for (int i = 0; i < threadCount; i++) {
        List<Student> part = allStudents.stream()
                .skip(i * dataPartionLength).limit(dataPartionLength).collect(Collectors.toList());
        pool.execute(() -> {
            studentService.updateStudents(part, latch);
        });
    }
    latch.await(30, TimeUnit.SECONDS);
    System.out.println("Main thread completed");
}

Testing shows the best performance around 2‑5 threads; more threads do not improve speed and may cause connection‑pool exhaustion.

Controlling multithreaded transactions with two CountDownLatch

Each thread starts its own transaction, performs updates, then waits for the main latch before committing or rolling back based on an error flag.

public void updateStudentsThread(List<Student> students,
                                   CountDownLatch threadLatch,
                                   CountDownLatch mainLatch,
                                   StudentTaskError taskStatus) {
    TransactionStatus ts = dataSourceTransactionManager.getTransaction(transactionDefinition);
    try {
        students.forEach(s -> {
            String newTeacher = "TNO_" + new Random().nextInt(100);
            s.setTeacher(newTeacher);
            studentMapper.update(s);
        });
    } catch (Throwable e) {
        taskStatus.setIsError();
    } finally {
        threadLatch.countDown();
    }
    try {
        mainLatch.await();
    } catch (Throwable e) {
        taskStatus.setIsError();
    }
    if (taskStatus.getIsError()) {
        dataSourceTransactionManager.rollback(ts);
    } else {
        dataSourceTransactionManager.commit(ts);
    }
}

Using a collection of TransactionStatus

All thread‑local TransactionStatus objects are stored in a synchronized list; after all threads finish, the main thread commits or rolls back the whole collection.

Batch update via UNION

If the database does not support native batch UPDATE, we can build a derived table with UNION of multiple SELECT statements and join it to the target table.

UPDATE student, (
    (SELECT 1 AS id, 'teacher_A' AS teacher)
    UNION
    (SELECT 2 AS id, 'teacher_A' AS teacher)
    UNION
    (SELECT 3 AS id, 'teacher_A' AS teacher)
    /* …more rows… */
) AS new_teacher
SET student.teacher = new_teacher.teacher
WHERE student.id = new_teacher.id;

Summary

Manual transaction control can dramatically improve bulk update performance.

Multithreading speeds up processing only up to a moderate thread count (2‑5 in the test).

When many threads hold connections, the connection pool may be exhausted, causing timeout errors.

If possible, use true batch UPDATE; otherwise UNION‑based updates are a viable alternative.