Optimizing Large‑Scale MySQL Updates with Manual Transactions and Multithreading in Spring Boot

Introduction

The business requirement is to modify up to 50,000 rows in a MySQL table, but the existing system lacks batch or asynchronous update support, so a naïve for‑loop was used, resulting in long execution time.

1. Simple Loop

A basic JUnit test iterates over all Student entities and updates the teacher field one by one; the test took about 1 minute 54 seconds.

/***
 * Update 50,000 rows one by one
 * Time: 2m27s, 1m54s
 */
@Test
void updateStudent() {
    List
allStudents = studentMapper.getAll();
    allStudents.forEach(s -> {
        // update teacher
        String newTeacher = "TNO_" + new Random().nextInt(100);
        s.setTeacher(newTeacher);
        studentMapper.update(s);
    });
}

2. Manual Transaction

By injecting DataSourceTransactionManager and TransactionDefinition, the whole loop runs inside a single transaction, reducing the time to about 24 seconds (≈5× faster).

@Autowired
private DataSourceTransactionManager dataSourceTransactionManager;

@Autowired
private TransactionDefinition transactionDefinition;

/**
 * Manual transaction improves speed
 * Time: 24s
 */
@Test
void updateStudentWithTrans() {
    List
allStudents = studentMapper.getAll();
    TransactionStatus txStatus = dataSourceTransactionManager.getTransaction(transactionDefinition);
    try {
        allStudents.forEach(s -> {
            String newTeacher = "TNO_" + new Random().nextInt(100);
            s.setTeacher(newTeacher);
            studentMapper.update(s);
        });
        dataSourceTransactionManager.commit(txStatus);
    } catch (Throwable e) {
        dataSourceTransactionManager.rollback(txStatus);
        throw e;
    }
}

3. Multithreaded Update

A StudentServiceImpl groups the update logic, and a test creates a fixed thread pool, partitions the data, and lets each thread call the service; a CountDownLatch waits for all threads to finish.

@Service
public class StudentServiceImpl implements StudentService {

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

    @Override
    public void updateStudents(List
students, CountDownLatch threadLatch) {
        TransactionStatus txStatus = dataSourceTransactionManager.getTransaction(transactionDefinition);
        System.out.println("Thread: " + Thread.currentThread().getName());
        try {
            students.forEach(s -> {
                String newTeacher = "TNO_" + new Random().nextInt(100);
                s.setTeacher(newTeacher);
                studentMapper.update(s);
            });
            dataSourceTransactionManager.commit(txStatus);
            threadLatch.countDown();
        } catch (Throwable e) {
            dataSourceTransactionManager.rollback(txStatus);
        }
    }
}

4. Thread‑Count Experiments

Running the multithreaded test with different numbers of threads shows that 2–5 threads give the best throughput; beyond that the connection pool becomes a bottleneck.

5. Two CountDownLatch Coordination

To ensure all threads finish their work before committing, a second latch is introduced; the main thread releases it after all child threads have signaled, then each thread commits or rolls back based on a shared error flag.

@Override
public void updateStudentsThread(List
students,
                                 CountDownLatch threadLatch,
                                 CountDownLatch mainLatch,
                                 StudentTaskError taskStatus) {
    TransactionStatus txStatus = dataSourceTransactionManager.getTransaction(transactionDefinition);
    System.out.println("Thread: " + Thread.currentThread().getName());
    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(txStatus);
    } else {
        dataSourceTransactionManager.commit(txStatus);
    }
}

6. TransactionStatus Collection

An alternative approach stores each thread’s TransactionStatus in a synchronized list; after all threads finish, the main thread iterates the list to commit or roll back uniformly.

@Service
public class StudentsTransactionThread {

    @Autowired
    private StudentMapper studentMapper;
    @Autowired
    private StudentService studentService;
    @Autowired
    private PlatformTransactionManager transactionManager;

    List
transactionStatuses = Collections.synchronizedList(new ArrayList<>());

    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = {Exception.class})
    public void updateStudentWithThreadsAndTrans() throws InterruptedException {
        List
allStudents = studentMapper.getAll();
        final int threadCount = 2;
        final int dataPartionLength = (allStudents.size() + threadCount - 1) / threadCount;

        ExecutorService pool = Executors.newFixedThreadPool(threadCount);
        CountDownLatch threadLatch = new CountDownLatch(threadCount);
        AtomicBoolean isError = new AtomicBoolean(false);

        for (int i = 0; i < threadCount; i++) {
            List
part = allStudents.stream()
                    .skip(i * dataPartionLength)
                    .limit(dataPartionLength)
                    .collect(Collectors.toList());
            pool.execute(() -> {
                try {
                    studentService.updateStudentsTransaction(transactionManager, transactionStatuses, part);
                } catch (Throwable e) {
                    isError.set(true);
                } finally {
                    threadLatch.countDown();
                }
            });
        }

        boolean await = threadLatch.await(30, TimeUnit.SECONDS);
        if (!await) {
            isError.set(true);
        }

        if (!transactionStatuses.isEmpty()) {
            if (isError.get()) {
                transactionStatuses.forEach(s -> transactionManager.rollback(s));
            } else {
                transactionStatuses.forEach(s -> transactionManager.commit(s));
            }
        }
        System.out.println("Main thread completed");
    }
}

7. UNION‑Based Batch Update

When MySQL does not support multi‑row UPDATE, the data can be assembled with a UNION of SELECT statements and joined to the target table; enabling allowMultiQueries=true makes this work.

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
    union
    select 4 as id, 'teacher_A' as teacher
    /* ...more data... */
) as new_teacher
set student.teacher = new_teacher.teacher
where student.id = new_teacher.id;

Conclusion

Manual transaction control dramatically speeds up bulk updates.

Multithreading improves performance only up to a modest thread count (2‑5 in the test).

Excessive threads exhaust the JDBC connection pool and cause timeouts.

If possible, use true batch UPDATE (or UNION‑based SQL) for the best results.