Hibernate Postgreql选择与外部连接问题更新

Question

我在尝试使用Spring数据和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;
}

假设我们希望选择一个包含所有相关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

更新a， c

该查询将尝试锁定导致异常的两个表，例如：org.postgresql.util.PSQLException:ERROR:FOR UPDATE无法应用于外部联接的可为null的一侧

我试图归档的是只锁定第一个表"FOR UPDATE OF a"

是否可以以某种方式配置或告诉Hibernate只锁定第一个表。

Answer 1

通过使用FetchType.LAZY连接表，可以绕过此错误。此获取类型是默认类型，不需要为@OneToMany联接指定。

public class A{
   @Id
   private Long id;

   @OneToMany
   private Set<B> bSet;

   @OneToMany
   private Set<C> cSet;
}

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;

    ...
}

我们希望为悲观的仅更新CardTransactionEntity选择具有已获取ReceiptSenty的CardTransactionEntity。这可以使用Hibernate和Spring数据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

Answer 3

Postreet SQL不支持此操作。如果执行外部SELECT，则没有什么可以阻止某人将行插入到LEFT JOINED表中，从而修改您正在查看的结果集（例如，重复读取时列将不再为NULL）。

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