How to conditional database insert operation in Java Spring with Lock?

Question

I am trying to conditional insert operation to database (assume SQLite). My goal is to ensure data consistency.

Suppose we are making an appointment planner.

So we have an appointments table like this:

• id

• personal

• appointment_date

However, we have an important rule. A person cannot make more than 3 appointments in the same day.

Normally we query from the database how many appointments this person has that day. Then we decide whether to insert or not. Right?

But let's say we have an API that gets a lot of traffic. Suppose two similar transactions started in the same millisecond and somehow the same person was given a 4th appointment. This is something we don't want.

I learned that to prevent this situation, a lock operation should be applied in the database. But I couldn't understand which lock type to apply. We know that deadlock can occur in pessimistic lock. Of course, we do not want this situation.

My question is, what should I do in Java Spring Boot to prevent this situation?

Entity:

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

    private String person;

    private Date appointment_date;

}

Service:

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

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

Repository:

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

Controller:

@RestController
public class AppointmentController {

    @Autowired
    private AppointmentService appointmentService;

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

Thank you very much for your attention :)

Answer 1

Let's assume you can change the table schema. In that case, the appointment aggregate will consist of

• id
• personId
• appointmentDates
• version
• createdDate

You can use Optimistic Lock by adding the @Version annotation in your entity and a column in your table.

Pseudo code

@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
    }

}

@Version annotation

and in your service class, you can create a new appointment Pseudo code

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

or update Pseudo code

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);

It will throw the OptimisticLockException in case another thread has modified this entity.

Answer 2

[UPDATE: "As far as generating the appointment number..." below]

Database application enforcing business rule: Limit 3 instances of entity having the same PERSON and APPOINTMENT_DATE values

If you don't mind your database being an intelligent collaborator instead of just a toxic waste dump, you might consider the following SQL-based solution (databases are good at data consistency):

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

If an attempt is made to insert a row that would violate the business rule, the database will throw an exception.

[UPDATE] As far as generating the appointment_number value, here is a suggestion to add to your Jpa/CrudRepository interface (drafted not tested; refinement required for handling/formatting date):

  @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);
  }

Another alternative is to implement that same MAX+1 algorithm in an INSERT trigger on the database table. Let me know if this helps.

[UPDATE 2 IN RESPONSE TO COMMENT:]

HIGHLY unlikely that 2 users would attempt to schedule an appointment for the same person on the same day AT THE SAME INSTANT/MILLISECOND. If they did, one would win and the other would get a unique/PK violation error. The database trigger solution would be more bullet-proof in this regard, but raises additional issues on the client/service side. Let me know if you're interested. Just the same, to your point about refilling gaps left by deletions, try this on (final SQL syntax depending upon 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);
  }