title: Spring JPA Locking Concept
body: <p>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?</p>
  
  <p>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.</p>
  
  <p><strong>Error: Illegal attempt to set lock mode on a native SQL query</strong></p>
  
  <p>Any help would be appreciated.</p>
  
  <p>Thanks</p>
  
  <p><strong>AirCraftRoute.java</strong></p>
  
  <pre><code>@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 &amp;&amp;
                  Objects.equals(getAirCraft(), that.getAirCraft()) &amp;&amp;
                  Objects.equals(getRoute(), that.getRoute()) &amp;&amp;
                  Objects.equals(getJourneyDate(), that.getJourneyDate()) &amp;&amp;
                  Objects.equals(getDepartureTime(), that.getDepartureTime()) &amp;&amp;
                  Objects.equals(getArrivalTime(), that.getArrivalTime());
      }
  
      @Override
      public int hashCode() {
          return Objects.hash(getAirCraft(), getRoute(), getJourneyDate(), getDepartureTime(), getArrivalTime(), getFare());
      }
  }
  </code></pre>
  
  <p><strong>AirCraftRouteRepository.java</strong></p>
  
  <pre><code>@Repository
  public interface AirCraftRouteRepository extends JpaRepository&lt;AirCraftRoute,Integer&gt; {
  
      @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 &gt; 0",nativeQuery = true)
      int updateAvailability(@Param("numberOfTickets") int numberOfTickets,@Param("airCraftId") Long airCraftId, @Param("routeId") Long routeId);
  }
  </code></pre>