mybatis集成数据库锁表

直接上代码

package course.service.impl;
 
import course.entity.Course;
import course.entity.CourseDetail;
import course.mapper.CourseDetailMapper;
import course.mapper.CourseMapper;
import course.mapper.StudentMapper;
import course.service.CourseService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;
 
import java.util.Objects;
 

@Service
public class CourseServiceImpl implements CourseService {
    private Logger logger = LoggerFactory.getLogger(CourseServiceImpl.class);
 
    @Autowired
    StudentMapper studentMapper;
 
    @Autowired
    CourseMapper courseMapper;
 
    @Autowired
    CourseDetailMapper courseDetailMapper;
 
 
    /**使用for update一定要加上这个事务
     * 当事务处理完后，for update才会将行级锁解除*/
    @Transactional(isolation = Isolation.READ_COMMITTED)
    // @Transactional(value = "testTransactionManager") //如果是多数据源，需要制定数据源
    @Override
    public Object chooseCourse(String studentCode, Integer courseId) {
 
        /** courseMapper.queryAllById(courseId)会对所选中的那条记录加行级锁，其他线程会在此排队，当事务提交后，才会进行解锁*/
        Course course = courseMapper.queryAllById(courseId);
 
        int electiveNum = course.getElectiveNum();
        int totalNum = course.getElectiveTotal();
        logger.info("After Lock Step 1, Thread: {},courseId{}, studentId: {}, electiveNum: {}, total: {}", Thread.currentThread(),courseId,studentCode, electiveNum, totalNum);
 
        if (Objects.isNull(course)){
            return "课程不存在";
        }
        if (electiveNum >= totalNum) {
            return "此课程已被选完";
        }
 
        /**将此此学生的选课信息保存到选课详情里面*/
        CourseDetail courseDetail = new CourseDetail();
        courseDetail.setCourseId(courseId);
        courseDetail.setStudentCode(studentCode);
        courseDetailMapper.insert(courseDetail);
 
        /**将course表中的electiveNum进行加1操作
         * 使用sql进行累加更加安全，因为使用方法开始查询的course中的electiveNum,并不一定是数据库存储的值*/
        courseMapper.addElectiveNumByCourseId(courseId);
        return "选课成功";
    }
}

主要是Transactional注解的使用 @Transactional(isolation = Isolation.READ_COMMITTED)

 

选择READ_COMMITTED注解的话，无论条件列上是否有索引，都不会锁表，只锁行

Read Uncommited和Read Committed不存在间隙锁所以不会存在锁表行为

 

具体详情可参考:
https://blog.csdn.net/zc_ad/article/details/83582614
https://blog.51cto.com/14230003/2427994