hibernate/hibernate-orm · error · IllegalQueryOperationException

Locking with OFFSET/FETCH is not supported

Error message

Locking with OFFSET/FETCH is not supported

What it means

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.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/dialect/sql/ast/OracleSqlAstTranslator.java:197

		}

		if ( strategy != LockStrategy.FOLLOW_ON && hasSetOperations( querySpec ) ) {
			if ( followOnStrategy == Locking.FollowOn.DISALLOW ) {
				throw new IllegalQueryOperationException( "Locking with set operators is not supported" );
			}
			else if ( followOnStrategy == Locking.FollowOn.IGNORE ) {
				strategy = LockStrategy.NONE;
			}
			else {
				strategy = LockStrategy.FOLLOW_ON;
			}
		}

		if ( strategy != LockStrategy.FOLLOW_ON
				&& needsLockingWrapper( querySpec, followOnStrategy )
				&& !canApplyLockingWrapper( querySpec ) ) {
			if ( followOnStrategy == Locking.FollowOn.DISALLOW ) {
				throw new IllegalQueryOperationException( "Locking with OFFSET/FETCH is not supported" );
			}
			else if ( followOnStrategy == Locking.FollowOn.IGNORE ) {
				strategy = LockStrategy.NONE;
			}
			else {
				strategy = LockStrategy.FOLLOW_ON;
			}
		}

		return strategy;
	}

	private boolean hasSetOperations(QuerySpec querySpec) {
		return querySpec.getFromClause().queryTableGroups( group -> group instanceof UnionTableGroup ? group : null ) != null;
	}

	private boolean isPartOfQueryGroup() {
		return getQueryPartStack().findCurrentFirst( OracleSqlAstTranslator::partIsQueryGroup ) != null;

View on GitHub (pinned to fad1729dce)

Solutions

  1. 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)
  2. Remove join fetches of collections from the locked paginated query so canApplyLockingWrapper returns true and Oracle wraps it
  3. Don't disallow follow-on locking: let the translator choose FOLLOW_ON (e.g. drop setFollowOnLocking constraints) or skip locking for the paged read
  4. Page in memory: lock a bounded id set first, then slice

Example fix

// before
List<Order> page = em.createQuery("select o from Order o join fetch o.lines where o.status = :s", Order.class)
    .setFirstResult(200).setMaxResults(50)
    .setLockMode(LockModeType.PESSIMISTIC_WRITE)  // Locking with OFFSET/FETCH is not supported
    .getResultList();

// after: page ids unlocked, then lock exactly those rows
List<Long> ids = em.createQuery("select o.id from Order o where o.status = :s order by o.id", Long.class)
    .setFirstResult(200).setMaxResults(50).getResultList();
List<Order> page = em.createQuery("select o from Order o join fetch o.lines where o.id in :ids", Order.class)
    .setParameter("ids", ids).setLockMode(LockModeType.PESSIMISTIC_WRITE).getResultList();
Defensive patterns

Strategy: validation

Validate before calling

// Oracle cannot FOR UPDATE + OFFSET/FETCH: don't combine paging with locks
boolean paged = firstResult > 0 || maxResults != Integer.MAX_VALUE;
if (paged && lockMode != LockModeType.NONE) {
    // two-step: page ids unlocked, then lock exactly those ids in a second query
    List<ID> ids = em.createQuery(idHql, idType)
        .setFirstResult(firstResult).setMaxResults(maxResults).getResultList();
    return em.createQuery(entityHqlByIds, entityType)
        .setParameter("ids", ids).setLockMode(lockMode).getResultList();
}

Try / catch

try {
    return em.createQuery(hql, cls).setFirstResult(f).setMaxResults(m)
        .setLockMode(LockModeType.PESSIMISTIC_WRITE).getResultList();
} catch (IllegalQueryOperationException e) {
    if (e.getMessage().startsWith("Locking with OFFSET/FETCH")) {
        return lockPagedIds(hql, f, m); // fallback: id-then-lock pattern
    }
    throw e;
}

Prevention

When it happens

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

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

Related errors

AI-assisted analysis of hibernate/hibernate-orm@fad1729dce (2026-08-22). Data as JSON: /api/errors/efff757dbd393ab1. Report an issue: GitHub.