spring - Spring Boot - 锁定表中的单个记录直到读写完成

Question

公共仓库中托管的代码https://github.com/bsridharpatnaik/SpringCRUDSample

假设我有一个表用户有一个字段 leaveBalance。

场景 - 当用户申请休假余额时，我必须读取他当前的休假余额并从休假余额中减去申请的休假数。

所以，我创建了模型、存储库和控制器。为了简单起见，我在控制器内部编写了逻辑。以下是我的请假申请逻辑

@PostMapping("/users/{id}/leave")
    public ResponseEntity<User> addLeaveForUser(@PathVariable(value = "id") Long userId,@RequestBody CreateLeaveDTO payload) throws ResourceNotFoundException {
        User user = userRepository.findById(userId)
                .orElseThrow(() -> new ResourceNotFoundException("User not found :: " + userId));

        LeaveDetails ld = new LeaveDetails();
        ld.setDays(payload.getDays());
        ld.setUser(user);
        leaveRepository.save(ld);

        // Set leave back to user
        Set<LeaveDetails> leaves = new HashSet<>();
        leaves = user.getUserLeaves();
        leaves.add(ld);
        user.setUserLeaves(leaves);
        System.out.println("Current Leaves - "+user.getLeaveBalance());
        user.setLeaveBalance(user.getLeaveBalance()-payload.getDays()); //subtract leave from leave balance
        userRepository.save(user);
        return ResponseEntity.ok(user);
    }

当有非并行请求时，一切正常。但是当收到并行请求时，两个请求同时读取 leaveBalance 并从同一个 leaveBalance 中减去申请的休假。示例 - 如果 leaveBalance 为 100，并且在 2 天内收到两个并行请求。请求后的 leaveBalance 是 98 而不是 96。

我想知道处理这种情况的最佳方法。我想锁定从数据库读取的记录，直到记录被保存回来。另外，我不想锁定整个表，因为可能有不同的用户试图读取不同的记录。

我试过的

• 使方法同步 - 这可行，但我不确定当从不同用户收到多个并行命中时会发生什么
• 试图将悲观写锁放在 findById 上并保存。但它没有奏效。收到错误“无法在只读事务中执行语句。”

有人可以用最好的概念/技术来帮助处理这种情况。

以下是我的其他课程供您参考

@Entity
@Table(name = "users")
@EntityListeners(AuditingEntityListener.class)
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long userId;

    @Column(name = "first_name", nullable = false)
    private String firstName;

    @Column(name = "last_name", nullable = false)
    private String lastName;

    @Column(name = "email_address", nullable = false)
    private String emailId;

    @Column(name = "created_at", nullable = false)
    @CreatedDate
    @Temporal(TemporalType.TIMESTAMP)
    private Date createdAt;

    @Column(name = "created_by", nullable = false)
    @CreatedBy
    private String createdBy;

    @Column(name = "updated_at", nullable = false)
    @LastModifiedDate
    @Temporal(TemporalType.TIMESTAMP)
    private Date updatedAt;

    @Column(name = "updated_by", nullable = false)
    @LastModifiedBy
    private String updatedby;

    @Column(name = "leave_balance", nullable = false)
    Long leaveBalance;

    @OneToMany(mappedBy="user")
    @JsonBackReference
    Set<LeaveDetails> userLeaves;
}

@Entity
@Table(name = "leaves")
@EntityListeners(AuditingEntityListener.class)
public class LeaveDetails {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long leaveId;
    private long days;

    @ManyToOne
    @JoinColumn(name="userId", nullable=false)
    User user;

@Repository
public interface UserRepository extends JpaRepository<User, Long>{
    public Optional<User> findById(Long id);
}

@Repository
public interface LeaveRepository extends JpaRepository<LeaveDetails, Long>{

}

标签： springspring-bootspring-mvcspring-data