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:
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. |