Spring data JPA native query skip locked

Question

I want to execute a SKIP LOCKED query on Oracle using Spring Data JPA, so I tried the following:

@Lock(LockModeType.PESSIMISTIC_WRITE)
@Query(value = "SELECT * FROM User WHERE ID=?1 FOR UPDATE SKIP LOCKED", nativeQuery = true)
User findOne(UUID id);

I tried the above and found that the generated query contains FOR UPDATE, but not SKIP LOCKED (below is the generated query from logs):

select ent0_.column1 as name, ent0_.CREATED_DATE as CREATED_2_33_0_ from TABLE_NAME alias_name where ent0_.column1=? for update

If I remove @Lock from the query method, the generated query does not even have FOR UPDATE.

Please suggest how I can generate a query with FOR UPDATE SKIP LOCKED, as required.

Answer 1

You can assing -2 to timeout value so that 'skip locked' will be used whenever possible.

PESSIMISTIC_WRITE with a javax.persistence.lock.timeout setting of -2

UPGRADE_SKIPLOCKED

The lock acquisition request skips the already locked rows. It uses a SELECT …​ FOR UPDATE SKIP LOCKED in Oracle and PostgreSQL 9.5, or SELECT …​ with (rowlock, updlock, readpast) in SQL Server.

public interface MyRepository extends CrudRepository<MyEntity, Long> {

    /**
     * The lock acquisition request skips the already locked rows.
     * It uses a SELECT …​ FOR UPDATE SKIP LOCKED in Oracle and PostgreSQL 9.5,
     * or SELECT …​ with (rowlock, updlock, readpast) in SQL Server.
     */
    String UPGRADE_SKIPLOCKED = "-2";

    @Lock(value = LockModeType.PESSIMISTIC_WRITE) // adds 'FOR UPDATE' statement
    @QueryHints({@QueryHint(name = "javax.persistence.lock.timeout", value = UPGRADE_SKIPLOCKED)})
    MyEntity findFirstByStatus(String status);

}

By doing like this, the select query will have select ... for update skip locked

https://docs.jboss.org/hibernate/orm/5.0/userguide/html_single/chapters/locking/Locking.html

Answer 2

Add:

@QueryHints({@QueryHint(name = "javax.persistence.lock.timeout", value ="-2")})