Connection pool – one process – many threads

Question

My understanding of the connection pool is; if the connectionstring is the exact same, then we reuse the connection instead of make a new connection.

My problem is that I’m creating many threads for parallel processing. In this “dummy” program, I create 500 threads and let the ThreadPool function handle the threads.

The steps are:

1. Each thread make an update table in SQL. (Stating the timestamp for the update)

2. Then the thread sleeps between 1 to 10 sec (random).

3. At the end the thread makes another update in SQL (Stating the timestamp for the end time)

4. Then the thread exit

   class Program
   {
       static void Main(string[] args)
       {
           int numberOfThreads = 150;
   
           ThreadPool.SetMinThreads(numberOfThreads, numberOfThreads);
           ThreadPool.SetMaxThreads(numberOfThreads, numberOfThreads);
   
           List<Int64> chunkList = new List<Int64>();
   
           int maxNumberOfChunks = 500;
           for (int i = 1; i < maxNumberOfChunks; i++)
           {
               chunkList.Add(i);
           }
   
           foreach (Int64 chunk_id in chunkList)
           {
               ThreadPool.QueueUserWorkItem(new WaitCallback(ThreadWorker), new arguments { chunk_id = chunk_id });
           }
           Console.ReadLine();
   
       }
       static void ThreadWorker(Object stateInfo)
       {
           arguments arguments = (arguments)stateInfo;
   
           Console.WriteLine("Chunk # : {0} is set to START", arguments.chunk_id);
           UpdateSQLdb(arguments.chunk_id, DateTime.Now, null, null, "START", null, null);
   
           Random random = new Random();
           int mseconds = random.Next(1, 10) * 1000;
           System.Threading.Thread.Sleep(mseconds);
           Console.WriteLine("Chunk # : {0} is sleeping for {1} sec.", arguments.chunk_id, mseconds);
   
           Console.WriteLine("Chunk # : {0} ist set to END", arguments.chunk_id);
           UpdateSQLdb(arguments.chunk_id, null, DateTime.Now, null, "END", null, null);
       }
       struct arguments
       {
           public Int64 chunk_id;
       }
   
       static void UpdateSQLdb(Int64 CHUNK_ID, DateTime? START_TS = null, DateTime? END_TS = null, Enum CHUNK_STATUS = null, string error_messages = null, byte? NEW_CALCULATION_ATTEMPTS = null, byte? NEW_POSTPROCESS_ATTEMPTS = null)
       {
           using (SqlConnection conn = new SqlConnection("Data Source=C55S01;Initial Catalog=MCS_BATCH;Integrated Security=SSPI;Asynchronous Processing=True")) //Timeout=60;Max Pool Size=200;Pooling=True;
           {
               int result = -1;
               conn.Open(); //<-- Each time I open a connection. It creates a new instead of reusing one from the ConnectionPool
   
               try
               {
                   using (SqlCommand cmd = new SqlCommand("TEST.UpdateSQL", conn))
                   {
                       cmd.CommandTimeout = 300; 
                       cmd.CommandType = System.Data.CommandType.StoredProcedure;
   
                       cmd.Parameters.Add("@CHUNK_ID", SqlDbType.BigInt, 15).Value = CHUNK_ID;
                       cmd.Parameters.Add("@START_TS", SqlDbType.DateTime2, 7).Value = START_TS;
                       cmd.Parameters.Add("@END_TS", SqlDbType.DateTime2, 7).Value = END_TS;
                       cmd.Parameters.Add("@ERR_MESSAGE", SqlDbType.VarChar).Value = error_messages;
                       cmd.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int, 4).Direction = System.Data.ParameterDirection.ReturnValue;
   
                       try
                       {
                           result = cmd.ExecuteNonQuery();
   
                           int return_value = (int)cmd.Parameters["@ReturnValue"].Value;
                           if (return_value != 0)
                           {
                               Console.WriteLine("1. Error in running TEST.UpdateSQL, return value is : {0}", cmd.Parameters["@ReturnValue"].Value);
                           }
                       }
                       catch (SqlException ex)
                       {
                           UpdateSQLdb(CHUNK_ID, null, DateTime.Now, null, ex.Message.ToString(), null, null);
                           Console.WriteLine("2. Error executing TEST.UpdateSQL : {0}", ex);
                       }
                   }
               }
               catch (Exception ex)
               {
                   Console.WriteLine("3.Error in TEST.UpdateSQL : {0}", ex);
                   throw;
               }
               if (conn.State == ConnectionState.Open)
               {
                   Console.WriteLine("Closing connection....");
                   conn.Close();
               }
               conn.Dispose();
           }
       }
   }
   

   }

My problem is that I get a System.InvalidOperationException was unhandled (Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.)

I have monitored the number of connections on the SQL server, and It quickly get to 100 connections (which is the default max number of connections in a pool)

If have tried to add these parameters to the connection string : Timeout=60"Max Pool Size=200;Pooling=True;

But this will just postpone the problem to a later stage, because the connection pool will hit 200 and the timeout will be reach at some point.

Question: Why are the connections created over and over again, instead of reusing one from the connection pool?

Any hint, tips or advice are highly appreciated.

Answer 1

It's doing exactly what you asked it to do. It's using connections from the pool but you've given it too much work. If you have 500 threads and 200 connections each thread can't have a connection. You should probably have as many connections as threads.

If you have yet more work to do (all 500 threads are busy), then you have to either return an error to the consumer or otherwise throttle the input into your application.