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

CodePudding user response：

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.

CodePudding user response：

Database application enforcing business rule: Limit 3 instance 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.