Transaction/DB problems in asynchronous methods with Seam

Question

I have a strange behaviour with Seam 2.2.2, JBoss 5.1, MySQL 5.1.5.1 and MySQL JDBC connector 5.1.12 and asynchronous methods (using quartz) and pojos.

I've got a pool of 10 async threads for various tasks. They normally work fine, performing db queries and updates etc. If there is an exception in one async method it doesn't impact the next async method called from the same thread.

But I now have one case where I get this exception in one particular thread:

    012-10-02 05:45:26,743 WARN  [][JDBCExceptionReporter] (er-4) SQL Error: 0, SQLState: null
2012-10-02 05:45:26,743 ERROR [][JDBCExceptionReporter] (er-4) Transaction is not active: tx=TransactionImple < ac, BasicAction: 7f000001:b824:5069f752:15d status: ActionStatus.ABORT_ONLY >; - nested throwable: (javax.resource.ResourceException: Transaction is not active: tx=TransactionImple < ac, BasicAction: 7f000001:b824:5069f752:15d status: ActionStatus.ABORT_ONLY >)
2012-10-02 05:45:26,743 INFO  [][DefaultLoadEventListener] (er-4) Error performing load command
org.hibernate.exception.GenericJDBCException: Cannot open connection
        at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)
        at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449)
        at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
        at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161)
        at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1573)

After this every time this thread (er-4) is used and a query is executed I get the same exception. How can I recover from this and why is this? It doesn't matter what method is called by this thread (they are annotated with @Transactional) they all fail because of the GenericJDBCException: Cannot open connection. The problem suddenly appeared out of the blue.

I am using db connection pooling and validate the connection before each use (from the deployment descriptor):

  <!--pooling parameters-->
       <min-pool-size>5</min-pool-size>
       <max-pool-size>100</max-pool-size>
       <blocking-timeout-millis>5000</blocking-timeout-millis>
       <idle-timeout-minutes>15</idle-timeout-minutes>
       <prepared-statement-cache-size>50</prepared-statement-cache-size>
       <check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>

Is this a problem with my code? with Seam? JBoss? MySQL? JDBC MySQL driver?

Has anyone else experienced similar problems.

Answer 1

The problem was caused by a method which did batch processing. It handled the UserTransaction directly but didn't handle exceptions.

e.g.

UserTransaction userTx = null;
    try{
        log.info("Updating List from #0 on", startAt);

    userTx = (UserTransaction) org.jboss.seam.Component.getInstance("org.jboss.seam.transaction.transaction");
      userTx.setTransactionTimeout(10 * 60);  //set timeout to 60 * 60 = 3600 secs = 1 h
      userTx.begin();

      /*If entity manager is created before the transaction is started (ie. via Injection) then it must join the transaction */
      entityManager.joinTransaction();
    //DO STUFF
    userTx.commit();
    }catch(Exception e){
        e.printStackTrace();
        return false;
    }

The catch statement should have contained a userTx.rollback() otherwise the transaction for that thread will stay in an unusable state.