Spring JPA Locking Concept

Question

Anybody have any idea how to use Locking with UPDATE statement in Spring JPA, so that only one thread can UPDATE the record at a time?

Here, I am trying to update the availability of table name aircraft_route and before updating, I want to take the lock of that row so that no other thread can update it at the same time.

Error: Illegal attempt to set lock mode on a native SQL query

Any help would be appreciated.

Thanks

AirCraftRoute.java

@Builder
@Setter
@Getter
@AllArgsConstructor
@NoArgsConstructor
@Entity(name = "aircraft_route")
public class AirCraftRoute implements Serializable
{
    @EmbeddedId
    private AirCraftRoutePK pk = new AirCraftRoutePK();

    @ManyToOne
    @MapsId("airCraftId")
    @JoinColumn(name = "aircraft_id")
    private AirCraft airCraft;

    @ManyToOne
    @MapsId("routeId")
    @JoinColumn(name = "route_id")
    private Route route;

    @Column(name = "journey_date")
    private Date journeyDate;

    @Column(name = "departure_time")
    private Time departureTime;

    @Column(name = "arrival_time")
    private Time arrivalTime;

    @Column(name = "fare")
    private float fare;

    @Column(name = "availability")
    private int availability;

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (!(o instanceof AirCraftRoute)) return false;
        AirCraftRoute that = (AirCraftRoute) o;
        return Float.compare(that.getFare(), getFare()) == 0 &&
                Objects.equals(getAirCraft(), that.getAirCraft()) &&
                Objects.equals(getRoute(), that.getRoute()) &&
                Objects.equals(getJourneyDate(), that.getJourneyDate()) &&
                Objects.equals(getDepartureTime(), that.getDepartureTime()) &&
                Objects.equals(getArrivalTime(), that.getArrivalTime());
    }

    @Override
    public int hashCode() {
        return Objects.hash(getAirCraft(), getRoute(), getJourneyDate(), getDepartureTime(), getArrivalTime(), getFare());
    }
}

AirCraftRouteRepository.java

@Repository
public interface AirCraftRouteRepository extends JpaRepository<AirCraftRoute,Integer> {

    @Transactional
    @Query(value = "SELECT ar.availability FROM aircraft_route ar WHERE ar.aircraft_id = :airCraftId AND ar.route_id = :routeId FOR UPDATE",nativeQuery = true)
    int getAvailability(@Param("airCraftId") Long airCraftId, @Param("routeId") Long routeId);

    @Modifying
    @Transactional
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query(value = "UPDATE aircraft_route ar SET ar.availability = ar.availability - :numberOfTickets WHERE ar.aircraft_id = :airCraftId AND ar.route_id = :routeId AND ar.availability > 0",nativeQuery = true)
    int updateAvailability(@Param("numberOfTickets") int numberOfTickets,@Param("airCraftId") Long airCraftId, @Param("routeId") Long routeId);
}

score 2 · Answer 1 · answered Sep 13 '19 at 14:27

2

I think you don't need the Lock at all for native SQL. You can FOR UPDATE directly in the native query. Like this

@Transactional
@Query(value = "SELECT * FROM aircraft_route ar WHERE ar.aircraft_id = :airCraftId AND ar.route_id = :routeId FOR UPDATE",nativeQuery = true)
List<Integer> getAvailability(@Param("airCraftId") Long airCraftId, @Param("routeId") Long routeId);

answered Sep 13 '19 at 14:27

StanislavL

56,971
9
68
98

Any idea how to use "FOR UPDATE" with UPDATE command? @Query(value = "UPDATE aircraft_route ar SET ar.availability = ar.availability - :numberOfTickets WHERE ar.aircraft_id = :airCraftId AND ar.route_id = :routeId AND ar.availability > 0",nativeQuery = true) Can we use UPDATE with this above command? – Gibran Mohammad Khan Sep 15 '19 at 11:20
No need to use `FOR UPSATE` with update. You are already updating. – StanislavL Sep 16 '19 at 09:42

score 0 · Answer 2 · answered Sep 13 '19 at 15:35

0

I think you can simply avoid to use native query for that simple syntax:

@Transactional
@Lock(LockModeType.PESSIMISTIC_WRITE)
@Query(value = "SELECT * FROM AircraftRoute ar WHERE ar.id = :airCraftId AND ar.routeId = :routeId")
List<Integer> getAvailability(@Param("airCraftId") Long airCraftId, @Param("routeId") Long routeId);

with something like above. You have to use right syntax with entity name and field but then you can use the @Lock Spring annotation and its db lock management instead of manual handling.

answered Sep 13 '19 at 15:35

Roberto Canini

340
1
3
13

Your Solution is throwing exception as "Validation failed for query for method public abstract java.util.List com.abc.gds.repository.AirCraftRouteRepository.getAvailability" and I think, it is due to the: https://stackoverflow.com/questions/44647630/validation-failed-for-query-for-method-jpql – Gibran Mohammad Khan Sep 14 '19 at 11:52
Can you post your AircraftRoute entity? And the updated interface getAvailability method? – Roberto Canini Sep 16 '19 at 07:09

Spring JPA Locking Concept

2 Answers2