Hibernate与Postgresql的select for update和外连接问题

Question

我在使用Spring Data与Hibernate作为JPA实现以及PostgreSQL数据库时，遇到了一些选择更新行的问题。

假设我们有实体A、B、C。

public class A{
   @Id
   private Long id;

   @OneToMany(fetch = FetchType.EAGER)
   private Set<B> bSet;

   @OneToMany(fetch = FetchType.EAGER)
   private Set<C> cSet;
}

假设我们想选择 A 表及其所有关联的 B 和 C 实体进行更新，即锁定与 A 表相关的行。

@Query(SELECT a FROM A a 
       LEFT JOIN FETCH a.bSet
       LEFT JOIN FETCH a.cSet
       WHERE a.id=?)
@Lock(LockModeType.PESSIMISTIC_WRITE)
public A selectAndLockA(Long Aid);

查询将类似于

SELECT a.column1, ... from tableA a LEFT JOIN tableB b ... FOR UPDATE of a,c

FOR UPDATE of a,c

该查询会尝试锁定两个表，会导致异常，如：org.postgresql.util.PSQLException: ERROR: FOR UPDATE cannot be applied to the nullable side of an outer join。我想要的是只锁定第一个表 "FOR UPDATE OF a"。是否有可能进行配置或告诉 Hibernate 只锁定第一个表？

- Putin

Answer 1

这在PostgreSQL中不受支持。如果您执行外部SELECT，任何人都可以插入行到LEFT JOINED表中，从而修改您正在查看的结果集（例如，在重复读取时列将不再为NULL）。

有关详细说明，请参见此处

- fhossfel

Answer 2

自问题被创建以来已经过了很长时间，但我有一个类似的问题，希望我的答案能帮助到某些人。

假设我们有以下JPA实体：

@Entity
@Table(name = "card_transactions")
public class CardTransactionsEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "card_trans_seq")
    @SequenceGenerator(name = "card_trans_seq", sequenceName = "card_trans_seq")
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns({
            @JoinColumn(name = "ofd_id", referencedColumnName = "ofd_id"),
            @JoinColumn(name = "receipt_id", referencedColumnName = "receipt_id")})
    private ReceiptsEntity receipt;

    @Column
    @Enumerated(EnumType.STRING)
    private CardTransactionStatus requestStatus;

    ...

}

@Entity
@Table(name = "receipts")
public class ReceiptsEntity {
    @EmbeddedId
    private OfdReceiptId id; 

    ...
}

@Embeddable
public class OfdReceiptId implements Serializable {

    @Column(name = "ofd_id")
    @Enumerated(EnumType.STRING)
    private OfdId ofdId;

    @Column(name = "receipt_id")
    private String receiptId;

    ...
}

我们希望只对CardTransactionsEntity进行悲观锁，并获取其关联的ReceiptsEntity。这可以使用Hibernate和Spring Data JPA存储库来实现，如下所示：

public interface CardTransactionRepository extends JpaRepository<CardTransactionsEntity, Long> {

    @Query("select ct from CardTransactionsEntity ct left join fetch ct.receipt r where ct.requestStatus = :requestStatus")
    @Lock(value = LockModeType.PESSIMISTIC_WRITE)
    @QueryHints(value = {
            @QueryHint(name = "javax.persistence.lock.timeout", value = "-2"), // LockOptions.SKIP_LOCKED
            @QueryHint(name = "org.hibernate.lockMode.r", value = "NONE") // "r" is alias for ct.receipt and will excluded from PESSIMISTIC_WRITE
    })
    List<CardTransactionsEntity> loadCardTransactions(@Param("requestStatus") CardTransactionStatus requestStatus, Pageable pageable);

}

这个仓库方法将执行类似以下的查询

SELECT ct.*, r.* from card_transactions ct LEFT OUTER JOIN receipts r ON ct.ofd_id = r.ofd_id and ct.receipt_id = r.receipt_id WHERE ct.request_status=? LIMIT ? FOR UPDATE OF ct SKIP LOCKED

- exploder86

Answer 3

您可以通过使用FetchType.LAZY连接表来绕过此错误。这种获取类型是默认的，对于@OneToMany连接不需要指定。

public class A{
   @Id
   private Long id;

   @OneToMany
   private Set<B> bSet;

   @OneToMany
   private Set<C> cSet;
}

- YucelC