hibernate/hibernate-orm · error · IllegalQueryOperationException

Locking with GROUP BY is not supported

Error message

Locking with GROUP BY is not supported

What it means

determineLockingStrategy decides between a native FOR UPDATE clause, follow-on locking, or no locking. Databases cannot combine SELECT ... FOR UPDATE with GROUP BY sensibly, so a non-empty group-by clause forces follow-on locking; if the follow-on strategy is DISALLOW (the JPA pessimistic-lock default, where locking must happen in the same query), it throws IllegalQueryOperationException instead.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java:1932

		return AggregateFunctionChecker.hasAggregateFunctions( querySpec );
	}

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

		if ( !querySpec.isRoot() ) {
			followOnStrategy = Locking.FollowOn.ALLOW;
		}

		LockStrategy strategy = LockStrategy.CLAUSE;

		if ( !querySpec.getGroupByClauseExpressions().isEmpty() ) {
			if ( followOnStrategy == Locking.FollowOn.DISALLOW ) {
				throw new IllegalQueryOperationException( "Locking with GROUP BY is not supported" );
			}
			else if ( followOnStrategy == Locking.FollowOn.IGNORE ) {
				return LockStrategy.NONE;
			}
			strategy = LockStrategy.FOLLOW_ON;
		}

		if ( querySpec.getHavingClauseRestrictions() != null ) {
			if ( followOnStrategy == Locking.FollowOn.DISALLOW ) {
				throw new IllegalQueryOperationException( "Locking with HAVING is not supported" );
			}
			else if ( followOnStrategy == Locking.FollowOn.IGNORE ) {
				return LockStrategy.NONE;
			}
			strategy = LockStrategy.FOLLOW_ON;
		}

		if ( querySpec.getSelectClause().isDistinct() ) {

View on GitHub (pinned to fad1729dce)

Solutions

  1. Allow follow-on locking for this query so Hibernate locks rows afterwards: query.setLockOptions(new LockOptions(LockMode.PESSIMISTIC_WRITE).setFollowOnLocking(true)) or the equivalent hint.
  2. Drop the lock from the aggregate query — grouping queries rarely benefit from row locks on aggregates.
  3. Lock the underlying rows in a separate root-level query (select ids ... for update) and then run the aggregate.

Example fix

// before — JPA pessimistic lock (follow-on DISALLOW) on a grouped query
List<Tuple> r = em.createQuery("select e.dept, count(e) from Employee e group by e.dept", Tuple.class)
    .setLockMode(LockModeType.PESSIMISTIC_WRITE).getResultList();

// after — explicitly allow follow-on locking via Hibernate LockOptions
List<Tuple> r = em.createQuery("select e.dept, count(e) from Employee e group by e.dept", Tuple.class)
    .unwrap(org.hibernate.query.Query.class)
    .setLockOptions(new org.hibernate.LockOptions(org.hibernate.LockMode.PESSIMISTIC_WRITE)
        .setFollowOnLocking(true))
    .getResultList();
Defensive patterns

Strategy: fallback

Validate before calling

boolean grouped = hql.toLowerCase().contains(" group by");
if (grouped && lockMode != null && lockMode.isPessimistic()) {
    // grouping forces follow-on locking; JPA lock modes disallow it
    lockOptions.setFollowOnLocking(true);
}

Try / catch

try { em.createQuery(hql).setLockMode(LockModeType.PESSIMISTIC_WRITE).getResultList(); }
catch (org.hibernate.query.IllegalQueryOperationException e) {
    if (e.getMessage().equals("Locking with GROUP BY is not supported")) {
        em.createQuery(hql).unwrap(org.hibernate.query.Query.class)
          .setLockOptions(new org.hibernate.LockOptions(org.hibernate.LockMode.PESSIMISTIC_WRITE).setFollowOnLocking(true))
          .getResultList();
    } else { throw e; }
}

Prevention

When it happens

Trigger: A query with GROUP BY executed with a pessimistic lock mode whose follow-on strategy is DISALLOW — e.g., em.createQuery(...).setLockMode(LockModeType.PESSIMISTIC_WRITE) on 'select e.dept, count(e) from Employee e group by e.dept'.

Common situations: Applying JPA pessimistic lock modes to reporting/aggregation queries; adding @Lock(PESSIMISTIC_WRITE) to repository methods that aggregate; blanket lock-mode aspects (e.g., Spring @Lock annotations) hitting grouped queries.

Related errors

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