"Locking with ORDER BY is not supported" error in a "select for update" query (Oracle)

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?

This could be a bug or maybe the check is just a bit too strict. Can you create a JIRA ticket for this please? It would be great if you could also provide a PR with a fix for this, then we can see the CI pipeline run to check whether this change makes a test fail.

Thanks, created HHH-16433.

1 Like