Database connections stay open

ct1 — Thu, 17 May 2012 18:10:11 GMT

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,

Re: Database connections stay open

ronald_van_kuij — Fri, 18 May 2012 06:01:05 GMT

It would help a lot if you post you relevant configs

Re: Database connections stay open

ct1 — Fri, 18 May 2012 14:26:06 GMT

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.

topic Re: Database connections stay open in Alfresco Archive

Database connections stay open

Re: Database connections stay open

Re: Database connections stay open