What causes "Locking with set operators is not supported" in hibernate/hibernate-orm?

Don't lock the set-operation query directly: fetch the ids/keys first, then lock each entity via session.find(entity, id, lockMode) or session.lock

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 combine FOR UPDATE locking with set operators (UNION/UNION ALL/INTERSECT/EXCEPT) -- the translator cites the Oracle SQL reference in the source comment. OracleSqlAstTranslator.determineLockingStrategy checks isPartOfQueryGroup(): when the query part being locked is one branch of a query group and follow-on locking is not an option, it must either downgrade (IGNORE), fall back to FOLLOW_ON, or throw IllegalQueryOperationException('Locking with set operators is not supported') when the caller disallowed follow-on locking (Locking.FollowOn.DISALLOW).

Source

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

		}
		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

Don't lock the set-operation query directly: fetch the ids/keys first, then lock each entity via session.find(entity, id, lockMode) or session.lock
Restructure the query so the UNION becomes a derived table and the lock applies to a simple outer SELECT (the locking-wrapper path)
Stop forcing/disallowing follow-on locking (remove setFollowOnLocking(true)) so the translator can pick FOLLOW_ON or NONE instead of throwing
Use optimistic (@Version) locking for this query instead of pessimistic FOR UPDATE

Example fix

// before
List<Long> ids = em.createQuery("select o.id from A o where ... union select b.id from B b where ...", Long.class)
    .setLockMode(LockModeType.PESSIMISTIC_WRITE)  // throws on Oracle
    .getResultList();

// after: read ids unlocked, then lock individually
List<Long> ids = em.createQuery("...", Long.class).getResultList();
for (Long id : ids) {
    em.find(EntityA.class, id, LockModeType.PESSIMISTIC_WRITE);
}

Defensive patterns

Strategy: validation

Validate before calling

// Do not hand Hibernate a lock request it must refuse: check the query shape first
static boolean lockableQuery(String hql, boolean paged) {
    String upper = hql.toUpperCase();
    return !upper.contains(" UNION ") && !upper.contains(" INTERSECT ") && !upper.contains(" EXCEPT ") && !paged;
}

if (!lockableQuery(hql, firstResult >= 0)) {
    // fetch ids unlocked, then lock each entity individually
}

Try / catch

try {
    return q.setLockMode(LockModeType.PESSIMISTIC_WRITE).getResultList();
} catch (IllegalQueryOperationException e) {
    if (e.getMessage().contains("Locking with set operators")) {
        // degrade: run unlocked, then session.lock per row
        List<ID> ids = q.getResultList();
        return loadAndLock(ids);
    }
    throw e;
}

Prevention

Never apply FOR UPDATE to a UNION/INTERSECT/EXCEPT query on Oracle; lock by id instead
Avoid setFollowOnLocking(true) on set-operation queries -- the internal re-query disallows follow-on and throws
Keep a helper that locks a list of ids (session.find/lock) and use it for all composite queries

When it happens

Trigger: Applying a pessimistic lock (Query.setLockMode(PESSIMISTIC_WRITE), LockOptions, session.lock-driven locking) to a query whose root is a set operation ('select ... union select ...'), while follow-on locking is disallowed. The most common source of DISALLOW is Hibernate's own FollowOnLockingAction, which re-executes the id-determining query with LockOptions.setFollowOnStrategy(FollowOn.DISALLOW) after you enable setFollowOnLocking(true) -- if that re-query still touches a query group, this throw fires.

Common situations: Calling setFollowOnLocking(true) on a UNION/INTERSECT query; follow-on locking auto-kicking in on a paginated UNION query and failing on its internal id fetch; migrating locking patterns that worked on MySQL/PostgreSQL to Oracle.

Related errors

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