Oracle Data Provider for .NET: Connection request timed out

We had a similar issue, and it took a while to debug this and fix this. Our code on getting stressed with many input files, and many threads processing, each thread using Entity framework and opening Oracle db connection, and doing a bunch of db queries and inserts, used to file occasionally. But works most of the time.

I modified out DbContext constructor to explicitly open the OracleConnection. I added some code like this

for (i = 0; i < 5; i++)   try {       oracleConnection.Open();   } catch (OracleException) {     Sleep for 15 ms and retry.      On last attempt I also do OracleConnection.ClearAllPools()   }

It improved, but still didn't solve it completely. I broke in the catch from debugger, and saw that many threads are trying to open and few threads are processing away.On Open in Oracle stack, Oracle for its internal purpose does ThreadPool.QueueUserWorkItem and waits for its completion. I can see on top of stack its wait. Here plenty of pooled connections are available (default is 100), I am hardly using 10. So it is not out of resource.

But the issue is in our code also we used ThreadPool.QueueUserWorkItem with no additional throttling. I thought that was cool to just queue all jobs we need to do, how much ever we need to this, and let .NET take care of this. But this has subtle issue. All our jobs have consumed the full queue count. When OracleConnection wants to get a pooled connection from the pool, it also queues to the thread pool. But it is never going to complete. Our jobs are all waiting for OracleConnection.Open, and its Queued Thread proc will still be in queue. So finally the wait will exit by timeout.It is a pity that even though there is plenty of pooled connection is available, we have consumed all ThreadPool proc, and Oracle's threadpool didn't even get a chance. Here setting ThreadPool.SetMaxThreads also isn't going to help. The issue is still the same. We hog all thread pool resource, and Orcale isn't going to find one and will still be in queue.

The fix is not to rely on only ThreadPool, but we add our own throttling as well. I used BlockingCollection and sempahores and add only some limit number of concurrent jobs in ThreadPool, say 5. In this way OracleConnection will always find a ThreadPool thread available, and wont fail.

Even I used to get this issue more Frequently, even after the use of Connection.Close()

After a long Analysis I have learnt few thing as mentioned below

1. Connection.Close() doesnt dispose the db connection
2. Connection Time out doesnt mean that issue is only with db query
3. Connection Time out could also be due to exhaustive connections in the connection pool (which was the culprit for me as it reached the maximum sessions of the db connection)

Fix :- Analysis took long time but fix is of just 2 mins

using(DbConnection instance){}

Eg :-

using (DbConnection  objDbConnection = new DbConnection()){   ojDbConnection.PersistData();}

Under PersistData(); All the db Operations like Open, close e.tc. will be performed

As we all knows "Using" is short form of

try{}catch(){}Finally{  Dispose objDbConnection;}

Hope it helps, as it helped me

try adding connection.close() at the end. I don't see releasing connections in your code and returning them to connection pool explicitly.So connection is being returned to connection pool only when GC is started.