Database connections stay open

[ct1]

Sirs:
I have been running some tests intended to simulate multiple user starting processes almost simultaneously.  To simulate the multiple users, I have a program that, for each BPMN process I want launched, starts a new thread which it instantiates a Process Engine, sets the authenticated user id, starts the process instance by id, and destroys its instance of the Process Engine.

The BPMN 2.0 processes get launched (after dealing with the aggravating ActivitiOptimisticLockingException by waiting 5 seconds and trying again, which happend 20% of the time) and run to completion.  However, the database connections remain open.  When I ran 1000 simulated users, over 2000 database connections remained open.  I did not detect a large number of database connections remaining open when I tested with Activiti Explorer's launching processes, only when I used the Java API to the Process Engine.

It seems that applications that open database connections are responsible for closing them. 

Does the code explicitly close the connections? 
If not, is there a configuration parameter on the Engine that controls the closing of database connections? 
Or, is there something in the API that would let me request the connections be closed?

Thank you.

[ronald_van_kuij]

It would help a lot if you post you relevant configs

[ct1]

Thank you for your reply.

I did not show the configuration in the first post because I did not think the answers to my questions were configuration specific.  I apologize for that assumption.

This is the activiti.cfg.xml file I used.  The commented out parts are used for testing with databases on different servers.  Only one is uncommented for a test.  As you can see, I ran with the database on LUSR1506 for the last test. 

I am using the SQL Server 2008 Express database.  One database is on the same machine on which I ran the Process Engine, the others are on the local area network and on the wide area network.  The database connections remaining open occurred no matter which database server I used.

I run the test as a Java Application within Eclipse Indigo Service Release 2 on LUSR1315, under Window 7 Professional OS.

activiti.cfg.xml:


<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans  
       http://www.springframework.org/schema/beans/spring-beans.xsd">

  <bean id="processEngineConfiguration" class="org.activiti.engine.impl.cfg.StandaloneProcessEngineConfiguration">
    <property name="databaseSchemaUpdate" value="false"/>  
         
    <!– Use these values for LUSR0989 –>
<!– <property name="jdbcUrl" value="jdbc:jtds:sqlserver://LUSR0989:1433/Workflow_DB1" />
    <property name="jdbcDriver" value="net.sourceforge.jtds.jdbc.Driver"/>
    <property name="jdbcUsername" value="yyyyyyyyyyyy" />
    <property name="jdbcPassword" value="zzzzzzzzzzzz" />
–>

    <!– Use these values for LUSR1506 –>
<property name="jdbcUrl" value="jdbc:jtds:sqlserver://LUSR1506:1433/Workflow_DB1" />
    <property name="jdbcDriver" value="net.sourceforge.jtds.jdbc.Driver"/>
    <property name="jdbcUsername" value="yyyyyyyyyyyy" />
    <property name="jdbcPassword" value="zzzzzzzzzzzz" />

   
    <!– Use these values for LUSR1315 –>
<!– <property name="jdbcUrl" value="jdbc:jtds:sqlserver://LUSR1315:1433/Activiti_DB" />
    <property name="jdbcDriver" value="net.sourceforge.jtds.jdbc.Driver"/>
    <property name="jdbcUsername" value="jjjjjjjjjjjj" />
    <property name="jdbcPassword" value="kkkkkkkkkkkk" />
–>
    <property name="jobExecutorActivate" value="true" />
  </bean>
 
</beans>


The db.properties files are as follows:


#    db=h2
#    jdbc.driver=org.h2.Driver
#    jdbc.url=jdbc:h2:tcp://localhost/activiti
#    jdbc.username=sa
#    jdbc.password=
db=mssql
jdbc.driver=net.sourceforge.jtds.jdbc.Driver
jdbc.url=jdbc:jtds:sqlserver://LUSR1506:1433/Workflow-DB1
jdbc.username=yyyyyyyyyyyy
jdbc.password=zzzzzzzzzzzz


I have a single class that interfaces with the Process Engine.  All classes needing a Process Engine call this one.  Here are relevant parts of that class, ProcEngController.java.



package com.argo.wfProj1;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Hashtable;
import java.util.List;
import java.util.ListIterator;

