spring - Spring JPA 锁定概念
问题描述
任何人都知道如何在 Spring JPA 中使用 Locking with UPDATE 语句,以便一次只有一个线程可以更新记录?
在这里,我正在尝试更新表名 aircraft_route 的可用性,并且在更新之前,我想锁定该行,以便没有其他线程可以同时更新它。
错误:非法尝试在本机 SQL 查询上设置锁定模式
任何帮助,将不胜感激。
谢谢
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);
}
解决方案
我认为对于本机 SQL,您根本不需要 Lock。您可以FOR UPDATE
直接在本机查询。像这样
@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);
推荐阅读
- matlab - 求多变量函数的最小值
- python - 尝试读取 MNIST 数据集时出现问题
- git - Android Studio 中的 git rebase 命令
- python - all_pairs_dijkstra 是否比多个 dijkstra_path 快?
- javascript - 每次运行后纠正当前状态
- c# - 在 mecanim 动画完成后,如何以编程方式设置或处理对象的新变换位置?
- performance - 为什么选择 `unwrap_or_else` 而不是 `unwrap_or`?
- java - 为什么在使用类似代码在 Java 中编译时调用 put() 无法在 Kotlin 中编译
- python - Pylint 中的模块“PyQt5.QtWidgets”错误中没有名称“QApplication”
- python - 将所有像素的 RGB 值四舍五入以均匀着色