'How to avoid race condition in table with pairs of id's

I am working on small parking service and i think I get problem with race condition. I have table in database "cars_on_parkings_ids" where i have pairs of carID and parkingID.

insert into cars_on_parkings_ids (ID, ID_CAR, ID_PARKING) values ('idOfPair', 'carID', 'parkingID');

When i want to chack if on parking is any free slot i am doing query like this (i have special slots with chargers intended for only electric cars and electric cars can park only on them, so i do not want to count them):

@Query(value = "SELECT COUNT(*) FROM CarOnParkingEnity cp INNER JOIN  CarEntity c ON c.idCar = cp.idCar " +
        "where c.powerType not like 'ELECTRIC' AND cp.idParking =:parkingId")
Integer findAmountOfTakenSlotsOnParking(@Param("parkingId") String parkingID);

and next, my service compare that number with number of parking slots in this parking.

So i have a problem with situation when 2 cars want to entry in very very close time and there is only 1 free slot. There is a few miliseconds between query and checking how many slots parking has.

if (carsOnParkingsRepository.findAmountOfTakenSlotsOnParking(parking.getIdParking())
                        < parking.getNumberOfParkingSlots()) {
                    return setCarToParking(carAtGateModel);
                } else {
                    return new ResponseEntity<>(responseMessage.everySlotIsTaken(), HttpStatus.OK);
                }


A. car1 want to entry on parking1
A. System is doing query about amount of taken places = returns 3
B. car1 want to entry on parking 1
B. System is doing query about amount of taken places = returns 3
A. System compare amount with number of parking slot from database with parkings. Parking has 4 slots
B. System compare amount with number of parking slot from database with parkings. Parking has 4 slots
A. Gate is getting up (3 < 4)
B. Gate is getting up (3 < 4) (but in real its 4/4

I do not know what should i use to avoid that situation. I was reading about add column '@Version' but it will not working with my pairs of ID's

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source

'How to avoid race condition in table with pairs of id's

Sources

Related Questions