高并发导致数据库报错 – 与服务器多线程处理能力相关
报错内容:
中文提示 : 连接数据库过程中发生错误,检查服务器是否正常连接字符串是否正确,错误信息: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")}";
}
}
重点注释
♿var Db = SqlInit.serviceResolve.GetRequiredService(); //每个线程都会创建一个新的连接对象
♿当查询完成时这个从连接池获取的连接对象会被释放
存在问题点
- ⛔硬件能力差 处理多线程速度慢 导致数据库连接释放慢 最终引发数据库连接池满的情况
- 🚫数据库连接字符串没有设置最大连接数 则默认为100 导致数据库连接池满
解决方式
- ✔️控制线程创建速度和数量
- ✔️优化线程业务处理时间
- ✔️增加数据库连接池大小