Spring Data JPA + Hibernate Skip Locked rows (PostgreSQL)

user_3752185 0
Viewed 4057

I am trying to execute SKIP LOCKED query on PostgreSQL using Spring Data JPA (2.1) and Hibernate. The query looks like this:

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

According to Spring data JPA native query skip locked and Select for update skip locked from JPA level it should work but the generated query only selects for update without skipping locked rows.

The generated query:

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_

What am I missing?

jpa postgresql hibernate spring
2 Answers

Your code is fine. All you need to remember is that PESSIMISTIC_WRITE uses a SELECT …​ FOR UPDATE SKIP LOCKED in Oracle and PostgreSQL 9.5. I assume you could have forgotten about telling JPA, that you what to use newer version of Postgres. So you have two options:

  • 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
user_63304420

You can use nativeQuery.

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