{"record":{"id":"b5be3d5aab640c01","repo":"hibernate/hibernate-orm","slug":"locking-with-distinct-is-not-supported","errorCode":null,"errorMessage":"Locking with DISTINCT is not supported","messagePattern":"Locking with DISTINCT is not supported","errorType":"exception","errorClass":"IllegalQueryOperationException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java","lineNumber":1952,"sourceCode":"\t\t\telse if ( followOnStrategy == Locking.FollowOn.IGNORE ) {\n\t\t\t\treturn LockStrategy.NONE;\n\t\t\t}\n\t\t\tstrategy = LockStrategy.FOLLOW_ON;\n\t\t}\n\n\t\tif ( querySpec.getHavingClauseRestrictions() != null ) {\n\t\t\tif ( followOnStrategy == Locking.FollowOn.DISALLOW ) {\n\t\t\t\tthrow new IllegalQueryOperationException( \"Locking with HAVING is not supported\" );\n\t\t\t}\n\t\t\telse if ( followOnStrategy == Locking.FollowOn.IGNORE ) {\n\t\t\t\treturn LockStrategy.NONE;\n\t\t\t}\n\t\t\tstrategy = LockStrategy.FOLLOW_ON;\n\t\t}\n\n\t\tif ( querySpec.getSelectClause().isDistinct() ) {\n\t\t\tif ( followOnStrategy == Locking.FollowOn.DISALLOW ) {\n\t\t\t\tthrow new IllegalQueryOperationException( \"Locking with DISTINCT is not supported\" );\n\t\t\t}\n\t\t\telse if ( followOnStrategy == Locking.FollowOn.IGNORE ) {\n\t\t\t\treturn LockStrategy.NONE;\n\t\t\t}\n\t\t\tstrategy = LockStrategy.FOLLOW_ON;\n\t\t}\n\n\t\tif ( !dialect.supportsOuterJoinForUpdate() ) {\n\t\t\tif ( lockingClauseStrategy != null && lockingClauseStrategy.containsOuterJoins() ) {\n\t\t\t\t// we have any outer joins to lock, but the dialect does not support locking outer joins\n\t\t\t\t// \t\t-we need to use follow-on locking if allowed\n\t\t\t\tif ( followOnStrategy == Locking.FollowOn.DISALLOW ) {\n\t\t\t\t\tthrow new IllegalQueryOperationException( \"Locking with OUTER joins is not supported\" );\n\t\t\t\t}\n\t\t\t\telse if ( followOnStrategy == Locking.FollowOn.IGNORE ) {\n\t\t\t\t\treturn LockStrategy.NONE;\n\t\t\t\t}\n\t\t\t\tstrategy = LockStrategy.FOLLOW_ON;","sourceCodeStart":1934,"sourceCodeEnd":1970,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java#L1934-L1970","documentation":"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\").","triggerScenarios":"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.","commonSituations":"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.","solutions":["Permit follow-on locking on this query via LockOptions.setFollowOnLocking(true) (or HibernateHints HINT_FOLLOW_ON_LOCKING), accepting the extra locking statements.","Remove DISTINCT and deduplicate in Java (Set collector), letting a native locking clause be used.","Avoid join-fetch + DISTINCT + pessimistic lock together; lock via a separate id-select ... for update."],"exampleFix":"// before — distinct + JPA pessimistic lock\nList<Employee> es = em.createQuery(\"select distinct e from Employee e join fetch e.projects\", Employee.class)\n    .setLockMode(LockModeType.PESSIMISTIC_WRITE).getResultList();\n\n// after — dedupe in Java, keep native FOR UPDATE\nList<Employee> es = em.createQuery(\"select e from Employee e join fetch e.projects\", Employee.class)\n    .setLockMode(LockModeType.PESSIMISTIC_WRITE)\n    .getResultStream().distinct().toList();","handlingStrategy":"fallback","validationCode":"boolean distinct = hql.toLowerCase().contains(\"select distinct\")\n        || criteriaQuery.isDistinct();\nif (distinct && lockMode != null && lockMode.isPessimistic()) {\n    criteriaQuery.setDistinct(false); // dedupe in Java instead; keeps native FOR UPDATE usable\n}","typeGuard":null,"tryCatchPattern":"try { em.createQuery(hql).setLockMode(LockModeType.PESSIMISTIC_WRITE).getResultList(); }\ncatch (org.hibernate.query.IllegalQueryOperationException e) {\n    if (e.getMessage().equals(\"Locking with DISTINCT is not supported\")) {\n        List<T> rows = em.createQuery(hql.replace(\"select distinct\", \"select\"))\n            .setLockMode(LockModeType.PESSIMISTIC_WRITE).getResultList();\n        results = rows.stream().distinct().toList();\n    } else { throw e; }\n}","preventionTips":["Prefer Java-side deduplication (Set/stream().distinct()) over SQL DISTINCT when locking.","Watch entity graphs/join fetch — they can introduce DISTINCT automatically.","Document that DISTINCT + pessimistic lock needs follow-on locking enabled."],"tags":["hibernate","orm","pessimistic-locking","distinct","jpa"],"backgroundTag":"pessimistic-locking-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}