高并发导致的数据库连接问题

海底星光

于 2024-08-08 16:20:12 发布

阅读量258

点赞数 8

分类专栏：奇葩问题文章标签：数据库 c#

本文链接：https://blog.csdn.net/qq_36522188/article/details/141027840

版权

奇葩问题专栏收录该内容

16 篇文章 0 订阅

订阅专栏

高并发导致数据库报错 – 与服务器多线程处理能力相关

报错内容：

中文提示 :  连接数据库过程中发生错误，检查服务器是否正常连接字符串是否正确，错误信息：A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)DbType="SqlServer";ConfigId="".
English Message : Connection open error . A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)DbType="SqlServer";ConfigId=""

测试代码如下

主程序代码

 private static  void Main(string[] args)
 {
     int i = 0;
     SqlInit.Init();
     List<Task> tasklist1 = new List<Task>();
     for (int j = 0; j < 500; j++)
     {

         tasklist1.Add(
         Task.Run( () =>
         {
             Test test = new Test();
              test.Init();
         }));
     }
     Task.WaitAll(tasklist1.ToArray());//全部完成之后在返回主进程
     //File.AppendAllText("‪1.txt", $"同步结束{DateTime.Now}");

     Console.ReadLine();
 }

数据库注入代码：

 public static class SqlInit
 {
    public static IServiceCollection  services = new ServiceCollection();
     public static IServiceProvider serviceResolve;
     public static void Init() {
         services.AddScoped<ISqlSugarClient>(options =>
         {
             var client = new SqlSugarClient(new List<ConnectionConfig>(){ new ConnectionConfig() {
              DbType = DbType.SqlServer,
             ConnectionString = "server=10.10.172.46;user id=;password=;database=ZN2401901;TrustServerCertificate=True;Pooling=true;Min Pool Size=1 ;Max Pool Size=1000",
             IsAutoCloseConnection = true,
             InitKeyType = InitKeyType.Attribute,
         }});
             client.Aop.OnLogExecuting = (sql, pars) =>
             {
                 Console.WriteLine(sql + "\r\n" +
                 client.Utilities.SerializeObject(pars.ToDictionary(it => it.ParameterName, it => it.Value)));
                 Console.WriteLine();
             };
             return client;
         });//单例注入SqlSugar
         services.AddSqlSugarScope<SqlSugarScope>(delegate (ConnectionConfig config)
         {
             config.DbType = DbType.SqlServer;
             config.ConnectionString = "server=10.10.172.46;user id=;password=;database=ZN2401901;TrustServerCertificate=True;Pooling=true;Min Pool Size=1 ;Max Pool Size=1000 ";
             config.IsAutoCloseConnection = true;
             config.InitKeyType = InitKeyType.Attribute;
             config.MoreSettings = new ConnMoreSettings
             {
                 DisableNvarchar = true
             };
         });
         serviceResolve = services.BuildServiceProvider();
     }

   
 }

  public static IServiceCollection AddSqlSugarScope<T>(this IServiceCollection services, Action<ConnectionConfig> configAction, ServiceLifetime lifetime = ServiceLifetime.Singleton) where T : SqlSugarScope
  {
      if (lifetime == ServiceLifetime.Singleton)
      {
          services.AddSingleton(delegate (IServiceProvider serviceProvider)
          {
              ConnectionConfig connectionConfig = new ConnectionConfig
              {
                  ConfigureExternalServices = new ConfigureExternalServices()
              };
              configAction(connectionConfig);
              SqlSugarScope sqlSugarScope = new SqlSugarScope(connectionConfig, delegate (SqlSugarClient dB)
              {
                      dB.Ado.IsEnableLogEvent = true;
                      dB.Aop.OnLogExecuted = delegate (string sql, SugarParameter[] pars)
                      {
                          foreach (SugarParameter sugarParameter in pars)
                          {
                              sql = sql.Replace(sugarParameter.ParameterName.ToString(), "'" + sugarParameter.Value?.ToString() + "'");
                          }
                          Console.WriteLine("执行后SQL:" + sql);
                      };
                      dB.Aop.OnError = delegate (SqlSugarException exp)
                      {
                      };
                      dB.Aop.OnDiffLogEvent = delegate
                      {
                      };
              });
              return (T)sqlSugarScope;
          });
      }

      return services;
  }

