2 Replies Latest reply on Nov 30, 2008 2:08 PM by mazz

    "ORA-02089: COMMIT is not allowed in a subordinate session"

    kurtu2

      Hi, I try to summarize my problem describing its context. I developed a Stateless Session bean (EJB3) and I'm running it on JBoss v. 4.2.2 ("all" configuration). One method, called for instance "operateOnDb()", of the bean makes some operation on the Oracle 10g database (v. 10.2.0.1.0). It operates on 2 different schemas of the database: the method first selects a record from the first schema, then it updates this record and as last step it inserts another record in the second schema. To Summarize the operations on low database level, it does the following operations:
      1. SELECT * FROM SCHEMA1.TABLE1 TB WHERE TB.ID = ?
      2. UPDATE SCHEMA1.TABLE1 SET FIELD = ? WHERE ID = ?
      3. INSERT INTO SCHEMA2.TABLE2 VALUES (?, ?, ?)
      The main constraint is that this operations have to be executed in 1 single (global) transaction (i.e if one of the operations fails, the transaction has to rollback and nothing has to be modified). For doing this I totally delegate the transaction management to the JBoss application server (I don't manage transaction begin or commit at code level). To implement this behaviour I use the following components:
      1. I define 2 XADatasources in jboss, one for each schema:

      <?xml version="1.0" encoding="UTF-8"?>
      <datasources>
       <xa-datasource>
       <jndi-name>schema1DS</jndi-name>
       <track-connection-by-tx>true</track-connection-by-tx>
       <isSameRM-override-value>false</isSameRM-override-value>
       <xa-datasource-class>
       oracle.jdbc.xa.client.OracleXADataSource
       </xa-datasource-class>
       <xa-datasource-property name="URL">jdbc:oracle:thin:@localhost:1521:odb10</xa-datasource-property>
       <xa-datasource-property name="User">SCHEMA1</xa-datasource-property>
       <xa-datasource-property name="Password">SCHEMA1</xa-datasource-property>
       <exception-sorter-class-name>
       org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter
       </exception-sorter-class-name>
       <no-tx-separate-pools/>
       <metadata>
       <type-mapping>Oracle9i</type-mapping>
       </metadata>
       </xa-datasource>
      </datasources>
      
      <?xml version="1.0" encoding="UTF-8"?>
      <datasources>
       <xa-datasource>
       <jndi-name>schema2DS</jndi-name>
       <track-connection-by-tx>true</track-connection-by-tx>
       <isSameRM-override-value>false</isSameRM-override-value>
       <xa-datasource-class>
       oracle.jdbc.xa.client.OracleXADataSource
       </xa-datasource-class>
       <xa-datasource-property name="URL">jdbc:oracle:thin:@localhost:1521:odb10</xa-datasource-property>
       <xa-datasource-property name="User">SCHEMA2</xa-datasource-property>
       <xa-datasource-property name="Password">SCHEMA2</xa-datasource-property>
       <exception-sorter-class-name>
       org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter
       </exception-sorter-class-name>
       <no-tx-separate-pools/>
       <metadata>
       <type-mapping>Oracle9i</type-mapping>
       </metadata>
       </xa-datasource>
      </datasources>
      


      2. I use JPA in combination with Hibernate as persistence provider for interaction with database, setting up the persistence.xml file (as you will notice I specify transaction-type="JTA", i.e. container managed):
      <persistence-unit name="Persistence1PU" transaction-type="JTA">
       <jta-data-source>java:/schema1DS</jta-data-source>
       <properties>
       <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle9Dialect"/>
       <property name="hibernate.hbm2ddl.auto" value="update"/>
       <property name="hibernate.jdbc.batch_size" value="0"/>
       </properties>
       </persistence-unit>
      
       <persistence-unit name="Persistence2PU" transaction-type="JTA">
       <jta-data-source>java:/schema2DS</jta-data-source>
       <properties>
       <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle9Dialect"/>
       <property name="hibernate.hbm2ddl.auto" value="update"/>
       <property name="hibernate.jdbc.batch_size" value="0"/>
       </properties>
       </persistence-unit>
      


      3. I implemented 2 DAO session beans (each DAO bean manages read/write operations on1 schema), where there is the dependency injection of the PersistenceContext from the container, as for example:
      @PersistenceContext(unitName="Persistence1PU")
      private EntityManager em;
      }
      


      4. The session bean method "operateOnDB()" has by default the TransactionAttributType.Required attribute (as all enterprise beans if you don't specify anything else), and uses the 2 DAO beans to operate on the database shemas

      Now, when i run the "operateOnDB()" method, I have an error at the end, just when the bean exits and the container tries to commit to the DB (using the 2-PHASE commit protocol). The source exception I get is an ORACLE exception:
      ORA-02089: COMMIT is not allowed in a subordinate session

      Talking with some DB experts, they told me that the reason for this kind of error is that the container tries to commit in 2 different moments, i.e. it commits to the DB after the first operation and then also at the end of the transaction. But in my code I don't execute commit() statements manually, I always let the container doing it for me. Is it possible that a first commit() is executed in a earlier moment without the permission of the transaction manager? Shouldn't it manage the global transaction by itself, doing the prepare phase first and then the commit only at the end? Or do you have some other explanation for this error and a possible solution?

        • 1. Re:
          vickyk

          Can you paste operateOnDB() code over here?

          Or do you have some other explanation for this error and a possible solution?

          Google search gives me these reasons
          http://www.jguru.com/forums/view.jsp?EID=1271414
          http://forums.oracle.com/forums/thread.jspa?threadID=488477


          • 2. Re:
            mazz

            I just recently hit this same exception myself. It only occurred once I switched to using XA datasources (same code did not show this error when using local, non-xa datasources).

            I think the cause of my problem might be different from yours, but I'll mention it anyway.

            My particular problem involved when I executed "truncate table" - and because its DDL, it auto-commits no matter what (your question shows that you are executing only DML, which is why I don't think its related). This caused problems because I was doing other SQL in the same transaction. The same thing would happen if you do things like e.g. CREATE or ALTER table.

            In my case, I was doing this "truncate table" in native SQL (was not using JPQL or hibernate to do it) and it was in an isolated method that was demarcated with REQUIRES_NEW. I switched to NOT_SUPPORTED which effectively suspended any currently active transaction before executing "truncate table". This fixed my problem.