hibernate/hibernate-orm · error · IllegalQueryOperationException

Locking with HAVING is not supported

Error message

Locking with HAVING is not supported

What it means

Same decision tree in determineLockingStrategy, next branch: a query with HAVING restrictions cannot use a native locking clause, so it needs follow-on locking; when the follow-on strategy is DISALLOW (JPA pessimistic lock default) Hibernate throws IllegalQueryOperationException("Locking with HAVING is not supported") rather than silently locking nothing.

Source

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

		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() ) {
			if ( followOnStrategy == Locking.FollowOn.DISALLOW ) {
				throw new IllegalQueryOperationException( "Locking with DISTINCT is not supported" );
			}
			else if ( followOnStrategy == Locking.FollowOn.IGNORE ) {
				return LockStrategy.NONE;
			}
			strategy = LockStrategy.FOLLOW_ON;
		}

		if ( !dialect.supportsOuterJoinForUpdate() ) {

View on GitHub (pinned to fad1729dce)

Solutions

1. Enable follow-on locking for the statement (LockOptions.setFollowOnLocking(true)) so Hibernate uses a post-select locking query.
2. Remove the lock from the HAVING query and lock targeted ids in a dedicated FOR UPDATE query.
3. Rewrite so the HAVING filter runs unlocked and the locked part is a simple root select.

Example fix

// before
List<Object[]> r = em.createQuery(
    "select o.customer, sum(o.total) from Order o group by o.customer having sum(o.total) > :min", Object[].class)
    .setLockMode(LockModeType.PESSIMISTIC_WRITE).getResultList();

// after — allow follow-on locking
List<Object[]> r = em.createQuery(
    "select o.customer, sum(o.total) from Order o group by o.customer having sum(o.total) > :min", Object[].class)
    .unwrap(org.hibernate.query.Query.class)
    .setLockOptions(new org.hibernate.LockOptions(org.hibernate.LockMode.PESSIMISTIC_WRITE).setFollowOnLocking(true))
    .getResultList();

boolean hasHaving = hql.toLowerCase().contains(" having");
if (hasHaving && lockMode != null && lockMode.isPessimistic()) {
    lockOptions.setFollowOnLocking(true); // allow the follow-on strategy instead of failing
}

try { em.createQuery(hql).setLockMode(LockModeType.PESSIMISTIC_WRITE).getResultList(); }
catch (org.hibernate.query.IllegalQueryOperationException e) {
    if (e.getMessage().equals("Locking with HAVING 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; }
}

When it happens

Trigger: Pessimistic lock mode with follow-on disallowed on a query containing a HAVING clause, e.g., 'select ... group by ... having count(*) > :min' with setLockMode(LockModeType.PESSIMISTIC_WRITE).

Common situations: Locking filtered aggregate/reports; repository methods with @Lock plus dynamic having clauses; migrating from Hibernate 5 where follow-on behavior defaulted differently.

Related errors

• Locking with GROUP BY is not supported
• Locking with DISTINCT is not supported
• Locking with aggregate functions is not supported
• Duplicate named query '%s'
• Duplicate named stored procedure '{}'

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