Ensuring Transaction Rollback in Multithreaded Spring MyBatis Operations

When inserting a massive amount of data, developers often split the workload across multiple threads to improve performance, but the @Transactional annotation does not work inside child threads, causing the main thread's changes to remain unrolled when an exception occurs.

Utility for Splitting Lists

/**
 * Average split list method.
 * @param source the original list
 * @param n number of sub‑lists
 * @return a list of sub‑lists
 */
public static <T> List<List<T>> averageAssign(List<T> source, int n) {
    List<List<T>> result = new ArrayList<>();
    int remainder = source.size() % n;
    int number = source.size() / n;
    int offset = 0;
    for (int i = 0; i < n; i++) {
        List<T> value;
        if (remainder > 0) {
            value = source.subList(i * number + offset, (i + 1) * number + offset + 1);
            remainder--;
            offset++;
        } else {
            value = source.subList(i * number + offset, (i + 1) * number + offset);
        }
        result.add(value);
    }
    return result;
}

Thread‑Pool Configuration

public class ExecutorConfig {
    private static int maxPoolSize = Runtime.getRuntime().availableProcessors();
    private volatile static ExecutorService executorService;

    public static synchronized ExecutorService getThreadPool() {
        if (executorService == null) {
            synchronized (ExecutorConfig.class) {
                if (executorService == null) {
                    executorService = newThreadPool();
                }
            }
        }
        return executorService;
    }

    private static ExecutorService newThreadPool() {
        int queueSize = 500;
        int corePool = Math.min(5, maxPoolSize);
        return new ThreadPoolExecutor(corePool, maxPoolSize, 10000L, TimeUnit.MILLISECONDS,
                new LinkedBlockingQueue<>(queueSize), new ThreadPoolExecutor.AbortPolicy());
    }
}

Why @Transactional Fails in Child Threads

A test case shows that when a delete operation is performed in the main thread and batch inserts are executed in parallel threads, an exception in any child thread does not roll back the delete, because the transaction context does not propagate to new threads.

Manual Transaction Control with SqlSession

@Resource
SqlContext sqlContext;

@Override
public void saveThread(List<EmployeeDO> employeeDOList) throws SQLException {
    SqlSession sqlSession = sqlContext.getSqlSession();
    Connection connection = sqlSession.getConnection();
    try {
        connection.setAutoCommit(false);
        EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
        employeeMapper.delete(null);
        ExecutorService service = ExecutorConfig.getThreadPool();
        List<Callable<Integer>> callableList = new ArrayList<>();
        List<List<EmployeeDO>> lists = averageAssign(employeeDOList, 5);
        for (List<EmployeeDO> list : lists) {
            Callable<Integer> callable = () -> employeeMapper.saveBatch(list);
            callableList.add(callable);
        }
        List<Future<Integer>> futures = service.invokeAll(callableList);
        for (Future<Integer> future : futures) {
            if (future.get() <= 0) {
                connection.rollback();
                return;
            }
        }
        connection.commit();
        System.out.println("Add completed");
    } catch (Exception e) {
        connection.rollback();
        log.info("error", e);
        throw new ServiceException("002", "Exception occurred");
    } finally {
        connection.close();
    }
}

Mapper SQL for Batch Insert

<insert id="saveBatch" parameterType="List">
INSERT INTO employee (employee_id, age, employee_name, birth_date, gender, id_number, creat_time, update_time, status)
VALUES
<foreach collection="list" item="item" index="index" separator=",">
    (#{item.employeeId}, #{item.age}, #{item.employeeName}, #{item.birthDate}, #{item.gender}, #{item.idNumber}, #{item.creatTime}, #{item.updateTime}, #{item.status})
</foreach>
</insert>

Running the above code, if any thread throws an exception, the connection rolls back, and the delete operation is undone, confirming that the transaction is successful. When all threads succeed, the data is inserted and the transaction commits.