Transaction management on multiple DB connections using hibernate

Hi,

If you want to manage transactions on multiple databases connections, can you do that with Hibernate?

For instance, I want to update data on both databases, but the first transaction on a DB has to be rollback after second transaction on the other DB fails. How can you handle this kind scenario?

Thanks in advance.
Brad

It can be configured as this topic multiple DB connections using hibernate

But it doesn't include how to manage transactions.
[ January 16, 2008: Message edited by: Brad Ko ]

This is an example of a distributed transaction; these are managed by a transaction manager (a software component); an application interacts with a transaction manager using JTA (an API). A Java EE application server should have all this included/supported; otherwise, you can consider using a separate transaction manager.

Hi Edvins

Thanks for your quick answer and valuable information. Someone suggested me using distributed transaction manager with the XA version of JDBC driver and I tried to implement it, however I got a error message
�org.hibernate.TransactionException: could not register synchronization�.
Would you correct me what I am wrong from the following?

Thanks again,
Brad

===Oracle(9i) JDBC Driver (XA) on Websphere(v6.1) datasource===
oracle.jdbc.xa.client.OracleXADataSource

===hibernate(v3.2.5.ga) configuration===
<hibernate-configuration>
<session-factory>
<property name="hibernate.dialect">org.hibernate.dialect.Oracle9iDialect</property>
<property name="hibernate.connection.datasource">jdbc/dsXA1</property>

<property name="hibernate.transaction.factory_class">
org.hibernate.transaction.CMTTransactionFactory
</property>
<property name="hibernate.transaction.manager_lookup_class">
org.hibernate.transaction.WebSphereExtendedJTATransactionLookup
</property>

<property name="show_sql">true</property>
<property name="current_session_context_class">thread</property>
<property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
</session-factory>
</hibernate-configuration>

[Java(v1.4) dao code]
for (int idx=0; idx<InitSessionFactory.NUMBER_OF_INSTATNCE; idx++)
{
try
{
Session session = InitSessionFactory.getCurrentSession(idx);
session.saveOrUpdate(obj);
}
catch ( Exception e )
{
e.printStackTrace();
log.error(": Exception on <updateTestOR>: " + e.getMessage());
}
}

If I use �JTATransactionFactory� in hibernate configuration, this error message is shown.
�org.hibernate.TransactionException: could not register synchronization with JTA TransactionManager�

<property name="hibernate.transaction.factory_class">
org.hibernate.transaction.JTATransactionFactory
</property>
<property name="hibernate.transaction.manager_lookup_class">
org.hibernate.transaction.WebSphereExtendedJTATransactionLookup
</property>

Brad,

can you post the entire exception stack trace?

Here you are.

0000002e SystemErr R org.hibernate.TransactionException: Could not register synchronization
at org.hibernate.transaction.CMTTransaction.registerSynchronization(CMTTransaction.java:159)
at org.hibernate.context.ThreadLocalSessionContext.currentSession(ThreadLocalSessionContext.java:78)
at org.hibernate.impl.SessionFactoryImpl.getCurrentSession(SessionFactoryImpl.java:544)
at com.ibm.msp.webtim.hibernate.InitSessionFactory.getCurrentSession(InitSessionFactory.java:87)
at com.ibm.msp.webtim.dao.TestORDao.updateTestOR(TestORDao.java:59)
at webtest.actions.FirstAction.execute(FirstAction.java:37)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1212)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:629)
at com.ibm.ws.webcontainer.webapp.WebApp.handleRequest(WebApp.java:2837)
at com.ibm.ws.webcontainer.webapp.WebGroup.handleRequest(WebGroup.java:220)
at com.ibm.ws.webcontainer.VirtualHost.handleRequest(VirtualHost.java:204)
at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:1681)
at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:77)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:421)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:367)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.ready(HttpInboundLink.java:276)
at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.sendToDiscriminaters(NewConnectionInitialReadCallback.java:201)
at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.complete(NewConnectionInitialReadCallback.java:103)
at com.ibm.ws.tcp.channel.impl.WorkQueueManager.requestComplete(WorkQueueManager.java:548)
at com.ibm.ws.tcp.channel.impl.WorkQueueManager.attemptIO(WorkQueueManager.java:601)
at com.ibm.ws.tcp.channel.impl.WorkQueueManager.workerRun(WorkQueueManager.java:934)
at com.ibm.ws.tcp.channel.impl.WorkQueueManager$Worker.run(WorkQueueManager.java:1021)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java(Compiled Code))
Caused by: org.hibernate.HibernateException: java.lang.reflect.InvocationTargetException
at org.hibernate.transaction.WebSphereExtendedJTATransactionLookup$TransactionManagerAdapter$TransactionAdapter.registerSynchronization(WebSphereExtendedJTATransactionLookup.java:165)
at org.hibernate.transaction.CMTTransaction.registerSynchronization(CMTTransaction.java:156)
... 28 more
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java(Compiled Code))
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java(Compiled Code))
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java(Compiled Code))
at java.lang.reflect.Method.invoke(Method.java(Compiled Code))
at org.hibernate.transaction.WebSphereExtendedJTATransactionLookup$TransactionManagerAdapter$TransactionAdapter.registerSynchronization(WebSphereExtendedJTATransactionLookup.java:159)
... 29 more
Caused by: com.ibm.websphere.jtaextensions.NotSupportedException
at com.ibm.ws.jtaextensions.ExtendedJTATransactionImpl.registerSynchronizationCallbackForCurrentTran(ExtendedJTATransactionImpl.java:227)
... 35 more

Thanks Edvins

The issue has been solved with your idea.
UserTransaction is taken from Session bean (BMP) and it sets transaction scope in session bean method which calls Hibernate DAO class. The method of the DAO class updates data into multiple data source. It works fine (even I don�t do performance testing yet).

Here is hibernate configuration and EJB method for another people who try to find out a example code of distributed transaction management.

==EJB method (BMP)===