This article provides additional details surrounding the JDBC Timeout parameter as well as saturated connection pools.
What the JDBC Timeout does
The web.xml JDBCTimeout parameter is used to determine the longest period that a repository database connection can be checked out of the connection pool.
A database connection will be checked out of the pool, usually for a single action that the user is performing. Lots of SQL queries can be run against the connection while it is checked out.
For example, if you save a View, it will check-out a connection, write all the records to the database with that connection, and then return the database connection to the pool (check-in), when it is done.
If the database connection is checked out for longer than the JDBCTimeout limit, then the connection will be closed. This will break whatever process was using that connection.
So, if the Save View process exceeds the Timeout, an error will be shown in the UI, saying that the View couldn't be saved.
Problems with long Timeouts
The problem with a long timeout is that if you get lots of processes that are taking a long time, then these database connections will be in use. This means that when another process needs database connection it will need to open a new connection, but it will eventually reach a limit.
So the database pool will start with the minimum number of connections, specified by the JDBCMinCount in the web.xml. When a connection is required by a user, or a background task, it will ask the pool for a connection. If there are idle connections in the pool, then one of these will be given to the process. If all the current connections in the pool are in use, then it will create a new one. It will be able to create new connections as needed until the number of connections reaches the JDBCMaxCount.
When the connection pool reaches it maximum size, the connection pool becomes saturated.
Once the connection pool is saturated, problems will start to occur. If all of the connections are in use, and a new connection is requested, the user or background task will need to wait for a connection to become available. This means that it will need to wait until a connection is released by another process. It may need to wait (in the worst case) for the number of seconds set for the JDBCTimeout period. For users in the Yellowfin user interface, it will appear that the application is unresponsive, if a database connection is not available, it cannot be returned in a reasonable time.
Causes of a saturated Connection Pool
A saturated connection pool can be caused by three things...
1. There are processes that are taking way too long to run - These are most likely to be background broadcasts, background cached filter population and background access-filter population tasks. If you have already altered the Timeout from the default 5 minutes, this would likely have been done for a reason relating to one of these tasks.
An '/info_threads.jsp' dump should show what is running when the application becomes unresponsive. If it is related to background tasks, then these can probably be managed and distributed so they don't occur at the same time. If it is not related to background tasks, the thread dump should show what it is doing.
2. Database Deadlock - This can be caused by a timing issue in the database, and can usually be resolved with a code change.
This is when two processes write to the same table, and each process is waiting for the other to finish, but neither of these ever yield. This will cause both connections to remain open for the full period defined by the JDBCTimeout.
You can usually see these issues from the database side. If this is the case, it may be worth checking if there are any locks on the database client-side. An Info Thread dump should show where these two locked processes are as well, but it can only be confirmed it a database lock with corresponding information from the database side.
3. Code Deadlock - Although infrequent, this is a problem with the code. This would need a code change to resolve.
A code deadlock is like a database dead lock, but the lock is on internal Yellowfin resources (like caches, etc.), and not on the database.
Again, two processes will be waiting for the other to finish, and neither will yield. If a database connection was checked out prior to this lock occurring, it will not be released. The JDBC connection will be released after the JDBCTimeout, but the code lock will remain, and probably block other operations as well, only a restart can fix this.
If you have any questions, or running into issues relating to connections, please reference our Troubleshooting Database Connections article, and if still struggling, please raise a ticket with our support team.
Regards,
The Yellowfin Support Team.