Spring Boot预约服务PESSIMISTIC_WRITE锁范围效率问题咨询

问题描述 #

我用Spring Boot开发预约服务，用户点击预约时，通过PESSIMISTIC_WRITE悲观锁检查剩余名额，但被指出锁范围设置不够高效。查资料发现多数场景都会用这种锁做名额检查，但我不清楚问题出在哪。想确认是不是select count统计剩余名额导致的？如果是这个原因，能不能通过修改数据库设计优化？如果不是，希望明确具体问题。目前并发控制看似有效，但想确认锁范围是否合理。

当前代码实现 #

业务层（AppointmentsService） #

@Service
public class AppointmentsService {

    @Transactional
    public ResponseEntity<?> createReservation(AppointmentsDTO appointmentsDTO, String userId) {
        // 省略其他逻辑

        // 检查预约时段可用性 - 悲观锁
        if (!isTimeSlotAvailable(appointmentsDTO.getHospitalId(), appointmentsDTO.getDate(), appointmentsDTO.getTime())) {
            throw new CustomValidationException(HttpStatus.BAD_REQUEST.value(), "无法预约");
        }

        appointmentsRepository.save(convertDtoToEntity(appointmentsDTO, userId));
    }

    // 检查可用性方法 - 悲观锁
    @Transactional(readOnly = true)
    boolean isTimeSlotAvailable(String hospitalId, LocalDate date, LocalTime time) {
        // int seat = availableTimeService.calculateAvailableSlots(hospitalId, date, time);
        int seat = 3 - appointmentsRepository.countAppointmentsForTimeSlot(hospitalId, date, time);
        return seat > 0;
    }
}

数据访问层（AppointmentsRepository） #

@Repository
public interface AppointmentsRepository extends JpaRepository<Appointments, String> {

    @Query("SELECT COUNT(a) FROM Appointments a WHERE a.hospital.businessId = :businessId AND a.appointmentDate = :appointmentDate AND a.appointmentTime = :appointmentTime")
    int countByHospital_BusinessIdAndAppointmentDateAndAppointmentTime(@Param("businessId") String businessId, @Param("appointmentDate") LocalDate appointmentDate, @Param("appointmentTime") LocalTime appointmentTime);

    @Lock(LockModeType.PESSIMISTIC_WRITE) 
    @QueryHints({ @QueryHint(name = javax.persistence.lock.timeout, value = "5000") })
    @Query("SELECT COUNT(a) FROM Appointments a WHERE a.hospital.businessId = :businessId AND a.appointmentDate = :appointmentDate AND a.appointmentTime = :startTime")
    int countAppointmentsForTimeSlot(@Param("businessId") String businessId,
                                     @Param("appointmentDate") LocalDate appointmentDate,
                                     @Param("startTime") LocalTime startTime);
}

问题分析 #

1. 锁范围过大且存在逻辑漏洞 #

你用PESSIMISTIC_WRITE锁countAppointmentsForTimeSlot查询时，数据库会锁定所有符合条件的Appointments记录。如果某个时段已有多条预约，锁的范围就是这些行的集合，并发高时会导致大量请求阻塞，效率低下。
更严重的是：如果该时段还没有任何预约记录，count返回0，此时没有任何行被锁定，多个并发请求会同时通过可用性检查，最终导致超卖（比如同时插入4条预约，超过3个名额的限制）。

2. count查询的性能问题 #

每次检查都要统计符合条件的行数，当数据量较大时，count查询的性能会比直接读取已预约数差很多，进一步降低并发效率。

优化方案：调整数据库设计+锁单一行 #

核心思路是把"时段名额"抽象成独立的实体，用单一行的锁来控制并发，避免锁定大量预约记录。

1. 新增TimeSlotQuota表 #

创建专门存储时段名额的表，结构示例：

CREATE TABLE time_slot_quota (
    id VARCHAR(32) PRIMARY KEY,
    hospital_business_id VARCHAR(32) NOT NULL,
    appointment_date DATE NOT NULL,
    appointment_time TIME NOT NULL,
    total_quota INT NOT NULL DEFAULT 3, -- 该时段总名额
    booked_count INT NOT NULL DEFAULT 0, -- 已预约数
    UNIQUE KEY uk_hospital_date_time (hospital_business_id, appointment_date, appointment_time)
);

提前初始化所有可预约时段的名额记录，或者在首次预约时自动创建对应时段的记录。

2. 修改并发控制逻辑 #

新增TimeSlotQuotaRepository

@Repository
public interface TimeSlotQuotaRepository extends JpaRepository<TimeSlotQuota, String> {

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @QueryHints({@QueryHint(name = javax.persistence.lock.timeout, value = "5000")})
    Optional<TimeSlotQuota> findByHospitalBusinessIdAndAppointmentDateAndAppointmentTime(
            @Param("hospitalBusinessId") String hospitalBusinessId,
            @Param("appointmentDate") LocalDate appointmentDate,
            @Param("appointmentTime") LocalTime appointmentTime);
}

修改业务层逻辑

@Service
public class AppointmentsService {

    @Autowired
    private TimeSlotQuotaRepository timeSlotQuotaRepository;

    @Transactional
    public ResponseEntity<?> createReservation(AppointmentsDTO appointmentsDTO, String userId) {
        String hospitalId = appointmentsDTO.getHospitalId();
        LocalDate date = appointmentsDTO.getDate();
        LocalTime time = appointmentsDTO.getTime();

        // 锁定对应时段的名额记录
        TimeSlotQuota quota = timeSlotQuotaRepository.findByHospitalBusinessIdAndAppointmentDateAndAppointmentTime(hospitalId, date, time)
                .orElseThrow(() -> new CustomValidationException(HttpStatus.BAD_REQUEST.value(), "该时段不可预约"));

        // 检查剩余名额
        if (quota.getTotalQuota() - quota.getBookedCount() <= 0) {
            throw new CustomValidationException(HttpStatus.BAD_REQUEST.value(), "名额已满");
        }

        // 更新已预约数
        quota.setBookedCount(quota.getBookedCount() + 1);
        timeSlotQuotaRepository.save(quota);

        // 保存预约记录
        appointmentsRepository.save(convertDtoToEntity(appointmentsDTO, userId));
    }
}

优化后的优势 #

• 锁范围极小：每次只锁定TimeSlotQuota表中的单一行，不管该时段有多少预约记录，并发阻塞的概率大幅降低，效率显著提升。
• 避免超卖：即使时段无预约记录，只要TimeSlotQuota行存在，就能被锁定，不会出现并发请求同时通过检查的情况。
• 性能更优：直接读取booked_count字段，比count查询的性能更高。

内容的提问来源于stack exchange，提问作者장현희