如何在Java Spring Boot中实现带锁的数据库条件插入保障数据一致性

针对同一用户单日预约不超过3次的需求，在高并发场景下避免数据不一致，同时规避悲观锁的死锁风险，推荐以下两种可靠方案：

方案一：原子化SQL插入（最优解） #

利用SQLite的原子性操作，将「查询当日预约数+条件插入」合并为一条SQL语句，从数据库层面保证操作的不可分割性，完全避免并发冲突。

步骤1：扩展Repository接口 #

添加原生SQL的原子插入方法，通过INSERT ... SELECT实现条件判断：

@Repository
public interface AppointmentRepository extends JpaRepository<Appointment, Long> {
    @Modifying
    @Query(nativeQuery = true, value = """
        INSERT INTO appointment (person, appointment_date)
        SELECT :person, :appointmentDate
        WHERE (SELECT COUNT(*) FROM appointment 
               WHERE person = :person AND DATE(appointment_date) = DATE(:appointmentDate)) < 3
    """)
    int insertIfUnderLimit(@Param("person") String person, @Param("appointmentDate") Date appointmentDate);

    // 查询刚插入的预约记录（用于返回结果）
    Optional<Appointment> findTopByPersonAndAppointmentDateOrderByIdDesc(String person, Date appointmentDate);
}

步骤2：修改服务层逻辑 #

通过受影响行数判断插入是否成功，失败则抛出提示：

@Service
public class AppointmentService {
    @Autowired
    private AppointmentRepository appointmentRepository;

    public Appointment saveAppointment(Appointment appointment) {
        int affectedRows = appointmentRepository.insertIfUnderLimit(
            appointment.getPerson(), 
            appointment.getAppointment_date()
        );
        
        if (affectedRows == 0) {
            throw new IllegalArgumentException("同一用户单日预约不得超过3次");
        }

        // 返回刚插入的预约记录
        return appointmentRepository.findTopByPersonAndAppointmentDateOrderByIdDesc(
            appointment.getPerson(), 
            appointment.getAppointment_date()
        ).orElseThrow(() -> new RuntimeException("预约记录保存失败"));
    }
}

步骤3：优化控制器返回 #

捕获异常并返回友好响应：

@RestController
public class AppointmentController {
    @Autowired
    private AppointmentService appointmentService;

    @PostMapping("/store")
    public ResponseEntity<?> store(@RequestBody Appointment appointment) {
        try {
            Appointment saved = appointmentService.saveAppointment(appointment);
            return ResponseEntity.ok(saved);
        } catch (IllegalArgumentException e) {
            return ResponseEntity.badRequest().body(e.getMessage());
        } catch (RuntimeException e) {
            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body("服务器异常");
        }
    }
}

关键配置 #

开启SQLite的WAL模式提升并发性能（支持行级锁），在application.properties中添加：

spring.datasource.url=jdbc:sqlite:your-appointment-db.db?journal_mode=WAL

方案二：事务包裹查询+插入（兼容JPA标准） #

如果偏好JPA标准操作而非原生SQL，可以通过事务包裹查询与插入逻辑，结合SQLite的事务隔离级别保证一致性：

步骤1：扩展Repository的计数方法 #

@Repository
public interface AppointmentRepository extends JpaRepository<Appointment, Long> {
    @Query("SELECT COUNT(a) FROM Appointment a WHERE a.person = :person AND DATE(a.appointment_date) = DATE(:appointmentDate)")
    long countDailyAppointments(@Param("person") String person, @Param("appointmentDate") Date appointmentDate);
}

步骤2：服务层添加事务与逻辑判断 #

@Service
@Transactional(isolation = Isolation.REPEATABLE_READ)
public class AppointmentService {
    @Autowired
    private AppointmentRepository appointmentRepository;

    public Appointment saveAppointment(Appointment appointment) {
        long count = appointmentRepository.countDailyAppointments(
            appointment.getPerson(), 
            appointment.getAppointment_date()
        );
        
        if (count >= 3) {
            throw new IllegalArgumentException("同一用户单日预约不得超过3次");
        }

        return appointmentRepository.save(appointment);
    }
}

注：SQLite默认事务隔离级别为SERIALIZABLE，已经足够保证事务内的查询一致性，此处显式指定REPEATABLE_READ是为了明确语义。

方案对比 #

• 方案一（原子SQL）：性能最优，完全依赖数据库原子操作，无需事务额外控制，是高并发场景下的首选。
• 方案二（事务包裹）：符合JPA标准写法，代码更易读，但依赖事务隔离级别，在极端高并发下性能略逊于方案一。

内容的提问来源于stack exchange，提问作者Ahmet Uğur