Prevent two transactions from creating the same entity but still allow concurrent creation

Question

I have a system that interfaces with a third party system to create and store car data. The user selects a ThirdPartyCar and uses it to create a Car in my system.

A service method saves the car. But it should only save if someone else has not already tried to save the car using that ThirdPatyCar:

@Transactional(transactionManager="mySystemTransactionManager", isolation=Isolation.?)
public void saveNewCarAndMapToThirdPartyCar(Car car, Long thirdPartyCarId) {

     // Mapping table tracks which ThirdPartyCar was used to create my Car. 
     // The thirdPartyCarId is the primary key of the table.
     if (!thirdPartyCarMapRepo.existsById(thirdPartyCarId)) {

            // sleep to help test concurrency issues
            log.debug("sleep");
            try {
                Thread.sleep(5000);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
            log.debug("awake");

            Car car = carRepository.save(car);
            thirdPartyCarMapRepo.save(new ThirdPartyCarMap(thirdPartyCarId, car));
    }
}

Here's a scenario that results in the problem:

User A                        User B
 existsById                      |
    |                            |
    |                         existsById
    |                            |
    |                            |
 carRepo.save                    |
 thirdPartyCarMapRepo.save       |
    |                            |
    |                            |
    |                          carRepo.save
    |                          thirdPartyCarMapRepo.save

With isolation set to anything lower than Isolation.SERIALIZABLE, it appears that both users will have existsById return false. This results in two cars being created and only the last saved one being mapped back to the third party car.

If I set Isolation.SERIALIZABLE, user cannot concurrently create cars even from different third party cars.

How can I prevent user B from creating a car when the third party car is the same as user A but still allow both to concurrently create when the third party cars are different?

Update

After thinking and researching about this a bit more, I believe this may not be an transaction isolation issue.

Here's the ThirdPartyCarMap table:

thirdPartyCarId (PK, bigint, not null)
carId (FK, bigint, not null)  /* reference my system's car table */

As an example using the scenario diagram above:

User A thirdPartyCarMapRepo.save does:

inserts into ThirdPartyCarMap (thirdPartyCarId, carId) values (45,1)

However, user B thirdPartyCarMapRepo.save does:

update ThirdPartyCarMap set carId =2 where thirdPartyCarId=45

So, it's the dual nature of the save invocation that is causing issues. I think I have the following possible solutions:

Approach 1: Implement Persistable and override the isNew behavior as described here
Approach 2: Add a native query to the repository that does the insert
Approach 3: Add a surrogate primary key (such as a auto incrementing id) and and remove thirdPartyCarIdas the primary but make it unique.

I think the last option is probably best but each option has issues: - Approach 1: isNew would return true even when reading data - Approach 2: Seems a bit like a hack - Approach 3: Extra data added to the table just for the sake of JPA it seems.

Is there another better approach that doesn't have these issues?

Did you try adding identity to `ThirdPartyCarMap`, and saving the new instance with null value? — yegodm, Apr 18 '19 at 16:14
@yegodm, yes, I had the id as `thirdPartyCarId` but the problem is that the PK is not null on saving b/c it always has a value from the third party system. So, I tried replacing with a PK independent of the third party system (i.e. auto incrementing id). Please see updated post for the my thoughts on that approach and updated question. Thanks. — James, Apr 18 '19 at 17:03
Oh, sorry, I didn't read through the approach #3 carefully. That is essentially what I meant. Yet I would still opt for that, not bothering about extra data. From the other point of view, and if I remember correctly, there is one more approach possible. Both ids can constitute a compound key named say `Key(thirdPartyCarId:Long, carId:Long)` mapped to a field `key: Key` with `@EmbeddedId` annotation. — yegodm, Apr 18 '19 at 19:20
No worries. Thanks for the additional option which would avoid the extra column in the DB but I suppose would might add a bit more code. I'm leaning towards approach 3. — James, Apr 18 '19 at 22:14

score 0 · Answer 1 · answered Apr 18 '19 at 08:33

0

One option would be to use a unique constraint on the DB level which is able to guarantee that you cannot have two PhysicalCars with the same VIN(If this is your unique identification in your DB for a PhysicalCar). This means that in your case when two threads will try to add a PhysicalCar one of them will fail with a unique constraint violation. Spring is already wrapping the DB exception into a DataIntegrityViolationException but you can extract from it the constraint name and if it's the one you expect to be thrown in case of duplicates for your PhysicalCar then you can act on it.

answered Apr 18 '19 at 08:33

Ioan M

1,105
6
16

Thanks for answer. Are you suggesting that I add another unique column to the mapping table? So, the mapping table would have the following columns: `thirdPartyCarId - PK`, `carId - FK`, `VIN` (or something unique) `- unique constraint`. I think the problem is that User B's save would just simply update the mapping record that user A inserted through its save operation. – James Apr 18 '19 at 15:30
1

Since spring is inspecting the '@Id' property to figure out whether it should do an update or an insert I would have in that table an incremental '@Id' and also a unique constraint for the ThirdPartyCarId. This way the second create will not pass any Id, spring will treat this as an insert(new record) and your DataConstraintViolation will be thrown and then you have to act on it as needed(return error to the user etc) – Ioan M Apr 18 '19 at 19:28
Ah now I understand. That's approach 3 in my update to the OP. It's the way I'm leaning unless there's a better option. – James Apr 18 '19 at 22:11

score 0 · Answer 2 · answered Apr 18 '19 at 08:49

0

You can create the checksum of User A and User B's ThirdPartyCar and Car object by using hashCode() or MD5 and check for equality. If you find User B's Car is same as User A's ThirdPartyCar then throw RuntimeException else proceed.

answered Apr 18 '19 at 08:49

Alin

314
1
3
9

Prevent two transactions from creating the same entity but still allow concurrent creation

2 Answers2