Spring Data JPA + Hibernate 如何跳过被锁定的行 (PostgreSQL)

Question

我正在尝试使用Spring Data JPA（2.1）和Hibernate在PostgreSQL上执行SKIP LOCKED查询。 查询如下所示：

@Lock(LockModeType.PESSIMISTIC_WRITE)
@QueryHints({@QueryHint(name = "javax.persistence.lock.timeout", value ="-2")})
List<Obj> findByEntityAndStatus(Entity entity, Status status);

根据 Spring data JPA native query skip locked 和 Select for update skip locked from JPA level 的说法，应该可以实现skip locked，但是生成的查询语句只会选择更新而不会跳过锁定的行。

生成的查询语句如下：

Hibernate: select obj0_.id as id1_5_, obj0_.name as name6_5_, obj0_.entity_id as entity10_5_, obj0_.status as status8_5_ from objs obj0_ left outer join entities entity1_ on obj0_.entity_id=entity1_.id where entity1_.id=? and obj0_.status=? for update of obj0_

我漏掉了什么？

- Janar

---

你自那时以来找到解决方案了吗？^^' - Hyukchan Kwon

@Janar，如果这有帮助的话，你可以选择正确的答案吗？ - J.Wincewicz

Answer 1

你的代码没问题。需要记住的是PESSIMISTIC_WRITE在Oracle和PostgreSQL 9.5中使用SELECT …​ FOR UPDATE SKIP LOCKED。我猜你可能忘了告诉JPA，你要使用更新版本的Postgres。所以你有两个选择：

• tell JPA that you are using PostgreSQL Dialect which supports SKIP LOCKED:
  

  spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQL10Dialect
  

  
  
  After this I received desired output:
  

  where
      subscripti0_.valid_until<=? 
  and subscripti0_.status='ACTIVE' 
  for update of subscripti0_1_ skip locked
  

  
  
  and obviously, concurrent thread was was not able to fetch locked rows until transaction was completed.
• use native query :
  

  SELECT * FROM objects o WHERE o.valid_until <= :validUntil FOR UPDATE SKIP LOCKED 
  

- J.Wincewicz

Answer 2

你可以使用nativeQuery。

@Query(value = "SELECT * FROM task LIMIT 10 FOR UPDATE SKIP LOCKED", nativeQuery = true)
List<TaskEntity> fetchAllUnlocked();

- vrapalis