spring-data-jpa 如何在Java Spring中进行带锁的条件数据库插入操作？

v64noz0r 于 2022-11-10 发布在 Spring

关注(0)|答案(2)|浏览(222)

我正在尝试对数据库进行条件插入操作（假设为SQLite）。我的目标是确保数据一致性。
假设我们正在制定一个约会计划。
因此，我们有一个约会表，如下所示：

标识符
个人的
约会日期

然而，我们有一个重要的规则。一个人不能在同一天进行超过3次预约。
通常我们从数据库中查询这个人那天有多少个约会，然后我们决定是否插入？
但是，假设我们有一个获得大量流量的API，假设两个相似的事务在同一毫秒内开始，并且不知何故，同一个人获得了第四个约会，这是我们不希望的。
我了解到，为了防止这种情况，应该在数据库中应用锁操作。但是我不知道应用哪种锁类型。我们知道在悲观锁中会发生死锁。当然，我们不希望出现这种情况。
我的问题是，我应该在Java Sping Boot**中做些什么来防止这种情况？
实体：

@Entity
@AllArgsConstructor
@NoArgsConstructor
@Getter
@Setter
public class Appointment {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String person;

    private Date appointment_date;

}

服务项目：

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

    public Appointment saveAppointment(Appointment appointment) {
        return appointmentRepository.save(appointment);
    }
}

储存库：

@Repository
public interface AppointmentRepository extends JpaRepository<Appointment, Long> {
}

控制器：

@RestController
public class AppointmentController {

    @Autowired
    private AppointmentService appointmentService;

    @PostMapping(“/store”)
    public Appointment store(@RequestBody Appointment appointment) {
        return appointmentService.saveAppointment(appointment);
    }
}

非常感谢您的关注：）

spring-data-jpa

来源：https://stackoverflow.com/questions/73571816/how-to-conditional-database-insert-operation-in-java-spring-with-lock

2条答案

按热度按时间

mpgws1up1#

让我们假设您可以更改表模式。

标识符
人员ID
预约日期
版本
创建日期

您可以在实体中加入@Version注解，并在数据表中加入数据行，以使用 * 开放式锁定 *。

伪代码

@Entity
public class Appointment {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @OneToOne
    private String personId;

    @ElementCollection
    private List<Date> appointmentDates = new ArrayList<>();

    @Version private Long version;

    private Instant createdDate;

    public static Appointment create(Appointment appointment) {
        // code that creates a new appointment
    }

    public Appointment addAppointment() {
        // code that updates your appointment
    }

}

@版本注解
在您的服务类中，您可以创建新的预约伪代码

var appointment = Appointment.create(newAppointment);
repository.save(appointment);

或更新伪代码

var appointmentToUpdate = repository.findById(appointmentId);
// validation for appointment count and date
if (createdDate.isToday() && appointmentToUpdate.getAppointmentDates().size() >= 3)
  // exception
var updatedAppointment = appointmentToUpdate.addAppointment();
repository.save(updatedAppointment);

如果另一个线程修改了此实体，它将引发OptimisticLockException。

赞(0）回复(0）举报 2022-11-10

vsaztqbk2#

[更新：“至于生成预约编号...”如下]

数据库应用程序强制执行业务规则：限制3个具有相同PERSON和APPOINTMENT_DATE值的实体示例
如果您不介意您的数据库成为一个智能的协作者，而不仅仅是一个有毒的垃圾堆，您可以考虑以下基于SQL的解决方案（数据库擅长数据一致性）：

CREATE TABLE APPOINTMENTS ( ID NUMBER NOT NULL PRIMARY KEY,
                            PERSON VARCHAR(30) NOT NULL,
                            APPOINTMENT_DATE DATE NOT NULL,
                            APPOINTMENT_NUMBER NUMBER default 1 NOT NULL
                          );
alter table appointment add constraint appointment_date_ck check (appointment_date = trunc(appointment_date)); -- only whole days, no hour/minute second components
alter table appointment add constraint appointment_number_ck check (appointment_number >= 1 and appointment_number <= 3); -- Limit 3
alter table appointment add constraint appointment_daily_limit_uk unique (upper(person), appointment_date, appointment_number); -- Enforcer

如果试图插入违反业务规则的行，数据库将引发异常。
[UPDATE]至于生成appointment_number值，这里有一个建议添加到您的Jpa/CrudRepository接口中（草稿未测试;处理/格式化日期所需的细化）：

@Query( value="select COALESCE(max(APPOINTMENT_NUMBER) + 1, 1) " +
    "      from APPOINTMENTS " +
    "     where PERSON = :PERSON_PARAM and APPOINTMENT_DATE = :DATE_PARAM"
    , nativeQuery=true
  )
  Integer getNextAppointmentNumber(@Param("PERSON_PARAM") String person, @Param("DATE_PARAM") Date appointmentDate);

  default Appointment saveAppointment(Appointment appointment){
    boolean inserting = (appointment.getId()==null);
    if (inserting) {
      Appointment.setAppointmentNumber (getNextAppointmentNumber(appointment.getPerson(), appointment.getAppointmentDate())));
      if (appointment.getAppointmentNumber() > 3) {
        throw new DailyAppointmentsExceededException(); // which define
    }
    return save(appointment);
  }

另一种方法是在数据库表的INSERT触发器中实现同样的MAX+1算法。

[更新2以回应意见：]

两个用户几乎不可能在同一天的同一瞬间/毫秒内尝试为同一个人安排约会。如果他们这样做了，其中一个会获胜，另一个会得到一个独特的/PK违规错误。数据库触发器解决方案在这方面会更安全，但会在客户端/服务端带来额外的问题。如果你感兴趣，请告诉我。同样，关于重新填充删除操作留下的间隙，请尝试以下语句（最终SQL语法取决于RDBMS）：

@Query( value="select appointment_number from (" +
                       " select 1 as appointment_number from appointments where PERSON = :PERSON_PARAM and APPOINTMENT_DATE = :DATE_PARAM and appointment_number != 1 " + 
                       " union select 2 as appointment_number from appointments where PERSON = :PERSON_PARAM and APPOINTMENT_DATE = :DATE_PARAM and appointment_number != 2 " + 
                       " union select 3 as appointment_number from appointments where PERSON = :PERSON_PARAM and APPOINTMENT_DATE = :DATE_PARAM and appointment_number != 3 " +
                ") LIMIT 1 order by appointment_number";
    , nativeQuery=true
  )
  Integer getNextAppointmentNumber(@Param("PERSON_PARAM") String person, @Param("DATE_PARAM") Date appointmentDate);

  default Appointment saveAppointment(Appointment appointment){
    boolean inserting = (appointment.getId()==null);
    if (inserting) {
      Appointment.setAppointmentNumber (getNextAppointmentNumber(appointment.getPerson(), appointment.getAppointmentDate())));
      if (appointment.getAppointmentNumber() == null) {
        throw new DailyAppointmentsExceededException(); // which define
    }
    return save(appointment);
  }

赞(0）回复(0）举报 2022-11-10

我来回答

spring-data-jpa 如何在Java Spring中进行带锁的条件数据库插入操作？

2条答案

相关问题

热门标签

最新问答