Search code examples
postgresqlhibernatejpaspring-data

Hibernate Postgresql select for update with outer join issue

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.

Solution

  • 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