Oracle JDBC connection timed out issue

Question

I have a scenario in production for a web app, where when a form is submitted the data gets stored in 3 tables in Oracle DB through JDBC. Sometimes I am seeing connection time out errors in logs while the app is trying to connect to Oracle DB through Java code. This is intermittent.

Below is the exception:

SQL exception while storing data in table
java.sql.SQLRecoverableException: IO Error: Connection timed out

Most of the times the web app is able to connect to data base and insert values in it but some times and I am getting this time out error and unable to insert data in it. I am not sure why am I getting this intermittent issue. When I checked the connections pool config in my application, I noticed the following things:

• Pool Size (Maximum number of Connections that this pool can open) : 10

• Pool wait (Maximum wait time, in milliseconds, before throwing an Exception if all pooled Connections are in use) : 1000

Since the pool size is just 10 and if there are multiple users trying to connect to data base will this connection time out issue occur ?

Also since there are 3 tables where the data insertion occurs we are doing the whole insertion in just one connection itself. We are not opneing each DB connection for each individual table.

NOTE: This application is deployed on AEM (Content Management system) server and connections pool config is provided by them.

Update: I tried setting the validation query in the connections pool but still I am getting the connection time out error. I am not sure whether the connections pool has checked the validation query or not. I have attached the connections pool above for reference.

Solution 1

I give this answer from programming point of view. There are multiple possibilities for this problem. These are following and i have added appropriate solution for it. As connection timeout occurs, means your new thread do not getting database access within mentioned time and it is due to:

• Possibility I: Not closing connection, there should be connection leakage somewhere in your application Solution
  You need to ensure this thing and need to check for this leakage and close the connection after use.

• Possibility II: Big Transaction Solution

  • i. Is these insertion synchronized, if it is so then use it very carefully. Use it at block level not method level. And your synchronized block size should be minimum as much as possible.

    What happen is if we have big synchronized block, we give connection, but it will be in waiting state as this synchronized block needs too much time for execution. so other thread waiting time increases. Suppose we have 100 users, each have 100 threads for that operation. 1st is executing and it takes too long time. and others are waiting. So there may be a case where 80th 90th,etc thread throw timeout. And For some thread this issue occurs.

    So you must need to reduce size of the synchronized block.

  • ii. And also for this case also check If the transaction is big, then try to cut the transaction into smaller ones if possible:-
    For an example here, for one insertion one small transaction. for second other small transaction, like this. And these three small transaction completes operation.

• Possibility III: Pool size is not enough if usability of application is too high Solution
  Need to increase the pool size. (It is applicable if you properly closes all the connection after use)

Solution 2

I would try two things:

• Try setting a validation query so each time the pool leases a connection, you're sure it's actually available. select 1 from dual should work. On recent JDBC drivers that should not be required but you might give it a go.

• Estimate the concurrency of your form. A 10 connections pool is not too small depending on the complexity of your work on DB. It seems you're saving a form so it should not be THAT complex. How many users per day do you expect? Then, on peak time, how many users do you expect to be using the form at the same time? A 10 connections pool often leases and retrieves connections quite fast so it can handle several transactions per second. If you expect more, increase the size slightly (more than 25-30 actually degrades DB performance as more queries compete for resources there).

• If nothing seems to work, it would be good to check what's happening on your DB. If possible, use Enterprise Manager to see if there are latches while doing stuff on those three tables.