Hibernate Postgresql select for update with outer join issue

Question

I have faced with issue trying to select for update row using Spring data with Hibernate as JPA implementation and Postgresql.

Suppose we have entities: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;
}

Suppose we want to select A with all related B and C entities for update i.e. with locking row related to A table.

@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);

The query will look like

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

FOR UPDATE of a,c

The query will try to lock two tables what leads to exception like : org.postgresql.util.PSQLException: ERROR: FOR UPDATE cannot be applied to the nullable side of an outer join

What I try to archive is locking only first table "FOR UPDATE OF a"

Is it possible to configure somehow or tell Hibernate to lock only first table.

Answer 1

This is not supported by PostreSQL. If you do an outer SELECT nothing can prevent somebody from inserting a row into the LEFT JOINED table thereby modifiying the result set you are looking at (e.g. the columns would not be NULL anymore on a repeated read).

For a detailed explanantion see here

Answer 2

It's been a long time since question was created, but I have a similar problem and hope my answer will help somebody.

Suppose that we have this JPA entities:

@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;

    ...
}

And we want select CardTransactionsEntity with fetched ReceiptsEntity for pessimistic update only CardTransactionsEntity. This can be done using Hibernate and Spring Data JPA repository as

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);

}

This repository method will execute query like

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