import org.activiti.engine.ActivitiException;
import org.activiti.engine.ProcessEngine;
import org.activiti.engine.ProcessEngineConfiguration;
import org.activiti.engine.ProcessEngines;
import org.activiti.engine.history.HistoricActivityInstance;
import org.activiti.engine.history.HistoricProcessInstance;
import org.activiti.engine.identity.Group;
import org.activiti.engine.identity.User;
import org.activiti.engine.repository.ProcessDefinition;
import org.activiti.engine.runtime.ProcessInstance;

import org.apache.log4j.Logger;

public class ProcEngController
{
private Logger logger = Logger.getLogger(ProcEngController.class);

private ProcessEngine myProcessEngine;

public ProcEngController() throws Throwable
{
  logger.info("ProcEngController.constructor() was called.");
 
  initTheEngine();
 
}

private void initTheEngine() throws Throwable
{
  logger.info("ProcEngController.initTheEngine() was called.");
 
  try
  {
   myProcessEngine =
     ProcessEngineConfiguration
     .createProcessEngineConfigurationFromResource("activiti.cfg.xml")
     .buildProcessEngine();
  
   logger.info("In ProcEngController.initTheEngine() " + "built Process Engine.");
   
  }
  catch(ActivitiException ae)
  {
   logger.fatal("In initTheEngine() caught ActivitiException trying "
      + "to buid the process engine.  Exception was "
      , ae);
  
   ae.printStackTrace();
  
   throw new Exception("Caught AcvtivitiException in "         + "ProcEngController.initTheEngine()."         +"  Could not buildProcessEngine().");  
  }
  catch(Throwable th)
  {
   logger.fatal("In ProcEngController.initTheEngine() caught "
     + "Trowable trying to "
     + "buid the process engine.  Exception was "
     , th);
  
   StackTraceElement[] ste = th.getStackTrace();
  
   for(int i = 0; i < ste.length; i++)
   {
    String oneSTElement = new String();
    oneSTElement = (String) ste[i].toString();
   
    logger.fatal(oneSTElement);
   
    if(i == 10)
    {
     break;
    }
   }
  
   th.printStackTrace();
  
   throw new Exception
     ("Caught Throwable in ProcEngController.initTheEngine() "
     + "Could not buildProcessEngine().");  
  }
}

public void destroy()
{
  logger.info("In ProcEngController.destroy() was called.");
 
  ProcessEngines.destroy();
 
  logger.info("In ProcEngController.destroy() called "
     + "ProcessEngines.destroy().");
}

…

protected boolean verifyIdIsOkay(String theRequestedUserId)
{
  boolean idIsOkay = false;
 
  if(    theRequestedUserId != null
   && theRequestedUserId.length() > 0)
  {
 
   String theFoundUserId = new String();
  
   try
   {
    theFoundUserId = myProcessEngine
      .getIdentityService()
      .createUserQuery()
      .userId(theRequestedUserId)
      .singleResult()
      .getId();
     
   }
   catch(Exception e2)
   {
    theFoundUserId = "";
   
    logger.info("In ProcEngController.verifyIdIsOkay() caught "
       + "Exception trying to find theRequestedUserId \""
       + theRequestedUserId
       + ".  Exception was "
       , e2);
   
    // The Process Engine throws an exception if the userId
    // cannot be found; it probably should handle the condition
    // more cleanly than that.  We do not need to trace the
    // situation here.
//    e2.printStackTrace();
     
   }
  
   if(theFoundUserId != null && theFoundUserId.length() > 0)
   {
    idIsOkay = true;
   }
   else
   {
    idIsOkay = false;
   }

  }
  else
  {
   idIsOkay = false;
  }
 
  logger.info("In ProcEngController.varifyIdIsOkay() looking for "
     + "theRequestedUserId = \""
     + theRequestedUserId
     + "\", and found idIsOkay = "
     + idIsOkay);
 
  return idIsOkay;
}

protected Hashtable<Integer, ProcDefData> findAllProcesses()
{ 
  Hashtable<Integer, ProcDefData> allProcDefsInfo = new Hashtable<Integer, ProcDefData>();
 
  List<ProcessDefinition> procDefsList = myProcessEngine
     .getRepositoryService()
     .createProcessDefinitionQuery()
     .orderByProcessDefinitionName()
     .asc()
     .orderByProcessDefinitionVersion()
     .asc()
     .list();
 
  for(int i = 0; i < procDefsList.size(); i++)
  {
   ProcessDefinition aProcDef = procDefsList.get(i);
  
   String deployId = new String();
   String procDefId = new String();
   String procDefKey = new String();
   String procDefName = new String();
   Integer procDefVersion = new Integer("-1");
   String procDefCategory = new String();
   String procDefDiagram = new String();
  
   deployId = aProcDef.getDeploymentId();
   procDefId = aProcDef.getId();
   procDefKey = aProcDef.getKey();
   procDefName = aProcDef.getName();
   procDefVersion = new Integer(aProcDef.getVersion());
   procDefCategory = aProcDef.getCategory();
   procDefDiagram = aProcDef.getDiagramResourceName();
  
   ProcDefData procDefData = new ProcDefData();
   procDefData.setDeployId(deployId);
   procDefData.setProcDefCategory(procDefCategory);
   procDefData.setProcDefId(procDefId);
   procDefData.setProcDefKey(procDefKey);
   procDefData.setProcDefName(procDefName);
   procDefData.setProcDefVersion(procDefVersion);
   procDefData.setProcDefDiagramName(procDefDiagram);
  
   // Find the deployment name
  
   String deploymentName = new String();
  
   deploymentName = findDeploymentName(deployId);
  
   procDefData.setDeploymentName(deploymentName);
  
   String deploymentDate = new String();
  
   deploymentDate = findDeploymentDate(deployId);
  
   procDefData.setDeploymentDate(deploymentDate);
  
   Integer seqNbr = new Integer(i);
  
   allProcDefsInfo.put(seqNbr, procDefData);
  }
 
  return allProcDefsInfo;
 
}

…
protected String findProcDefName(String procDefId)
{
  String procDefName = new String();
 
  procDefName = myProcessEngine.getRepositoryService()
    .createProcessDefinitionQuery()
    .processDefinitionId(procDefId)
    .singleResult()
    .getName();
     
  return procDefName;
}

…
protected ProcessInstance startAProcess(String processIdToStart,int cycle, int countInCycle)
{
  logger.info("ProcEngController.startAProcess was called to "
     + "start processId = " + processIdToStart + ".  Cycle = "
     + cycle + " and count in cycle = " + countInCycle);
 
  ProcessInstance theProcInstance = null;
 
  int startAProcessRetryLimit = 6;
 
  int startsTried = 0;
 
  // Put starting a process in a loop so we can easily retry it if it
  // fails the first time.
  for(startsTried = 0; startsTried < startAProcessRetryLimit; startsTried++)
  {
   try
   {
                    theProcInstance =
     myProcessEngine.getRuntimeService()
     .startProcessInstanceById(processIdToStart);
   
    // If starting process does not catch an exception, it worked;
    // so get out of the retry loop.
   
    logger.info("ProcEngController.startAProcess() started process "
       + processIdToStart
       + " when startsTried = " + startsTried
       + " in thread "
       + Thread.currentThread().getName());
   
    startsTried = startAProcessRetryLimit;  // Get out of loop
   }
   catch(Throwable th)
   {
    logger.error("ProcEngController.startAProcess() startsTried = "
       + startsTried
       + ", caught throwable "
       + "trying to start " + processIdToStart
       + "in thread "
       + Thread.currentThread().getName(), th);
   
    // If trying to start the process catches a throwable, log it "
    // and retry starting the process after waiting a few seconds.
    try
    {
     Thread.sleep(5000);
    }
    catch(Exception e)
    {
     logger.error("ProcEngController.startAProcess() caught "
       + "exception trying to pause before "
       + "attempting again to start "
       + processIdToStart
       + ".  startsTried = "
       + startsTried, e);
    
     // Make sure the conditional part of the for() statement
     // prevents an infinite loop of start-exceptions here.
     // The automatic incrementing of startsTried should
     // prevent the infinite loop.
    }
   }
  
  }
 
  return theProcInstance;
}

…

protected void setAuthenticatedUserId(String theUserId)
{
  myProcessEngine.getIdentityService().setAuthenticatedUserId(theUserId);
}

}


Please let me know if you need anything else to answer my questions.

Thank you in advance for your continued help.