hibernate/hibernate-orm · error · IllegalQueryOperationException

Locking with OFFSET/FETCH is not supported

Error message

Locking with OFFSET/FETCH is not supported

What it means

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').

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/OracleLegacySqlAstTranslator.java:182

				strategy = LockStrategy.FOLLOW_ON;
			}
		}

		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 ) && !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( part -> part instanceof QueryGroup ? part : null ) != null;

View on GitHub (pinned to fad1729dce)

Solutions

1. Allow follow-on locking so Hibernate applies FOR UPDATE in a follow-up query
2. Upgrade to Oracle 12c+ so native OFFSET/FETCH exists and the locking wrapper can apply
3. Split into two queries: paginate an unlocked id query, then lock and fetch exactly those ids
4. Use keyset pagination plus an explicit 'select ... for update' on the id set

Example fix

// before: lock + pagination with follow-on locking disallowed
List<Order> page = session.createQuery( hql, Order.class )
        .setFirstResult( 100 ).setMaxResults( 50 )
        .setLockMode( "o", LockMode.PESSIMISTIC_WRITE )
        .list(); // -> IllegalQueryOperationException

// after: paginate ids unlocked, then lock exactly those rows
List<Long> ids = session.createQuery( "select o.id from Order o order by o.id", Long.class )
        .setFirstResult( 100 ).setMaxResults( 50 ).list();
List<Order> page = session.createQuery( "select o from Order o where o.id in :ids", Order.class )
        .setParameter( "ids", ids )
        .setLockMode( "o", LockMode.PESSIMISTIC_WRITE ).list();

boolean pagination = firstResult > 0 || maxResults > 0;
boolean locked = lockMode.greaterThan( LockMode.OPTIMISTIC );
if ( pagination && locked && !followOnLockingEnabled ) {
    // either enable follow-on locking or split: paginate ids, then lock by ids
}

try {
    return query.list();
}
catch ( IllegalQueryOperationException e ) {
    if ( e.getMessage() != null && e.getMessage().contains( "OFFSET/FETCH" ) ) {
        // split into unlocked id pagination + 'where id in :ids for update'
        throw e;
    }
    throw e;
}

When it happens

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

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

Related errors

• Locking with OFFSET/FETCH is not supported
• Locking with set operators is not supported
• Can't emulate offset clause in subquery
• Can't emulate offset clause in subquery
• Can't emulate offset fetch clause in subquery

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