The following code that reads from a queue table used by multiple processes works using Hibernate version 5.6 and renders a select ... order by ... for update skip locked
query for Oracle DB.
entityManager
.createQuery("SELECT q FROM Queue q ORDER BY q.created", Item.class)
.unwrap(org.hibernate.query.Query.class)
.setFetchSize(1)
.setLockOptions(
new LockOptions(LockMode.PESSIMISTIC_WRITE)
.setTimeOut(LockOptions.SKIP_LOCKED)
.setFollowOnLocking(false))
.scroll(ScrollMode.FORWARD_ONLY))
However, the same code fails with an exception using Hibernate 6.1.7:
org.hibernate.query.IllegalQueryOperationException: Locking with ORDER BY is not supported
at org.hibernate.dialect.OracleSqlAstTranslator.determineLockingStrategy(OracleSqlAstTranslator.java:66)
Looking at the OracleSqlAstTranslator, it seems to specifically deny using order by. However, e.g. Oracle 19c documentation does not mention that order by would be unsupported with for update clause, and Hibernate 5.6 produced a query that worked.
Changing the setFollowOnLocking
option to true
avoids the exception but seems to change semantics – instead of skipping locked rows, the query will now fail if another process already holds a lock for a row.
Is this is an intentional limitation for some other reasons, or a regression introduced in 6.x?