2 Replies Latest reply on Dec 3, 2008 3:06 PM by Guillaume Jeudy

    Multiple databases possible without distributed transactions?

    Rob Hills Newbie

      We have an application that accesses two databases (one Oracle, on SQLServer).  We always read from one and write to the other and hence have no need for two-phase commits so we are using local-tx datasources.  We'd prefer to avoid the complexity of using XA datasources. 


      As we have com.arjuna.ats.jta.allowMultipleLastResources set to true in our jbossjta-properties.xml, our current configuration works without error, but we keep getting the multiple last resources warning in our logs.  I've also read the warnings about this configuration (eg in http://www.jboss.org/community/docs/DOC-11443) and would prefer to have one that doesn't come with these potential issues.


      So, is it possible to configure a JBoss/Seam application to work with two Databases and keep each datasource's transactions completely independent of one another?  I've googled heaps on this, but all the documentation I've found assumes that you want to have distributed transactions!


      Cheers,
      Rob Hills
      Waikiki, Western Australia

        • 1. Re: Multiple databases possible without distributed transactions?
          Rob Hills Newbie

          I forgot to mention, we're using POJOs, not EJBs so annotating methods with @TransactionAttribute(TransactionAttributeType.REQUIRES_NEW) isn't an option for us.


          Also, as we always want each DB to have independent transactions, we'd prefer to find a global configuration option if possible rather than having to modify our code.

          • 2. Re: Multiple databases possible without distributed transactions?
            Guillaume Jeudy Master

            Hi Rob, I have a similar setup as you, read from one DB, write to another.


            I don't see any potential issues in our scenarios. I think the JBoss folks decided to play it safe and always log the warning just in case someone is using this setting with multiple database being written to.  In this case, there is a slim chance that some dbs are committed while others aren't. Keep in mind that the likelihood of an outage occurring during the commit sequence is very small.


            In your current setup the dbs are still running with independent transactions but make it appear to you that they run within the same transaction due to JBoss JTA transaction manager. In other words when JBoss TX Mgr commits each transactional resource it cannot be rolled back anymore which is exactly the behavior of an independent (local) transaction.


            I agree that

            @TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)

            option is cleaner to isolate reads in their own JTA transaction but I don't think it is required in our case either.


            Are their other concerns that you may have?