"ORA-02089: COMMIT is not allowed in a subordinate session"
kurtu2 Nov 4, 2008 4:04 PMHi, 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?