工作流jbpm3.1.2导致数据库连接池满的问题

背景： 
    XX系统实施一段时间之后，出现数据库连接池满，第一次通过修改if(con!=null && con.isClosed()){con.close();}这样的逻辑错误解决部分问题。第二次通过彻底复查代码，修改了connection、session没有释放的问题，基本上保证我们自己写的代码没有数据库连接不释放的问题。但是临近近期还是出现连接池满的问题。。。

过程：
    从日志看，除了有大量工作流报错之外程序很少有异常，类似如下：

引用:

2009-06-12 15:44:34,187 [http-80-Processor44] [org.hibernate.event.def.AbstractFlushingEventListener] [ERROR] - Could not synchronize database state with session
org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [org.jbpm.graph.exe.Token#35000000000033432]
         ..............................................
        at org.jbpm.persistence.db.DbPersistenceService.close(DbPersistenceService.java:180)
         ..............................................
2009-06-12 15:44:34,187 [http-80-Processor44] [org.jbpm.svc.Services] [ERROR] - problem closing service 'persistence'
org.jbpm.persistence.JbpmPersistenceException: couldn't flush hibernate session
        at org.jbpm.persistence.db.DbPersistenceService.close(DbPersistenceService.java:182)

Caused by: org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [org.jbpm.graph.exe.Token#35000000000033432]
        at org.jbpm.persistence.db.DbPersistenceService.close(DbPersistenceService.java:180)
        ... 54 more

最开始基本确定了是工作流报错导致数据库连接池不释放，理由：
        a、上面的错和hibernate的session有关
        b、在sybase执行sp_who发现大量不释放连接所占用的库为DB_LC，而这个库就是工作流相关的库。
        c、从sybase的sysprocesses表查看，不释放连接是每天新增10-30不等，随机统计了日志某天的如前所述的异常为27个，而从数据库端统计该天新增连接也是27个。
     因为自己对工作流不熟悉，所以每次都是把情况反映给相关人员处理。前几天去客户现场正好抓取了一下不释放连接正在执行的sql，基本都是乱码，如下：

引用:

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
SQL Text: !
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
(1 row affected)
47
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
SQL Text: *
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
(1 row affected)
49
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
SQL Text:
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
(1 row affected)

这个结果用处不大，很好奇这个问题，所以找了一份工作流的源码，找到报错的类DbPersistenceService.close方法，如下：

复制内容到剪贴板

代码:

public void close() {
    if ( (session!=null)
         && (transaction==null)
         && (isRollbackOnly)
       ) {
      throw new JbpmException("setRollbackOnly was invoked while configuration specifies user managed transactions");
    }
    if (messagingSession!=null) {
      messagingSession.closeOpenIterators();
    }
    if (schedulerSession!=null) {
      schedulerSession.closeOpenIterators();
    }
    if ( (isTransactionEnabled)
         && (transaction!=null)
       ) {
      if (isRollbackOnly) {
        try {
          log.debug("rolling back hibernate transaction");
          mustSessionBeFlushed = false; // flushing updates that will be rolled back is not very clever :-)
          transaction.rollback();
        } catch (Exception e) {
          throw new JbpmPersistenceException("couldn't rollback hibernate session", e);
        }
      } else {
        try {
          log.debug("committing hibernate transaction");
          mustSessionBeFlushed = false; // commit does a flush anyway
          transaction.commit();
        } catch (Exception e) {
          try {
            // if the commit fails, we must do a rollback
            transaction.rollback();
          } catch (Exception e2) {
            // if the rollback fails, we did what we could and you're in
            // deep shit :-(
            log.error("problem rolling back after failed commit", e2);
          }
          throw new JbpmPersistenceException("couldn't commit hibernate session", e);
        }
      }
    }
   
    if (mustSessionBeFlushed) {
      try {
        log.debug("flushing hibernate session");
        session.flush();
      } catch (Exception e) {
        throw new JbpmPersistenceException("couldn't flush hibernate session", e);
      }
    }   
    if (mustSessionBeClosed) {
      try {
        log.debug("closing hibernate session");
        session.close();
      } catch (Exception e) {
        throw new JbpmPersistenceException("couldn't close hibernate session", e);
      }
    }

    if (mustConnectionBeClosed) {
      try {
        log.debug("closing jdbc connection");
        connection.close();
      } catch (Exception e) {
        throw new JbpmPersistenceException("couldn't close jdbc connection", e);
      }
    }
  }

一看真是吓一跳，程序在执行到session.flush();时候报错的话，如果mustSessionBeClosed为true根本不能执行到后面的session.close()，会导致数据库连接不释放的问题……基本确定问题所在了，就在本地试着复现一下问题（因为前面所描述的异常在开发环境无法复现，所以只能强制在flush后抛异常），果然不出意料。
     因为这个是jbpm3.1.2版本，觉得应该是jbpm的bug吧，就又下载了一份jbpm3.3.0GA源码，找到DbPersistenceService.close()方法：

复制内容到剪贴板

代码:

  public void close() {

    if ( (session!=null)
         && !isTransactionActive()
         && (isRollbackOnly())
       ) {
      throw new JbpmException("setRollbackOnly was invoked while configuration specifies user managed transactions");
    }
   
    if ( (isTransactionEnabled)
         && (transaction!=null)
       ) {

      if (! isRollbackOnly()) {
        Exception commitException = commit();
        if (commitException!=null) {
          rollback();
          closeSession();
          closeConnection();
          throw new JbpmPersistenceException("hibernate commit failed", commitException);
        }

      } else { // isRollbackOnly==true
        Exception rollbackException = rollback();
        if (rollbackException!=null) {
          closeSession();
          closeConnection();
          throw new JbpmPersistenceException("hibernate rollback failed", rollbackException);
        }
      }
    }
   
    Exception flushException = flushSession();
    if (flushException!=null) {
      // JBPM-1465 transaction has been either committed or rolled back at this point
      // on the other hand, it is possible that no transaction is underway
      // hence rolling back here is redundant and possibly dangerous
      closeSession();
      closeConnection();
      throw new JbpmPersistenceException("hibernate flush failed", flushException);
    }
    Exception closeSessionException = closeSession();
    if (closeSessionException!=null) {
      closeConnection();
      throw new JbpmPersistenceException("hibernate close session failed", closeSessionException);
    }
    Exception closeConnectionException = closeConnection();
    if (closeConnectionException!=null) {
      throw new JbpmPersistenceException("hibernate close connection failed", closeConnectionException);
    }
  }

果然在3.3.0版本中，当flush、close等操作出现异常时候，都会调用closeSession()和closeConnection()以保证连接正常释放。照猫画虎在该方法写了关闭session和connection的方法，准备月底发布新版本试试。

结论：
   XX系统工作流jbpm3.1.2存在连接不释放的bug，当然前提是程序执行数据库操作报错的情况下（如session.flush）。虽然解决了连接不释放的问题，但是这个关于这个报错的深层原因还没搞清楚。另外和相关人员确认，工作流的这些异常可以cacth掉，到目前为止除了引起连接不释放之外，没有发现其他问题。