Context:
- Spring Boot application with Spring JPA and MS SQL DB.
- User registration process with insuranceNumber
- insuranceNumber is not unique in DB but only for certain status (PROSPECTIVE, PARTICIPANT)
- there is a duplication check in a service to check this
REST Controller -> RegistrationService (@Transactional) - do duplicate check (select * from customer where status in (PROSPECTIVE,PARTICIPANT) and insuranceNumber = XYZ -> no results good) - insert new customer to DB Issue:
- If tested, the duplication checks works but sometimes I have duplicates of insuranceNumber in PROSPECTIVE status
- Assumption:
- due to multiple REST requests in short time I have multiple threads (let's assume 3)
- Thread 1 "duplicate check" - all fine
- Thread 2 "duplicate check" - all fine (Thread 1 is not comitted yet)
- Thread 1 insert do DB, commit TX
- Thread 2 insert do DB, commit TX ## the issue, now customer with same insurance number in same status
- Thread 3 "duplicate check" - fails - als expected
Possible Solutions:
- Frontend: prevent this multiple requests. Out of scope. I want to be sure from backend
- DB: create something on DB site (database trigger) to do the same duplication check again. Feels wrong as it duplicates the logic of the duplication check. Would also raise a different exception than if raised in Java already
- Java code: RegistrationService with synchronized method. Would slow down registration for everybody. For me it would be enough that only one insurance number is allowed to enter the registration method.
Are there more ideas? Play around with isolation levels for the DB? Prevent to enter the registration method if one thread has already entered the method with same insurance number?
