程式碼學習不歸路

呈上篇 出現的錯誤，相伴隨的也可能會出現另一種錯誤 All pooled connections are in use

測試工具與套件

c# 7.0
RoslynPad
MySqlConnector

重現方式

Step1 查詢現在的最大連線數

SHOW GLOBAL VARIABLES LIKE 'max_connections';

Step2 設定最大連線數量為 151

SET GLOBAL max_connections = 151;

Step3 使用 client1 建立100條連線後，並設定 connection pool 連線最大長度為 100，但是不關閉，先等待 5 秒

#r "nuget: MySqlConnector, 2.3.5"
using System.Data;
using MySqlConnector;

for (var i = 1; i <= 100; i++)
{
   try
   {
       var db = GetDbConnection(@"Server=127.0.0.1;Database=db1;Uid=root;Pwd=123;Port=3306;MaximumPoolSize=100");
       //slow it
       Thread.Sleep(20);
   }
   catch (Exception ex)
   {
       Console.WriteLine($"{i}, {ex}");
       break;
   }
}
Thread.Sleep(5000);
"done".Dump();
try
{
   var db = GetDbConnection(@"Server=127.0.0.1;Database=db1;Uid=root;Pwd=123;Port=3306;MaximumPoolSize=100");
}
catch (Exception ex)
{
   Console.WriteLine($"{ex}");
}

IDbConnection GetDbConnection(string strConn)
{
   var connection = new MySqlConnection(strConn);
   if (connection.State != ConnectionState.Open)
   {
       connection.Open();
   }
   return connection;
}

step4 使用client2，建立51條連線後，等待 10 秒後關閉

#r "nuget: MySqlConnector, 2.3.5"
using MySqlConnector;
using System.Data;

for (var i = 1; i <= 51; i++)
{
   try
   {
       var db = GetDbConnection(@"Server=127.0.0.1;Database=db2;Uid=root;Pwd=123;Port=3306;MaximumPoolSize=100");
       //show it        
       Thread.Sleep(20);     
   }
   catch (Exception ex)
   {
       Console.WriteLine($"{i}, {ex}");
       break;
   }
}

"done".Dump();
Thread.Sleep(10 * 1000);

IDbConnection GetDbConnection(string strConn)
{
   var connection = new MySqlConnection(strConn);
   if (connection.State != ConnectionState.Open)
   {
       connection.Open();
   }
   return connection;
}

在 client1 就會因為 connection pool 都在使用了，且去要連線的時候，已經超過 151個連線數量，就會出現 Connect Timeout expired. All pooled connections are in use 的錯誤訊息

參考資料
https://dotblogs.com.tw/AceLee/2024/02/16/133323