5 Replies Latest reply on Feb 21, 2013 4:11 AM by tomjenkinson

    TransactionalDriver and Hibernate (on Tomcat) - Reusing connections and connection pooling

    talawahdotnet

      I am using Narayana 4.17 in a standalone configuration running on Tomcat on Amazon EC2/ElasticBeanstalk.  The only reason I am using Narayana at this point is to allow me to use Infinispan as the 2nd level/query cache for Hibernate. After struggling with this configuration using a custom XADataSource wrapper and corresponding JNDI ObjectFactory that wraps a MysqlXADataSource and pulls TransactionalDriver into the mix, I finally have it mostly working, however TransactionalDriver's behavior seems to differ from the documentation's with respect to reusing connections and connection pooling.

       

      After getting things up and running I noticed that a new connection to my MySQL database was being opened every 10 seconds when my job ran and it was not being closed or reused.  I tried in vain to make use of the reuseconnection=true option referred to by the documentation but from my tests and from looking at the code it doesn't seem to have any effect.  I then came across JBTM-789 which indicated that recovery connections would not be properly closed unless I registered a modifier for my DBMS.  So I did that, but that just made things worse, I started getting an exception on commit(), indicating that no operations are allowed because the connection is already closed.  My code looked roughly like this:

       

       

           tx = (UserTransaction) new InitialContext().lookup("java:comp/UserTransaction");
           tx.begin();
           Session session = sessionFactory.getCurrentSession();
           List<Event> result = session.createQuery( "from Event", Event.class ).getResultList();
           tx.commit();
      

       

      So I stepped through the code to try and get a better understanding of what was going on and observed the following:

       

      With no modifier the following code is executed:

       

      if (_theModifier == null)
       {
           jdbcLogger.i18NLogger.info_closingconnectionnull(_theConnection.toString());
            // no indication about connections, so assume close immediately
      
       if (_theConnection != null && !_theConnection.isClosed())
       _theConnection.close();
      
       _theConnection = null;
      
       return;
       }
      

       

      The connection wrapper gets closed but the underlying physical connection (_recoveryConnection) stays open. Unfortunately that physical connection never seems to get reused and every time my job execute a new connection is opened.

       

       

      When I created a modifier that returns false for.supportsMultipleConnections() the following code was executed:

       

      if (!delayClose)  // close now
       {
       _recoveryConnection.closeCloseCurrentConnection();
       if (_theConnection != null && !_theConnection.isClosed())
       _theConnection.close();
      
       _theConnection = null;
       }
      
      

       

      Both the wrapper and the underlying physical connection get closed, but this happens before commit gets called, and I get an exception. So I did some more debugging/reading and figured out that by design hibernate attempts to aggressively release connections. As a matter of fact when using <property name="hibernate.current_session_context_class">jta</property> and sessionFactory.getCurrentSession(); hibernate defaults to hibernate.connection.release_mode = AFTER_STATEMENT. So I Added hibernate.connection.release_mode = ON_CLOSE to my hibernate.cfg.xml and refactored my code like this:

       

           tx = (UserTransaction) new InitialContext().lookup("java:comp/UserTransaction");
           Session session = sessionFactory.openSession();
           tx.begin();
           List<Event> result = session.createQuery( "from Event", Event.class ).getResultList();
           tx.commit();
           session.close();
      

       

      This way, I ensure that the commit happens before the connection is released and since no transaction exist when the connection is release, the physical connection is also closed (without the need for the modifier). So the exception went away, as did the zombie connections but the obvious downside is that a new connection is still being opened for every single transaction.  Again, according to the documentation the transactional driver supports some basic connection pooling for the same user/pass/db combination, but from my reading the code, it seems at this only applies to queries executed as a part of the same transaction

       

      if ((tx1 != null && tx1.equals(tx2))
           && connControl.url().equals(dbUrl)
           && connControl.user().equals(user)
           && connControl.password().equals(passwd)
           && connControl.dynamicClass().equals(dynamic))
      
      

       

      So my question at this point is, is there something else that I missed that will allow to get connection pooling/reuse across different transactions?  I have stayed away from the usual connection pools like c3p0 and dbcp because I got the impression that non XA aware pools should not be mixed with XA connections.  Does that warning apply to my use case (single XA resource (MySQL) plus Infinispan via transaction synchronization)? What about XAPool?  I saw some warning against it in this document, but that warning may be outdated and my not apply to my scenario, then again XAPool itself looks pretty outdated and I can't seem to find much info on people using it with JBossTM.  I am scared to even ask about what it might take to integrate IronJacamar myself...don't think I have the time to go down another experimental rabbit hole.  What kind of timeline are you guys looking at for doing that integration yourselves?

       

      P.S. Word warning to anybody who may come across this post. If is not already abundantly clear this configuration is probably not worth the hassle it takes to set it up. You would probably be better off deploying on JBoss AS (if you can) or using ehcache which allows non-transaction usage. Perhaps things will get easier if and when JBossTS replaces TransactionalDriver with IronJacamar.