在有事务的方法中，有时候我们需要对这个方法进行性能优化，性能优化的措施采用多线程并发处理，不同的线程，在mybatis中创建的sqlSession不同，导致多线程之间同一时刻不能使用同一个connection（数据库的session），通过@Transactional实现事务处理会导致多线程执行的sql不会同时进行事务的回滚

如下是connection，transation,sqlSession之间的关系：

链接可以通过数据库链接池被复用。在MyBatis中，不同时刻的SqlSession可以复用同一个Connection，同一个SqlSession中可以提交多个事务。因此，链接—会话—事务的关系如下：

共用一个sqlSession，则共用一个connection

代码实现如下：

声明线程池：

/**
 * 单例线程池工具
 * @author yangrui
 * @date 2022/9/22 10:28
 */
public class ThreadPoolSingletonUtils {

    //创建私有化静态线程池属性
    private static ThreadPoolExecutor threadPoolExecutor = null;

    /**
     * 获取公有的线程池
     * @return
     */
    public static ThreadPoolExecutor getThreadPoolExecutor(){
        //为null和被shutdown都实例化对象
        if(threadPoolExecutor==null||threadPoolExecutor.isShutdown()){
            synchronized (ThreadPoolSingletonUtils.class){
                if(threadPoolExecutor==null||threadPoolExecutor.isShutdown()){
                    //当前队列最大可以容纳50个任务做处理
                    threadPoolExecutor= new ThreadPoolExecutor(
                            32,
                            64,
                            2, TimeUnit.MINUTES,
                            new LinkedBlockingQueue<Runnable>(5000),
                            new ThreadPoolExecutor.CallerRunsPolicy()/*如果超过队列的将用主线程进行任务的执行*/);
                }
            }
        }
        return threadPoolExecutor;
    }
}

获取mybatis会话：

@Component
public class SqlContext {
    @Resource
    private SqlSessionTemplate sqlSessionTemplate;

    public SqlSession getSqlSession(){
        SqlSessionFactory sqlSessionFactory = sqlSessionTemplate.getSqlSessionFactory();
        return sqlSessionFactory.openSession();
    }
}

具体实现类：

@Slf4j
@Service
public class TestServiceImpl {

    @Resource
    SqlContext sqlContext;

    public void test() throws  SQLException {

        SqlSession sqlSession = sqlContext.getSqlSession();
        Connection connection = sqlSession.getConnection();
        TestMapper employeeMapper = sqlSession.getMapper(TestMapper.class);
        try {
            // 设置手动提交
            connection.setAutoCommit(false);
            employeeMapper.insert();
            List<Future> futureList = new ArrayList<>();
            futureList.add(ThreadPoolSingletonUtils.getThreadPoolExecutor().submit(()->{
                employeeMapper.update();
            }));
            futureList.add(ThreadPoolSingletonUtils.getThreadPoolExecutor().submit(()->{
                employeeMapper.update1();
            }));
            for(Future future : futureList){
                future.get();
            }
            connection.commit();
        }catch (Exception e){
            connection.rollback();
            log.error(e.getMessage(),e);
        }
    }
}

多个SqlSession，多个connection

上述虽然有多线程，但是共用一个connection，不能达到并发执行sql的作用，还是串行执行sql，如果需要并发执行sql，需要在mybatis中创建多个SqlSession，则对应多个connection，则代码如下：

package com.mysteel.oem.framework.service.impl;

import com.banksteel.commons.lang.CommonException;
import com.mysteel.oem.framework.mapper.TestMapper;
import com.mysteel.oem.framework.utils.SqlContext;
import com.mysteel.oem.framework.utils.ThreadPoolSingletonUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSession;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.Callable;
import java.util.concurrent.Future;
@Slf4j
@Service
public class TestServiceImpl {

    @Resource
    SqlContext sqlContext;

    public void test() throws  SQLException {
        SqlSession sqlSession = sqlContext.getSqlSession();
        Connection connection = sqlSession.getConnection();
        TestMapper employeeMapper = sqlSession.getMapper(TestMapper.class);
        try {
            // 设置手动提交
            connection.setAutoCommit(false);
            employeeMapper.insert();
            test1();
            connection.commit();
        }catch (Exception e){
            connection.rollback();
            log.error(e.getMessage(),e);
        }
    }

    void test1() throws SQLException {
        SqlSession sqlSession = sqlContext.getSqlSession();
        Connection connection = sqlSession.getConnection();
        TestMapper employeeMapper = sqlSession.getMapper(TestMapper.class);
        try{
            connection.setAutoCommit(false);
            List<Future<Integer>> futureList = ThreadPoolSingletonUtils.getThreadPoolExecutor().invokeAll(getCallableList(employeeMapper));
            for(Future future : futureList){
                future.get();
            }
            connection.commit();
        }catch (Exception e){
            connection.rollback();
            log.error(e.getMessage(),e);
            throw new CommonException("101","抛出异常");
        }
    }

    List<Callable<Integer>>   getCallableList( TestMapper employeeMapper){
        List<Callable<Integer>> callableList = new ArrayList<>();
        callableList.add(()->{ return employeeMapper.update();});
        callableList.add(()->{ return employeeMapper.update1();});
        return callableList;
    }
}

以上代码，亲测多线程执行的sql可以进行回滚