Custom Processes stuck due to database connection pool issue

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

BG stops working, processes (or process engine) are stuck until restart.

Error in logs:
Caused by: com.niku.union.persistence.PersistenceException: Error getting a DB connection
    at com.niku.union.persistence.PersistenceController.doProcessRequest(PersistenceController.java:617)
    at com.niku.union.persistence.PersistenceController.processRequest(PersistenceController.java:308)
    at com.niku.security.cache.UserSessionCache.retrieveFromPersistence(UserSessionCache.java:234)
    ... 6 more
Caused by: org.apache.tomcat.jdbc.pool.PoolExhaustedException: [Process Controller (tenant=clarity)] Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none available[size:1000; busy:1000; idle:0; lastwait:30000].

Alternatively, the issue may be detected before the BG outage occurs if the DBA is receiving alert son database connection numbers and kills them in a timely manner

Cause

This is not an Out Of Memory nor a Clarity product issue. It's an indication that the database connections have run out. This usually only happens when a custom process or job is opening SQL connections but not closing them. It's also not a configuration of the database. 1000 connections should be plenty enough for anything done with the product. If you have a lot of custom processes that open connections, some of them may have a connection leak. This is why when you run your custom processes, you can eventually run out of connections.  

Support may be able to review any heap dump evidence and determine the root cause

Resolution

Closing GEL connections automatically upon completing the scripts will be included in 16.0.1 and 16.0.0.1 as DE63078

We recommend the following that should be addressed by the developers of the custom process:

• Check the processes that you currently run, and ensure that every database connection open is then closed. Check especially all the processes that contain direct JDBC SQL connection.
• A dummy loop can be used to try and catch block that would close the connections even if the script errors out. This is the way all the other processes should be made, and if that's not the case we suggest redesigning them or reconsidering their use. 
• Best practices on how to approach this here: Database connections in GEL scripts best practices