{"record":{"id":"0006ce0c0909e30f","repo":"hibernate/hibernate-orm","slug":"locking-with-set-operators-is-not-supported-0006ce","errorCode":null,"errorMessage":"Locking with set operators is not supported","messagePattern":"Locking with set operators is not supported","errorType":"exception","errorClass":"IllegalQueryOperationException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/dialect/sql/ast/OracleSqlAstTranslator.java","lineNumber":171,"sourceCode":"\t\t}\n\t\tsuper.visitSqlSelection( sqlSelection );\n\t}\n\n\t@Override\n\tprotected LockStrategy determineLockingStrategy(\n\t\t\tQuerySpec querySpec,\n\t\t\tLocking.FollowOn followOnStrategy) {\n\t\tif ( followOnStrategy == Locking.FollowOn.FORCE ) {\n\t\t\treturn LockStrategy.FOLLOW_ON;\n\t\t}\n\n\t\tLockStrategy strategy = super.determineLockingStrategy( querySpec, followOnStrategy );\n\n\t\t// Oracle also doesn't support locks with set operators\n\t\t// See https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm#i2066346\n\t\tif ( strategy != LockStrategy.FOLLOW_ON && isPartOfQueryGroup() ) {\n\t\t\tif ( followOnStrategy == Locking.FollowOn.DISALLOW ) {\n\t\t\t\tthrow new IllegalQueryOperationException( \"Locking with set operators is not supported\" );\n\t\t\t}\n\t\t\telse if ( followOnStrategy == Locking.FollowOn.IGNORE ) {\n\t\t\t\tstrategy = LockStrategy.NONE;\n\t\t\t}\n\t\t\telse {\n\t\t\t\tstrategy = LockStrategy.FOLLOW_ON;\n\t\t\t}\n\t\t}\n\n\t\tif ( strategy != LockStrategy.FOLLOW_ON && hasSetOperations( querySpec ) ) {\n\t\t\tif ( followOnStrategy == Locking.FollowOn.DISALLOW ) {\n\t\t\t\tthrow new IllegalQueryOperationException( \"Locking with set operators is not supported\" );\n\t\t\t}\n\t\t\telse if ( followOnStrategy == Locking.FollowOn.IGNORE ) {\n\t\t\t\tstrategy = LockStrategy.NONE;\n\t\t\t}\n\t\t\telse {\n\t\t\t\tstrategy = LockStrategy.FOLLOW_ON;","sourceCodeStart":153,"sourceCodeEnd":189,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/sql/ast/OracleSqlAstTranslator.java#L153-L189","documentation":"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).","triggerScenarios":"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.","commonSituations":"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.","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"],"exampleFix":"// before\nList<Long> ids = em.createQuery(\"select o.id from A o where ... union select b.id from B b where ...\", Long.class)\n    .setLockMode(LockModeType.PESSIMISTIC_WRITE)  // throws on Oracle\n    .getResultList();\n\n// after: read ids unlocked, then lock individually\nList<Long> ids = em.createQuery(\"...\", Long.class).getResultList();\nfor (Long id : ids) {\n    em.find(EntityA.class, id, LockModeType.PESSIMISTIC_WRITE);\n}","handlingStrategy":"validation","validationCode":"// Do not hand Hibernate a lock request it must refuse: check the query shape first\nstatic boolean lockableQuery(String hql, boolean paged) {\n    String upper = hql.toUpperCase();\n    return !upper.contains(\" UNION \") && !upper.contains(\" INTERSECT \") && !upper.contains(\" EXCEPT \") && !paged;\n}\n\nif (!lockableQuery(hql, firstResult >= 0)) {\n    // fetch ids unlocked, then lock each entity individually\n}","typeGuard":null,"tryCatchPattern":"try {\n    return q.setLockMode(LockModeType.PESSIMISTIC_WRITE).getResultList();\n} catch (IllegalQueryOperationException e) {\n    if (e.getMessage().contains(\"Locking with set operators\")) {\n        // degrade: run unlocked, then session.lock per row\n        List<ID> ids = q.getResultList();\n        return loadAndLock(ids);\n    }\n    throw e;\n}","preventionTips":["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"],"tags":["hibernate","oracle","locking","pessimistic-lock","for-update","union","query-translation"],"backgroundTag":"for-update-with-set-operators","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}