hibernate/hibernate-orm · error · IllegalQueryOperationException

Locking with DISTINCT is not supported

Error message

Locking with DISTINCT is not supported

What it means

determineLockingStrategy treats a DISTINCT select the same way: databases either reject or give surprising results combining DISTINCT with FOR UPDATE, so the strategy is demoted to follow-on locking, and with FollowOn.DISALLOW (the JPA pessimistic default) translation fails with IllegalQueryOperationException("Locking with DISTINCT is not supported").

Source

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

			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() ) {
			if ( lockingClauseStrategy != null && lockingClauseStrategy.containsOuterJoins() ) {
				// we have any outer joins to lock, but the dialect does not support locking outer joins
				// 		-we need to use follow-on locking if allowed
				if ( followOnStrategy == Locking.FollowOn.DISALLOW ) {
					throw new IllegalQueryOperationException( "Locking with OUTER joins is not supported" );
				}
				else if ( followOnStrategy == Locking.FollowOn.IGNORE ) {
					return LockStrategy.NONE;
				}
				strategy = LockStrategy.FOLLOW_ON;

View on GitHub (pinned to fad1729dce)

Solutions

1. Permit follow-on locking on this query via LockOptions.setFollowOnLocking(true) (or HibernateHints HINT_FOLLOW_ON_LOCKING), accepting the extra locking statements.
2. Remove DISTINCT and deduplicate in Java (Set collector), letting a native locking clause be used.
3. Avoid join-fetch + DISTINCT + pessimistic lock together; lock via a separate id-select ... for update.

Example fix

// before — distinct + JPA pessimistic lock
List<Employee> es = em.createQuery("select distinct e from Employee e join fetch e.projects", Employee.class)
    .setLockMode(LockModeType.PESSIMISTIC_WRITE).getResultList();

// after — dedupe in Java, keep native FOR UPDATE
List<Employee> es = em.createQuery("select e from Employee e join fetch e.projects", Employee.class)
    .setLockMode(LockModeType.PESSIMISTIC_WRITE)
    .getResultStream().distinct().toList();

boolean distinct = hql.toLowerCase().contains("select distinct")
        || criteriaQuery.isDistinct();
if (distinct && lockMode != null && lockMode.isPessimistic()) {
    criteriaQuery.setDistinct(false); // dedupe in Java instead; keeps native FOR UPDATE usable
}

try { em.createQuery(hql).setLockMode(LockModeType.PESSIMISTIC_WRITE).getResultList(); }
catch (org.hibernate.query.IllegalQueryOperationException e) {
    if (e.getMessage().equals("Locking with DISTINCT is not supported")) {
        List<T> rows = em.createQuery(hql.replace("select distinct", "select"))
            .setLockMode(LockModeType.PESSIMISTIC_WRITE).getResultList();
        results = rows.stream().distinct().toList();
    } else { throw e; }
}

When it happens

Trigger: A DISTINCT query (HQL 'select distinct', criteria .distinct(true)) executed with a JPA pessimistic lock mode (setLockMode PESSIMISTIC_WRITE/FORCE_INCREMENT) which implies follow-on locking is disallowed.

Common situations: Deduplicating joined-fetch results while pessimistically locking; @Lock on Spring Data repository queries that also use @Distinct or distinct projections; entity graphs with join fetch (auto-distinct) plus pessimistic locks.

Related errors

• Locking with GROUP BY is not supported
• Locking with HAVING 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/b5be3d5aab640c01. Report an issue: GitHub.