Select for update skip locked from JPA level
I know this post is a bit old, but for the record, just as the accepted answer stated, "javax.persistence.lock.timeout" (org.hibernate.cfg.AvailableSettings#JPA_LOCK_TIMEOUT
) set to "-2" (org.hibernate.LockOptions#SKIP_LOCKED
) with Hibernate results in "SKIP LOCKED". However, this can be done at run-time without having to set any global settings.
Since 2.0 JPA allows to pass hints along like so
entityManager.find(MyType.class, id, LockModeType.PESSIMISTIC_WRITE, new HashMap<String, Object>() {{
put("javax.persistence.lock.timeout", "-2");
}});
Hibernate provides the UPGRADE_SKIPLOCKED Lock mode.
Using JPA and Hibernate, to produce a "SKIP_LOCKED" as per Hibernate LockMode documentation, you have to combine the PESSIMISTIC_WRITE JPA LockModeType:
entityManager.find(Department.class, 1, LockModeType.PESSIMISTIC_WRITE);
and the Lock timeout setting, like for example in persistence.xml for your persistence unit:
<properties>
<property name="javax.persistence.query.timeout" value="-2"/>
</properties>
(Note that you can configure this LockMode for complex query as well)
SKIP LOCKED is not part of ANSI SQL. Some RDBMS such the following provide this as a specific feature:
- MySQL
- Postgresql
- Oracle
So with pure JPA, it is not possible to specify a "SKIP LOCKED" in queries. Indeed, as documented in LockModeType, JPA 2.1 only supports the following:
- NONE
- OPTIMISTIC
- OPTIMISTIC_FORCE_INCREMENT
- PESSIMISTIC_FORCE_INCREMENT
- PESSIMISTIC_READ
- PESSIMISTIC_WRITE
- READ
- WRITE
However, to enable SKIP LOCKED in your query you can use these alternatives:
- Use specific JPA implementation feature, such as Hibernate LockMode which allows to specify the SKIP LOCKED via a JPA query, thanks to a combination of PESSIMISTIC_WRITE LockModeType Lock Timeout specific setting as described above
- Create a native SQL query as you did