Unable to borrow new connection from DB connection pool

[galen...@gmail.com]

Hi,

Sometimes when I run a lot of processes (i.e. thousands) at the same time, I get errors back from the database connection pool saying "unable to get new connection from the pool after timeout". (mybatis trying to get a new connection from the pool is mentioned in the stacktrace, and the timeout is set to sixty seconds).
In other words it's suggesting that something is probably having long-lived open connections, and/or not closing connections. The process I'm running takes a little bit over a second to complete, and there are several asynchronous continuations in it.

So I have some general questions about connection management in Camunda:
1) What is the lifetime/scope of an open session with respect to a process?
2) If a process takes longer to complete, does it impact how long connections remain open (and therefore not returned to the pool)?

If I have time, I will try to create a test case that reproduces what I see, but I'm still trying to simplify the case, and try to track down how to exactly reproduce it, as it doesn't always seem to happen.

In the meantime, perhaps getting some general information about Camunda's manament of connections may help.

There are a few places in my code where I use Spring JDBC to insert update info in my own tables, but they are very fast, singleton transactions, and I don't manage the interaction with the pool (Spring does). So I'm pretty sure that Spring is not leaving any long-running or open connections on the table..

Thanks,
Galen

[Daniel Meyer]

Hi Galen,

sorry for the late response...

> 1) What is the lifetime/scope of an open session with respect to a process? 

the process is executed as a sequence of commands which are executed by the proces engine. Commands either result from API calls or from the Job Executor doing async continuation or timers.

In each command, the process engine will open a session in MyBatis. This leads to a connection being requested from the pool.

=> connections are checked out from the pool for the duration of a command. 

This is as far as the process engine is concerned. If you use  an external transaction manager (Spring or JTA) to scope transactions a transaction may also span two commands. Example (Spring):

@Transactional

public void doSomething() {

  runtimeService.startProcessInstance()

  taskService.complete()

}

In that case the connection will be used for the two commands.

> 2)  If a process takes longer to complete, does it impact how long connections remain open (and therefore not returned to the pool)? 

Only if an individual transaction within the process takes a long time.

2 Questions:

1) Which connection pool are you usign?

2) Are you using org.camunda.bpm.engine.impl.persistence.StrongUuidGenerator ?

Cheers,

Daniel

[galen...@gmail.com]

Hi Daniel,

Thanks for the information.

1) I'm using the Tomcat JDBC pool. For example, in server.xml:
<Resource name="jdbc/ProcessEngine"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
uniqueResourceName="process-engine"
driverClassName="com.mysql.jdbc.Driver"
url="DB_URL?useUnicode=true&amp;characterEncoding=UTF-8&amp;characterSetResults=utf8&amp;connectionCollation=utf8_unicode_ci"
...
...

2) I don't believe I'm using the StrongUuidGenerator. I don't see any "idGenerator" configuration/element in my configuration. How can I tell what I'm using?

Thanks,
Galen

[Daniel Meyer]

Hi Galen,

Is the process engine configured in bpm-platform.xml or are you
configuring it yourself in Spring?

Regards,
Daniel

[galen...@gmail.com]

Hi Daniel,

My bpm-platform.xml configures the process engine, then my Spring beans get this default engine. Here are some relevant snippets of my configuration:

------------------------------------------
------------------------------------------
In bpm-platform.xml:

<job-executor>
<job-acquisition name="default">
<properties>
<property name="lockTimeInMillis">300000</property>
<property name="waitTimeInMillis">2500</property>
<property name="maxJobsPerAcquisition">10</property>
</properties>
</job-acquisition>
</job-executor>

<process-engine name="default">
<job-acquisition>default</job-acquisition>
<configuration>org.camunda.bpm.engine.impl.cfg.StandaloneProcessEngineConfiguration</configuration>
<datasource>java:jdbc/ProcessEngine</datasource>

<properties>
<property name="history">full</property>
<property name="databaseSchemaUpdate">true</property>
<property name="authorizationEnabled">true</property>
<property name="jobExecutorDeploymentAware">true</property>
<property name="jobExecutorActivate">false</property>
</properties>

<plugins>
<!-- plugin enabling Process Application event listener support -->
<plugin>
<class>org.camunda.bpm.application.impl.event.ProcessApplicationEventListenerPlugin</class>
</plugin>

<!-- The following plugin allows you to grant administrator authorizations
to an existing LDAP user -->
<plugin>
<class>org.camunda.bpm.engine.impl.plugin.AdministratorAuthorizationPlugin</class>
<properties>
<property name="administratorUserName">xxxx</property>
</properties>
</plugin>

<!-- plugin enabling custom incident handler -->
<plugin>
<class>my.core.engine.plugins.CustomIncidentHandlerPlugin</class>
</plugin>
</plugins>

</process-engine>


------------------------------------------
------------------------------------------
In applicationContext.xml (Spring):

<jee:jndi-lookup id="dbDataSource" jndi-name="java:comp/env/jdbc/ProcessEngine" expected-type="javax.sql.DataSource" />

...
...
<!-- bind the process engine service as Spring Bean -->
<bean name="processEngineService" class="org.camunda.bpm.BpmPlatform" factory-method="getProcessEngineService" />

<!-- bind the default process engine as Spring Bean -->
<bean name="processEngine" factory-bean="processEngineService" factory-method="getDefaultProcessEngine" />

<bean id="repositoryService" factory-bean="processEngine" factory-method="getRepositoryService"/>
<bean id="runtimeService" factory-bean="processEngine" factory-method="getRuntimeService"/>
<bean id="taskService" factory-bean="processEngine" factory-method="getTaskService"/>
<bean id="historyService" factory-bean="processEngine" factory-method="getHistoryService"/>
<bean id="identityService" factory-bean="processEngine" factory-method="getIdentityService" />
<bean id="managementService" factory-bean="processEngine" factory-method="getManagementService"/>

[JavaCode]

Hello,

I'm facing the same issue.
have you find any solution for that.

[galen...@gmail.com]

Hi Daniel,

I have more information about this, and have posted the details in a JIRA ticket:

https://app.camunda.com/jira/browse/CAM-4590

Thanks, and please let me know if you have any questions,
Galen