具体业务代码

 public class Test
 {
     private static object _lock = new object();

     public Test()
     {
     }
     public async Task InitAsync() {
         //Console.WriteLine("当前线程：" + Thread.CurrentThread.ManagedThreadId.ToString());
         var Db = (SqlInit.serviceResolve.GetRequiredService<ISqlSugarClient>()).CopyNew();
         var addr = getMemory(Db);
         try
         {
             var dt = await Db.Ado.GetDataTableAsync("SELECT *, 0 AS _NAV_ORDER_F_ FROM [dbo].[iic_invocation_interface] ORDER BY _NAV_ORDER_F_ OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY;");
             var dt1 = await Db.Ado.GetDataTableAsync("select COUNT(*) as \"count\" from master.dbo.sysprocesses where dbid = DB_ID('ZN2401901');");
             
             Console.WriteLine(dt.Rows.Count);
           
             Console.WriteLine("当前线程：" + Thread.CurrentThread.ManagedThreadId.ToString()+ $"完成:{addr},当前连接数：{dt1.Rows[0]["count"]}");
         }
         catch (Exception ex) {
             Console.WriteLine("当前线程：" + Thread.CurrentThread.ManagedThreadId.ToString()+$"DB：{addr}"+ $"{ex.Message}");
             //Console.WriteLine("DB内存地址为：" + addr);
             //Console.WriteLine("错误原因：" + ex.Message);
         }
     }

     public void Init()
     {
         //Console.WriteLine("当前线程：" + Thread.CurrentThread.ManagedThreadId.ToString());
         var Db = SqlInit.serviceResolve.GetRequiredService<SqlSugarScope>();
         var addr = getMemory(Db);
         try
         {
             var dt =  Db.Ado.GetDataTable("SELECT *, 0 AS _NAV_ORDER_F_ FROM [dbo].[iic_invocation_interface] ORDER BY _NAV_ORDER_F_ OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY;");
             var dt1 =  Db.Ado.GetDataTable("select COUNT(*) as \"count\" from master.dbo.sysprocesses where dbid = DB_ID('ZN2401901');");

             Console.WriteLine(dt.Rows.Count);

             Console.WriteLine("当前线程：" + Thread.CurrentThread.ManagedThreadId.ToString() + $"完成:{addr},当前连接数：{dt1.Rows[0]["count"]}");
         }
         catch (Exception ex)
         {
             Console.WriteLine("当前线程：" + Thread.CurrentThread.ManagedThreadId.ToString() + $"DB：{addr}" + $"{ex.Message}");
                 AppendTextToFile("当前线程：" + Thread.CurrentThread.ManagedThreadId.ToString() + $"DB：{addr}" + $"{ex.Message}");
             //Console.WriteLine("DB内存地址为：" + addr);
             //Console.WriteLine("错误原因：" + ex.Message);
         }
     }
     static void AppendTextToFile(string message)
     {
         string filePath = @"F:\test\sqltest\bin\Debug\net6.0\1.txt";
         lock (_lock)
         {
             using (StreamWriter writer = new StreamWriter(filePath, true)) // true 表示追加模式
             {
                 writer.WriteLine(message);
             }
         }
     }
     public string getMemory(object obj)
     {
         GCHandle handle = GCHandle.Alloc(obj, GCHandleType.WeakTrackResurrection);
         IntPtr addr = GCHandle.ToIntPtr(handle);
         return $"0x{addr.ToString("X")}";
     }


 }