hibernate/hibernate-orm · error · IllegalQueryOperationException

Locking with set operators is not supported

Error message

Locking with set operators is not supported

What it means

Oracle cannot apply FOR UPDATE to a SELECT participating in a set operation (union/intersect/minus; see the Oracle docs link in the source). OracleLegacySqlAstTranslator.determineLockingStrategy() normally degrades to follow-on locking (rows locked by a follow-up query); when follow-on locking is disallowed (FollowOn.DISALLOW, e.g. hibernate.query.followOnLocking=false) it throws IllegalQueryOperationException instead as soon as the query group contains set operators.

Source

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

		}
		super.visitSqlSelection( sqlSelection );
	}

	@Override
	protected LockStrategy determineLockingStrategy(
			QuerySpec querySpec,
			Locking.FollowOn followOnStrategy) {
		if ( followOnStrategy == Locking.FollowOn.FORCE ) {
			return LockStrategy.FOLLOW_ON;
		}

		LockStrategy strategy = super.determineLockingStrategy( querySpec, followOnStrategy );

		// Oracle also doesn't support locks with set operators
		// See https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm#i2066346
		if ( strategy != LockStrategy.FOLLOW_ON && isPartOfQueryGroup() ) {
			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 && 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;

View on GitHub (pinned to fad1729dce)

Solutions

  1. Re-enable follow-on locking (remove the hibernate.query.followOnLocking=false setting) so Hibernate applies the lock in a follow-up select
  2. Restructure the query to remove set operators around the locked query spec (compose results in the application)
  3. Run the union query unlocked and lock rows separately with a native 'select ... for update' on the key set

Example fix

// before: follow-on locking disallowed + union
List<Order> l = session.createQuery( unionHql, Order.class )
        .setLockMode( "o", LockMode.PESSIMISTIC_WRITE )
        .list(); // -> IllegalQueryOperationException

// after: allow follow-on locking (drop hibernate.query.followOnLocking=false)
List<Order> l = session.createQuery( unionHql, Order.class )
        .setLockMode( "o", LockMode.PESSIMISTIC_WRITE ) // follow-up select applies the lock
        .list();
Defensive patterns

Strategy: try-catch

Validate before calling

boolean hasSetOps(String hql) {
    return hql != null && hql.toLowerCase( Locale.ROOT ).matches( "(?s).*\\b(union|intersect|except)\\b.*" );
}

if ( hasSetOps( hql ) && lockMode.greaterThan( LockMode.OPTIMISTIC ) ) {
    // run unlocked or allow follow-on locking instead of failing translation
}

Try / catch

try {
    return query.list();
}
catch ( IllegalQueryOperationException e ) {
    if ( e.getMessage() != null && e.getMessage().contains( "set operators" ) ) {
        query.setLockOptions( LockOptions.NONE ); // re-run unlocked, lock separately by ids
        return query.list();
    }
    throw e;
}

Prevention

When it happens

Trigger: HQL/Criteria using union/intersect/except combined with a pessimistic lock (setLockMode(PESSIMISTIC_WRITE) or LockOptions) while follow-on locking is disabled, on OracleLegacyDialect.

Common situations: Follow-on locking switched off globally for performance; a pessimistic lock added to an existing union-based search or report query; pagination plus locking on merged result sets.

Related errors

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