Hibernate does not correcly apply LockMode.UPGRADE when performing a polymorphic select to a leaf-class via a mapped superclass on Oracle ----------------------------------------------------------------------------------------------------------------------------------------- Key: HHH-3298 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3298 Project: Hibernate3 Issue Type: Bug Components: core Affects Versions: 3.2.6 Environment: Hibernate 3.2.6, Oracle 10gR2 Reporter: Mike Everett Attachments: src.zip Greetings All, I'm experiencing an issue with Hibernate generated SQL when I attempt to retrieve a union-subclass entity via a concrete mapped superclass with LockMode.UPGRADE. Hibernate will generate SQL with a UNION ALL of the sub-classes and then apply FOR UPDATE to the query which is not allowed with Oracle. This leads to the following error: java.sql.SQLException: ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc. It seems that "etc." part includes UNION and UNION ALL. Example with three classes: Entity (abstract) <--extends--- ConcreteInheritorOne (concrete) <--extends-- ConcreteInheritorTwo (concrete) assume: inheritorTwoId == the id of an item of class ConcreteInheritorTwo session == a Hibernate Session // These lookups will cause the error Entity e = (Entity) session.get(Entity.class, inheritorTwoId, LockMode.UPGRADE); Entity e = (Entity) session.get(ConcreteInheritorOne.class, inheritorTwoId, LockMode.UPGRADE); // This will not cause the error Entity e = (Entity) session.get(ConcreteInheritorTwo.class, inheritorTwoId, LockMode.UPGRADE); I have attached entity classes and a Test class which illustrate this effect. I spelunked into the code a bit, but an easy fix did not present itself to my hibernate-code-virgin eyes. Thanks for making Hibernate great! --Mje #### Workaround #### If anyone else is experiencing this problem, I've implemented code similar to the method below as a work-around in an abstraction layer: public Object getEntityForUpdate(Class entityClass, String entityId) { Session s = getSession(); ClassMetadata meta = s.getSessionFactory().getClassMetadata(entityClass); Boolean doTwoPhaseLock = false; if(meta instanceof UnionSubclassEntityPersister) { if(((UnionSubclassEntityPersister)meta).getEntityMetamodel().getSubclassEntityNames().size() > 1) doTwoPhaseLock = true; } if(doTwoPhaseLock) { Entity e = (Entity)s.get(entityClass, entityId); System.out.println("Using Two-Phase Lock on: EntityId[" + entityId + "] EntityClass[" + entityClass.getName() + "]"); s.lock(e, LockMode.UPGRADE); return e; } else { System.out.println("Using One-Phase Lock on: EntityId[" + entityId + "] EntityClass[" + entityClass.getName() + "]"); return s.get(entityClass, entityId, LockMode.UPGRADE); } } #### Hibernate Generated SQL Example #### select entity0_.id as id6_0_, entity0_.versionNumber as versionN2_6_0_, entity0_.clazz_ as clazz_0_ from ( select versionNumber, id, 2 as clazz_ from BASE_ConcreteInheritorTwo union all select versionNumber, id, 1 as clazz_ from BASE_ConcreteInheritorOne ) entity0_ where entity0_.id=? for update #### Stack Trace #### 15:18:14,484 ERROR JDBCExceptionReporter:78 - ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc. 15:18:14,484 INFO DefaultLoadEventListener:111 - Error performing load command org.hibernate.exception.SQLGrammarException: could not load an entity: [org.example.domain.hibernate.Entity#1211505494256] at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.loadEntity(Loader.java:1874) at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:48) at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:42) at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3049) at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:399) at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:375) at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:139) at org.hibernate.event.def.DefaultLoadEventListener.lockAndLoad(DefaultLoadEventListener.java:297) at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:106) at org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:878) at org.hibernate.impl.SessionImpl.get(SessionImpl.java:869) at org.hibernate.impl.SessionImpl.get(SessionImpl.java:864) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:301) at $Proxy0.get(Unknown Source) at org.example.test.Test.doTest2(Test.java:68) at org.example.test.Test.main(Test.java:35) Caused by: java.sql.SQLException: ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc. at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:810) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039) at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:850) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1134) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3384) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186) at org.hibernate.loader.Loader.getResultSet(Loader.java:1787) at org.hibernate.loader.Loader.doQuery(Loader.java:674) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236) at org.hibernate.loader.Loader.loadEntity(Loader.java:1860) ... 19 more -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira