ORA-02051: another session or branch in same transaction failed or finalized

Question

I have a problem with my XA Transaction.

enter image description here

If you look at the picture, the XA Transaction flow order is numbered. The call number 3 is the half-workaround I have found so far to half-solve the problem. I mean, if I don't make the SELECT 1 query to Database 2 before accesing the ejb 2, the Transaction fails.

I say half-solves the problem, because when I put the SELECT 1 query, the Servlet doesn't crash and I can complete the Transaction. But this workaround is not applicable in other scenarios where for example an external B2B client don't have and shouldn't have access to our internal Database 2, so they can't make the SELECT 1 query to fix it.

The concrete exception point is in the Hibernate beforeComplete Synchronization. It crashes when Hibernate is flushing cache to Database 2 but Oracle throws this error:

ORA-02051: another session or branch in same transaction failed or finalized

After spending days making proves and reading the OMG "Transaction Service Specification" and chapter 10 of the book "Mastering EJB" i cannot understand why my XA Transaction fails ;)

I Would be very grateful if someone can help me in either way.

The Full stack trace is this:

> 2014-11-11 18:27:27,365 DEBUG [ORB.thread.pool : 0] [LogicalConnectionImpl] [f59d55f3-8361-4c80-975c-8df36f89b7c3] Obtained JDBC connection
2014-11-11 18:27:27,428 DEBUG [ORB.thread.pool : 0] [SqlExceptionHelper] [f59d55f3-8361-4c80-975c-8df36f89b7c3] could not execute statement [n/a]
java.sql.SQLSyntaxErrorException: ORA-02051: otra sesi?? bifurcaci??ha fallado o terminado en la misma transacci??
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1044)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3665)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1352)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteUpdate(WSJdbcPreparedStatement.java:1185)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeUpdate(WSJdbcPreparedStatement.java:802)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:186)
    at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:58)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3079)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3521)
    at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:88)
    at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:393)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:385)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:301)
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:349)
    at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:56)
    at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1159)
    at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:404)
    at org.hibernate.engine.transaction.synchronization.internal.SynchronizationCallbackCoordinatorNonTrackingImpl.beforeCompletion(SynchronizationCallbackCoordinatorNonTrackingImpl.java:106)
    at org.hibernate.engine.transaction.synchronization.internal.RegisteredSynchronization.beforeCompletion(RegisteredSynchronization.java:53)
    at com.ibm.ws.uow.ComponentContextSynchronizationWrapper.beforeCompletion(ComponentContextSynchronizationWrapper.java:65)
    at com.ibm.tx.jta.impl.RegisteredSyncs.coreDistributeBefore(RegisteredSyncs.java:291)
    at com.ibm.ws.tx.jta.RegisteredSyncs.distributeBefore(RegisteredSyncs.java:153)
    at com.ibm.ws.tx.jta.TransactionImpl.prePrepare(TransactionImpl.java:2339)
    at com.ibm.tx.jta.impl.TransactionImpl.internalPrepare(TransactionImpl.java:1422)
    at com.ibm.ws.Transaction.JTS.TransactionWrapper.prepare(TransactionWrapper.java:204)
    at com.ibm.ws.Transaction.JTS.WSCoordinatorImpl.prepare(WSCoordinatorImpl.java:144)
    at com.ibm.ws.Transaction.JTS._WSCoordinatorImplBase._invoke(_WSCoordinatorImplBase.java:50)
    at com.ibm.CORBA.iiop.ServerDelegate.dispatchInvokeHandler(ServerDelegate.java:669)
    at com.ibm.CORBA.iiop.ServerDelegate.dispatch(ServerDelegate.java:523)
    at com.ibm.rmi.iiop.ORB.process(ORB.java:523)
    at com.ibm.CORBA.iiop.ORB.process(ORB.java:1575)
    at com.ibm.rmi.iiop.Connection.doRequestWork(Connection.java:3039)
    at com.ibm.rmi.iiop.Connection.doWork(Connection.java:2922)
    at com.ibm.rmi.iiop.WorkUnitImpl.doWork(WorkUnitImpl.java:64)
    at com.ibm.ejs.oa.pool.PooledThread.run(ThreadPool.java:118)
    at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1815)

UPDATE:

Same use case WORKS FINE on Wildfly attacking either an Oracle Database Server, Oracle XE or Postgresql. So it seams it's a WAS problem. What do you think?

I pray for your help please, if you can enlight me in any way. I will give you a +100 bounty bonus. Thank you — Andrés Oviedo, Nov 12 '14 at 18:13
the error says sql syntax exception.. [ORB.thread.pool : 0] [SqlExceptionHelper] [f59d55f3-8361-4c80-975c-8df36f89b7c3] could not execute statement [n/a] java.sql.SQLSyntaxErrorException: ORA-02051: otra sesi?? bifurcaci??ha fallado o terminado en la misma transacci?? what is the sql you're trying to execute — Shankar, Dec 02 '14 at 19:54
@Shankar It's actually an insert made by a BPM product software (not ours). But the sql executed has no matter in this. The use case works fine making the select 1 or in JBoss. — Andrés Oviedo, Dec 02 '14 at 20:25
@andresoviedo What is the version of Oracle that you are using on DB1 and DB2? Do any other error messages get logged on either WebSphere? How about the Alert.log from the databases, any other error messages logged? — Gui, Dec 03 '14 at 00:30
You need to add your Hibernate transaction management settings and describe the data sources in use. — Vlad Mihalcea, Dec 03 '14 at 06:36
@Gui We are using the latest versions: WAS (8.5.5), Oracle (11g), Driver oracle (11.2.0.4), Hibernate (4.3.6) for both servers. We haven't found any other error neither in WAS nor Oracle. — Andrés Oviedo, Dec 03 '14 at 08:35
@VladMihalcea We have already configure the spring-orm 4 LocalContainerEntityManagerFactoryBean to work with Hibernate and the JTA datasource. — Andrés Oviedo, Dec 03 '14 at 08:50
You also need to use the JTATransactionManager and make sure Hibernate is set to JTA. Without your configs I can't say where the problem is. — Vlad Mihalcea, Dec 03 '14 at 09:16
@VladMihalcea We are using the spring WebsphereUOWTransactionManager wrapper for both tiers. I really don't think the problem is the configuration, because with JBoss is working fine. Also the select 1 fixes the problem, so I think context is configured OK. — Andrés Oviedo, Dec 03 '14 at 09:35
for curiosity, transactions from EJB1 and EJB2 are annoted with REQUIRED? Who inject the EJB's? (Spring, WebSphere Container or are retrivied by initialContext.lookup?). Edit your question, and please, put copy of your persistence.xml — wryel, Dec 09 '14 at 03:42
@andresoviedo kindly share your JBOSS and WAS connection pool settings — psaraj12, Dec 09 '14 at 08:09
@wryel Both EJBs are default CMT REQUIRED. The EJBs are looked-up by a Spring proxy. There is no persistence.xml. All metadata is annotated in the third party BPM libraries. — Andrés Oviedo, Dec 09 '14 at 08:24
@psaraj12 All connection pool settings have it's setting by default: timeout 180s, maxConn=10, ... JBoss also: jta=true, ccm=true, ... — Andrés Oviedo, Dec 09 '14 at 08:37
@andresoviedo what idea made you use the SELECT 1 from ejb 1 to Database 2 ??? — psaraj12, Dec 09 '14 at 08:52
@psaraj12 Good question! I started to isolate the use case. At the beginning I had a "select * from mytable" after the EJB invocation. I took it off and it worked! I realized that making any select after calling the EJB was making the transaction to rollback. Next I tried to move the query before calling the EJB, and guess what! It also worked! Finally, to isolate completely the use case I changed my table query for the "select 1" to discard a business problem, and guess what! It also worked! That's how I arrived here! — Andrés Oviedo, Dec 09 '14 at 11:45
@andresoviedo how long does it take to run this query select 1 in database — psaraj12, Dec 09 '14 at 13:28
@andresoviedo is the configuration of App server 1 and Appserver 2 same since ejb 1 is able to do the select but not ejb 2 ??? — psaraj12, Dec 09 '14 at 13:55
@psaraj12 The 'select 1' takes a fraction of second. Both App Servers have same configuration. The App Server2 are doing other things (not the select 1). — Andrés Oviedo, Dec 10 '14 at 08:59
@AndrésOviedo, have you applied the workaround explained here --> ORA-02051 Another Session Or Branch In Same Transaction Failed (Doc ID 2253226.1) — Roberto Hernandez, Sep 01 '21 at 13:59

ORA-02051: another session or branch in same transaction failed or finalized

0 Answers0