{"record":{"id":"efff757dbd393ab1","repo":"hibernate/hibernate-orm","slug":"locking-with-offset-fetch-is-not-supported-efff75","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-core/src/main/java/org/hibernate/dialect/sql/ast/OracleSqlAstTranslator.java","lineNumber":197,"sourceCode":"\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\n\t\t\t\t&& needsLockingWrapper( querySpec, followOnStrategy )\n\t\t\t\t&& !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( OracleSqlAstTranslator::partIsQueryGroup ) != null;","sourceCodeStart":179,"sourceCodeEnd":215,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/sql/ast/OracleSqlAstTranslator.java#L179-L215","documentation":"Oracle cannot combine FOR UPDATE with OFFSET/FETCH pagination. When a locked query has an offset or fetch clause, OracleSqlAstTranslator would need to wrap it in an outer SELECT (locking wrapper); if needsLockingWrapper(...) is true but canApplyLockingWrapper(querySpec) is false (the wrapper is infeasible for this query shape), the only alternatives are follow-on locking or none. With Locking.FollowOn.DISALLOW the translator throws IllegalQueryOperationException('Locking with OFFSET/FETCH is not supported') rather than emit invalid SQL.","triggerScenarios":"A paginated query -- setFirstResult/setMaxResults, or HQL 'offset n fetch next m rows only' -- combined with a pessimistic lock and disallowed follow-on locking. Frequently hit through Hibernate's follow-on locking machinery: FollowOnLockingAction re-queries with FollowOn.DISALLOW, and when that re-query still carries OFFSET/FETCH and cannot be wrapped, this throw fires (e.g., lock + paging + join fetch collections).","commonSituations":"Combining setMaxResults with PESSIMISTIC_WRITE on queries that join-fetch collections or otherwise defeat the locking wrapper; enabling setFollowOnLocking(true) on paginated queries; ticket-queue / worklist screens that page and lock at once.","solutions":["Split the operation: run the paginated id query unlocked, then lock and load the selected entities by id (session.find(..., LockModeType.PESSIMISTIC_WRITE) or an id-list query with the lock)","Remove join fetches of collections from the locked paginated query so canApplyLockingWrapper returns true and Oracle wraps it","Don't disallow follow-on locking: let the translator choose FOLLOW_ON (e.g. drop setFollowOnLocking constraints) or skip locking for the paged read","Page in memory: lock a bounded id set first, then slice"],"exampleFix":"// before\nList<Order> page = em.createQuery(\"select o from Order o join fetch o.lines where o.status = :s\", Order.class)\n    .setFirstResult(200).setMaxResults(50)\n    .setLockMode(LockModeType.PESSIMISTIC_WRITE)  // Locking with OFFSET/FETCH is not supported\n    .getResultList();\n\n// after: page ids unlocked, then lock exactly those rows\nList<Long> ids = em.createQuery(\"select o.id from Order o where o.status = :s order by o.id\", Long.class)\n    .setFirstResult(200).setMaxResults(50).getResultList();\nList<Order> page = em.createQuery(\"select o from Order o join fetch o.lines where o.id in :ids\", Order.class)\n    .setParameter(\"ids\", ids).setLockMode(LockModeType.PESSIMISTIC_WRITE).getResultList();","handlingStrategy":"validation","validationCode":"// Oracle cannot FOR UPDATE + OFFSET/FETCH: don't combine paging with locks\nboolean paged = firstResult > 0 || maxResults != Integer.MAX_VALUE;\nif (paged && lockMode != LockModeType.NONE) {\n    // two-step: page ids unlocked, then lock exactly those ids in a second query\n    List<ID> ids = em.createQuery(idHql, idType)\n        .setFirstResult(firstResult).setMaxResults(maxResults).getResultList();\n    return em.createQuery(entityHqlByIds, entityType)\n        .setParameter(\"ids\", ids).setLockMode(lockMode).getResultList();\n}","typeGuard":null,"tryCatchPattern":"try {\n    return em.createQuery(hql, cls).setFirstResult(f).setMaxResults(m)\n        .setLockMode(LockModeType.PESSIMISTIC_WRITE).getResultList();\n} catch (IllegalQueryOperationException e) {\n    if (e.getMessage().startsWith(\"Locking with OFFSET/FETCH\")) {\n        return lockPagedIds(hql, f, m); // fallback: id-then-lock pattern\n    }\n    throw e;\n}","preventionTips":["Adopt the id-page-then-lock pattern for every paginated pessimistic lock on Oracle","Keep join fetch of collections out of locked paginated queries -- it defeats the locking wrapper","Don't force follow-on locking on paged queries; let the translator pick or skip locking"],"tags":["hibernate","oracle","locking","pessimistic-lock","for-update","pagination","offset-fetch"],"backgroundTag":"for-update-with-offset-fetch","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}