OracleConnection Connection Request Timed out

0

Having an issue in a .Net Core 3.1 multi-threaded console app; OracleConnection will consistently throw an exception for "Connection request timed out" if the number of calls to ThreadPool.QueueUserWorkItem exceeds the number of threads set in ThreadPool.SetMaxThreads. The "task" is a call to a stored proc in Oracle which is returning data via a cursor. In a single thread all works fine, no issues

This is in .Net Core 3.1 using the latest nuget version of Oracle.ManagedDataAccess.Client

Have tried many combinations of settings in the Oracle Connection string, pool size, Connection Lifetime Connection Timeout etc. Nothing seems to help. Once the number of tasks queued exceed the number of threads the Open call on the connection will throw an exception.

Tracing using OracleConfiguration reveals the exception:

2020-09-23 21:30:51.997780 TID:6   (PRI) (ERR) (CP) PoolManager.CreateNewPR() (txnid=n/a) Oracle.ManagedDataAccess.Client.OracleException (0x80004005): Connection request timed out
   at OracleInternal.ConnectionPool.PoolManager`3.CreateNewPR(Int32 reqCount, Boolean bForPoolPopulation, ConnectionString csWithDiffOrNewPwd, OracleConnection connRefForCriteria, String instanceName, List`1 switchFailedInstNames)

Some threads are left at the following state "Searching for a idle connection":

2020-09-23 21:28:57.443815 TID:13  (PRI) (ENT) (CP) PoolManager.Get() MultiTenant : Searching for a idle connection, retryCountWithoutAffinity: 0

Code to reproduce:

using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
using System;
using System.Data;
using System.Diagnostics;
using System.Threading;

namespace OracleDBStoredProc
{
    class Program
    {
        private static Object lockObj = new Object();

        static void Main(string[] args)
        {
            int numThreads = 10;
            ThreadPool.SetMaxThreads(numThreads, numThreads);

            for (int i = 0; i < numThreads+ 1; i++)
            {
                string myMsg = $" TASK {i} ";
                ThreadPool.QueueUserWorkItem(CallToDatabase, myMsg);
                Console.WriteLine(myMsg + " created.");
            }

            Console.WriteLine("Finished");
            Console.ReadKey();
        }

        static void CallToDatabase(Object stateInfo)
        {

            OracleConfiguration.TraceOption = 1;
            OracleConfiguration.TraceFileLocation = @"C:\traces";
            OracleConfiguration.TraceLevel = 2;

            string taskName = (string)stateInfo;

            Console.WriteLine(taskName + "started");

            string constr = "User Id=MyUser;Password=MyPswd;Data Source=(DESCRIPTION= (ADDRESS= (PROTOCOL=tcp)(HOST=MyServer)(PORT=1521))(CONNECT_DATA= (SERVICE_NAME=myservice)))";

            OracleConnection con = new OracleConnection(constr);
            con.Open();

            var cmd = new OracleCommand("MY_APP.GET_MY_DATA", con)
            {
                CommandType = CommandType.StoredProcedure
            };

            OracleParameter p1 = cmd.Parameters.Add("refcur_ret", OracleDbType.RefCursor, ParameterDirection.Output);
            p1.Direction = ParameterDirection.Output;

            cmd.ExecuteNonQuery();

            OracleDataReader reader1 = ((OracleRefCursor)p1.Value).GetDataReader();

            DataTable test = new DataTable();
            test.Load(reader1);

            reader1.Close();
            reader1.Dispose();

            p1.Dispose();

            cmd.Dispose();

            con.Close();
            con.Dispose();

            String msg = null;
            Thread thread = Thread.CurrentThread;
            lock (lockObj)
            {
                msg = String.Format("{0} thread information\t", taskName) +
                      String.Format("   Thr ID: {0}\t\t", thread.ManagedThreadId);
            }

            Console.WriteLine(msg);
        }
    }
}
c#
oracle
multithreading
odp.net
.net-core-3.1
asked on Stack Overflow Sep 23, 2020 by Brian • edited Sep 28, 2020 by Brian

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0