{"record":{"id":"ba662b6bf3ad58ad","repo":"hibernate/hibernate-orm","slug":"locking-with-offset-fetch-is-not-supported","errorCode":null,"errorMessage":"Locking with OFFSET/FETCH is not supported","messagePattern":"Locking with OFFSET/FETCH is not supported","errorType":"exception","errorClass":"IllegalQueryOperationException","httpStatus":null,"severity":"error","filePath":"hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/OracleLegacySqlAstTranslator.java","lineNumber":182,"sourceCode":"\t\t\t\tstrategy = LockStrategy.FOLLOW_ON;\n\t\t\t}\n\t\t}\n\n\t\tif ( strategy != LockStrategy.FOLLOW_ON && hasSetOperations( querySpec ) ) {\n\t\t\tif ( followOnStrategy == Locking.FollowOn.DISALLOW ) {\n\t\t\t\tthrow new IllegalQueryOperationException( \"Locking with set operators is not supported\" );\n\t\t\t}\n\t\t\telse if ( followOnStrategy != Locking.FollowOn.IGNORE ) {\n\t\t\t\tstrategy = LockStrategy.NONE;\n\t\t\t}\n\t\t\telse {\n\t\t\t\tstrategy = LockStrategy.FOLLOW_ON;\n\t\t\t}\n\t\t}\n\n\t\tif ( strategy != LockStrategy.FOLLOW_ON && needsLockingWrapper( querySpec ) && !canApplyLockingWrapper( querySpec ) ) {\n\t\t\tif ( followOnStrategy == Locking.FollowOn.DISALLOW ) {\n\t\t\t\tthrow new IllegalQueryOperationException( \"Locking with OFFSET/FETCH is not supported\" );\n\t\t\t}\n\t\t\telse if ( followOnStrategy != Locking.FollowOn.IGNORE ) {\n\t\t\t\tstrategy = LockStrategy.NONE;\n\t\t\t}\n\t\t\telse {\n\t\t\t\tstrategy = LockStrategy.FOLLOW_ON;\n\t\t\t}\n\t\t}\n\n\t\treturn strategy;\n\t}\n\n\tprivate boolean hasSetOperations(QuerySpec querySpec) {\n\t\treturn querySpec.getFromClause().queryTableGroups( group -> group instanceof UnionTableGroup ? group : null ) != null;\n\t}\n\n\tprivate boolean isPartOfQueryGroup() {\n\t\treturn getQueryPartStack().findCurrentFirst( part -> part instanceof QueryGroup ? part : null ) != null;","sourceCodeStart":164,"sourceCodeEnd":200,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/OracleLegacySqlAstTranslator.java#L164-L200","documentation":"Third guard in OracleLegacySqlAstTranslator.determineLockingStrategy(): when the query needs Oracle's locking wrapper (because it carries OFFSET/FETCH, e.g. emulated pagination on pre-12c Oracle) but the wrapper cannot be applied, the strategy must fall back to follow-on locking. If follow-on locking is disallowed, translation throws IllegalQueryOperationException('Locking with OFFSET/FETCH is not supported').","triggerScenarios":"Pessimistic locking combined with setFirstResult/setMaxResults (limit/offset) on OracleLegacyDialect — especially pre-12c servers where OFFSET/FETCH is emulated — while follow-on locking is disallowed.","commonSituations":"Batch jobs that try to lock a page of rows ('first N rows for update'); applications combining LockModeType.PESSIMISTIC_WRITE with pageable queries and follow-on locking disabled.","solutions":["Allow follow-on locking so Hibernate applies FOR UPDATE in a follow-up query","Upgrade to Oracle 12c+ so native OFFSET/FETCH exists and the locking wrapper can apply","Split into two queries: paginate an unlocked id query, then lock and fetch exactly those ids","Use keyset pagination plus an explicit 'select ... for update' on the id set"],"exampleFix":"// before: lock + pagination with follow-on locking disallowed\nList<Order> page = session.createQuery( hql, Order.class )\n        .setFirstResult( 100 ).setMaxResults( 50 )\n        .setLockMode( \"o\", LockMode.PESSIMISTIC_WRITE )\n        .list(); // -> IllegalQueryOperationException\n\n// after: paginate ids unlocked, then lock exactly those rows\nList<Long> ids = session.createQuery( \"select o.id from Order o order by o.id\", Long.class )\n        .setFirstResult( 100 ).setMaxResults( 50 ).list();\nList<Order> page = session.createQuery( \"select o from Order o where o.id in :ids\", Order.class )\n        .setParameter( \"ids\", ids )\n        .setLockMode( \"o\", LockMode.PESSIMISTIC_WRITE ).list();","handlingStrategy":"try-catch","validationCode":"boolean pagination = firstResult > 0 || maxResults > 0;\nboolean locked = lockMode.greaterThan( LockMode.OPTIMISTIC );\nif ( pagination && locked && !followOnLockingEnabled ) {\n    // either enable follow-on locking or split: paginate ids, then lock by ids\n}","typeGuard":null,"tryCatchPattern":"try {\n    return query.list();\n}\ncatch ( IllegalQueryOperationException e ) {\n    if ( e.getMessage() != null && e.getMessage().contains( \"OFFSET/FETCH\" ) ) {\n        // split into unlocked id pagination + 'where id in :ids for update'\n        throw e;\n    }\n    throw e;\n}","preventionTips":["Never combine setFirstResult/setMaxResults with pessimistic locks while follow-on locking is disabled","On pre-12c Oracle, paginate and lock in two separate queries","Consider keyset pagination for locked batch processing"],"tags":["oracle","pessimistic-locking","pagination","offset-fetch"],"backgroundTag":"pessimistic-locking-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}