CODEWITHSUNDEEP
springpostgresqlhibernatejpa
Janar
Janar

Reputation: 2701

Spring Data JPA + Hibernate Skip Locked rows (PostgreSQL)

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?

Upvotes: 6

Views: 7798

Answers (2)

J.Wincewicz
J.Wincewicz

Reputation: 962

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

Upvotes: 8

vrapalis
vrapalis

Reputation: 11

You can use nativeQuery.

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

Upvotes: 0

Related Questions