jarkkohyoty ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *created* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiNjcyYjcyMzk0... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16433?atlOrigin=eyJpIjoiNjcyYj... ) HHH-16433 ( https://hibernate.atlassian.net/browse/HHH-16433?atlOrigin=eyJpIjoiNjcyYj... ) “Locking with ORDER BY is not supported” error in a “select for update” query (Oracle DB) ( https://hibernate.atlassian.net/browse/HHH-16433?atlOrigin=eyJpIjoiNjcyYj... ) Issue Type: Bug Affects Versions: 6.1.7 Assignee: Unassigned Created: 04/Apr/2023 06:21 AM Environment: Database: Oracle 19c Priority: Minor Reporter: jarkkohyoty ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) 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 ( https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELEC... ) 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. ( https://hibernate.atlassian.net/browse/HHH-16433#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16433#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=Em... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100221- sha1:89dc9bf )