Optimizing Large-Scale Database Updates with Manual Transactions and Multithreading in Spring Boot MyBatis

In a Spring Boot‑MyBatis project that stores student data in MySQL, the author needed to modify about 50,000 rows, which a naïve for‑loop took nearly two minutes.

First a basic loop test was written:

/***
 * 一条一条依次对50000条数据进行更新操作
 * 耗时：2m27s,1m54s
 */
@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);
    });
}

Adding manual transaction control reduced the time to about 24 seconds:

@Autowired
private DataSourceTransactionManager dataSourceTransactionManager;

@Autowired
private TransactionDefinition transactionDefinition;

/**
 * 由于希望更新操作一次性完成，需要手动控制添加事务
 * 耗时：24s
 */
@Test
void updateStudentWithTrans() {
    List<Student> allStudents = studentMapper.getAll();
    TransactionStatus transactionStatus = dataSourceTransactionManager.getTransaction(transactionDefinition);
    try {
        allStudents.forEach(s -> {
            String teacher = s.getTeacher();
            String newTeacher = "TNO_" + new Random().nextInt(100);
            s.setTeacher(newTeacher);
            studentMapper.update(s);
        });
        dataSourceTransactionManager.commit(transactionStatus);
    } catch (Throwable e) {
        dataSourceTransactionManager.rollback(transactionStatus);
        throw e;
    }
}

To further improve speed, a multithreaded service was introduced. The service receives a list of students and a CountDownLatch, opens a transaction, updates each record, commits, and counts down the latch.

@Service
public class StudentServiceImpl implements StudentService {
    @Autowired
    private StudentMapper studentMapper;
    @Autowired
    private DataSourceTransactionManager dataSourceTransactionManager;
    @Autowired
    private TransactionDefinition transactionDefinition;

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

A test harness creates a fixed thread pool, partitions the 50 000 rows, and submits each partition to the service. Results showed that increasing the thread count does not linearly improve performance; the best time (≈15 s) was observed with 2–5 threads, while larger pools caused connection‑pool exhaustion.

When many threads each hold a JDBC connection, the HikariCP pool timed out. The author solved this by raising spring.datasource.hikari.maximum-pool-size to 100 and adjusting other pool parameters.

# 连接池中允许的最小连接数。缺省值：10
spring.datasource.hikari.minimum-idle=10
# 连接池中允许的最大连接数。缺省值：10
spring.datasource.hikari.maximum-pool-size=100
# 自动提交
spring.datasource.hikari.auto-commit=true
# 一个连接idle状态的最大时长（毫秒），超时则被释放（retired），缺省:10分钟
spring.datasource.hikari.idle-timeout=30000
# 一个连接的生命时长（毫秒），超时而且没被使用则被释放（retired），缺省:30分钟，建议设置比数据库超时时长少30秒
spring.datasource.hikari.max-lifetime=1800000
# 等待连接池分配连接的最大时长（毫秒），超过这个时长还没可用的连接则发生SQLException， 缺省:30秒

Another approach groups the transaction statuses in a synchronized list and commits or rolls back them after all threads finish, using Propagation.REQUIRES_NEW to isolate each thread’s transaction.

@Service
public class StudentsTransactionThread {
    @Autowired
    private StudentMapper studentMapper;
    @Autowired
    private StudentService studentService;
    @Autowired
    private PlatformTransactionManager transactionManager;

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

    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = {Exception.class})
    public void updateStudentWithThreadsAndTrans() throws InterruptedException {
        List<Student> allStudents = studentMapper.getAll();
        final Integer threadCount = 2;
        final Integer dataPartionLength = (allStudents.size() + threadCount - 1) / threadCount;
        ExecutorService studentThreadPool = Executors.newFixedThreadPool(threadCount);
        CountDownLatch threadLatchs = new CountDownLatch(threadCount);
        AtomicBoolean isError = new AtomicBoolean(false);
        for (int i = 0; i < threadCount; i++) {
            List<Student> threadDatas = allStudents.stream()
                    .skip(i * dataPartionLength).limit(dataPartionLength).collect(Collectors.toList());
            studentThreadPool.execute(() -> {
                try {
                    studentService.updateStudentsTransaction(transactionManager, transactionStatuses, threadDatas);
                } catch (Throwable e) {
                    e.printStackTrace();
                    isError.set(true);
                } finally {
                    threadLatchs.countDown();
                }
            });
        }
        boolean await = threadLatchs.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("主线程完成");
    }
}

When the database does not support multi‑row UPDATE, the author shows a UNION‑based technique that builds a derived table of id‑teacher pairs and updates the target table in a single statement.

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;

In summary, manual transaction boundaries dramatically speed up bulk updates, multithreading yields optimal performance only with a modest number of threads, connection‑pool sizing must match the concurrency level, and native batch‑update or UNION tricks are preferable when available.