Hibernate Postgresql选择要进行外部联接问题的更新 [英] Hibernate Postgresql select for update with outer join issue

问题描述

我遇到了尝试使用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进行更新，即与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:错误:FOR UPDATE无法应用于外部联接的可为空的一侧

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

是否可以进行某种配置或告诉Hibernate仅锁定第一个表.

解决方案

PostreSQL不支持此功能.如果您执行外部SELECT，那么什么都不会阻止某人在LEFT JOINED表中插入一行，从而修改您正在查看的结果集(例如，在重复读取时，列将不再为NULL).

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

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.

解决方案

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

这篇关于Hibernate Postgresql选择要进行外部联接问题的更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！