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?

CodePudding user response：

The only reliable approach to prevent duplicates in DB is to create unique index, in your particular case that should be filtered unique index:

CREATE UNIQUE NONCLUSTERED INDEX CUSTOMERS_UK  
ON CUSTOMERS(insuranceNumber)  
WHERE status IN ('PROSPECTIVE','PARTICIPANT')

Another options are:

• application locks in MSSQL
• lock by a key in Java, however, that won't work in case of multiple instance deployment