.NET 8 EF连接SQL Server连接池耗尽问题及复现测试咨询

算力阿峰

2026-7-4

问题描述

我有一个基于.NET 8的应用，使用Entity Framework连接SQL Server Standard，ApplicationDbContext通过依赖注入（DI）以Scoped服务的形式在整个应用中注入，理论上连接会被自动释放。此问题与之前被标记为重复的问题完全不同。

随着用户量增长，近期夜间高峰时段出现以下错误：

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.

我已在连接字符串中设置Max Pool Size=5000，但似乎没有效果，连接字符串如下：

"Data Source=localhost;Initial Catalog=RackEmApp_live;User ID=***;***;MultipleActiveResultSets=True;Encrypt=False; Max Pool Size=5000;"

根据资料，连接池默认已启用，无需额外配置。

我尝试复现该问题：将Max Pool Size设为1，用JMeter压测端点，但应用仍能正常响应，与设为5000时表现一致。因此我缺乏可靠的测试方法来验证解决方案是否有效，请问该如何可靠地复现并测试？

相关代码

MobileController.cs

private readonly UserManager<ApplicationUser> _userManager;
private readonly ILogger _logger;
private readonly ApplicationDbContext _context;
private readonly IOptions<Settings> _settings;
private readonly IEmailSender _emailSender;


public MobileController(
    UserManager<ApplicationUser> userManager,
    ApplicationDbContext context,
    IOptions<Settings> settings,
    IEmailSender emailSender)
{
    _userManager = userManager;
    _context = context;
    _settings = settings;
    _emailSender = emailSender;
}

public async Task<IActionResult> GetResult(MatchType matchType, long referenceid, string lastchecked)
{
    DateTime datetocheck = DateTime.Parse(HttpUtility.UrlDecode(lastchecked));

    Result response = new Result();
    if (matchType == MatchType.League)
    {
        response = await _context.Results
            .Where(i => i.Fixture.Id == referenceid && i.LastUpdated >= datetocheck)
            .Select(i => new Result()
            {
                ReferenceId = referenceid,
                MatchType = matchType,
                ResultId = i.Id,
                HomeFrameScore = i.HomeScore,
                AwayFrameScore = i.AwayScore,
                HomeHandicap = i.HomeTeam.Handicap,
                AwayHandicap = i.AwayTeam.Handicap,
                MatchStatus = i.Status,
                SubmitStatus = i.SubmitStatus,
                Frames = i.ResultFrames.Select(f => new Frame
                {
                    ResultFrameId = f.Id,
                    FrameNumber = f.FrameNumber,
                    HomeBreak = f.HomeBreak,
                    HomeDish = f.HomeDish,
                    HomeForfeit = f.HomeForfeit,
                    HomeLockedIn = f.HomeLockedIn,
                    HomeScore = f.HomeScore,
                    HomeDisherId = f.HomeDisherId,
                    AwayBreak = f.AwayBreak,
                    AwayDish = f.AwayDish,
                    AwayForfeit = f.AwayForfeit,
                    AwayLockedIn = f.AwayLockedIn,
                    AwayScore = f.AwayScore,
                    AwayDisherId = f.AwayDisherId,
                    Status = f.FrameStatus,
                    MatchFormatSectionId = f.MatchFormatSection.Id,
                    HomeFramePlayers = f.ResultFramePlayers
                                        .Where(i => i.HomeAway == HomeAway.Home)
                                        .Select(fp => new FramePlayer
                                        {
                                            ResultFramePlayerId = fp.Id,
                                            Number = fp.Number,
                                            ResultFrameId = fp.ResultFrame.Id,
                                            Joker = fp.Joker,
                                            HomeAway = fp.HomeAway,
                                            PlayerId = fp.PlayerSeasonTeam != null ? fp.PlayerSeasonTeam.Player.Id : 0
                                        }).ToList(),
                    AwayFramePlayers = f.ResultFramePlayers
                                        .Where(i => i.HomeAway == HomeAway.Away)
                                        .Select(fp => new FramePlayer
                                        {
                                            ResultFramePlayerId = fp.Id,
                                            Number = fp.Number,
                                            ResultFrameId = fp.ResultFrame.Id,
                                            Joker = fp.Joker,
                                            HomeAway = fp.HomeAway,
                                            PlayerId = fp.PlayerSeasonTeam != null ? fp.PlayerSeasonTeam.Player.Id : 0
                                        }).ToList(),

                }).ToList()
            })
            .FirstOrDefaultAsync();

        if (response == null)
            return NoContent();
    }
    return Json(response);
}

ApplicationDbContext.cs

namespace RackEmApp.Data
{
    public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
            
        }

        public ApplicationDbContext()
        {
        }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);
            // Customize the ASP.NET Identity model and override the defaults if needed.
            // For example, you can rename the ASP.NET Identity table names and more.
            // Add your customizations after calling base.OnModelCreating(builder);

            // configures one-to-many relationship
            builder.Entity<Sponsor>()
                .HasOne<League>(i => i.League);

            // other configs omitted for brevity
        }

        public DbSet<League> Leagues { get; set; }
        // lots more db sets omitted for brevity
    }
}

Startup.cs

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<ApplicationDbContext>(options =>
    {
        options.UseSqlServer(Configuration.GetConnectionString("RackEmAppDB"));
        options.EnableSensitiveDataLogging();
    });

    services.AddIdentity<ApplicationUser, IdentityRole>(config =>
        {
            config.SignIn.RequireConfirmedEmail = true;
        })
        .AddEntityFrameworkStores<ApplicationDbContext>()
        .AddDefaultTokenProviders();

//more stuff omitted for brevity

解决方案与复现方法

一、先排查连接池耗尽的真实原因

你的ApplicationDbContext是Scoped注入，理论上请求结束会自动释放，但需先确认以下潜在问题：

检查是否存在未正确await的异步操作：虽然你的GetResult方法用了await，但如果其他业务代码有未await的异步调用，会导致DbContext被异常持有，连接无法释放。
检查查询执行时长：你的查询关联了多层导航属性（ResultFrames、ResultFramePlayers、PlayerSeasonTeam等），若数据量较大，会导致查询耗时过长，连接被长时间占用，最终耗尽连接池。
检查单例服务中的DbContext使用：如果后台任务或单例服务直接注入了Scoped的DbContext，会导致实例被长期持有，连接无法回收。

二、可靠复现连接池耗尽的方法

你之前的JMeter压测未复现问题，是因为默认请求结束过快，连接立刻回到池里。需模拟连接被长时间占用的场景：

1. 修改测试接口，强制占用连接

在测试环境的GetResult方法中添加延迟，模拟慢查询：

// 在FirstOrDefaultAsync之后添加
await Task.Delay(5000); // 模拟查询耗时5秒，占用连接5秒

2. 调整JMeter压测配置

设置线程数为Max Pool Size + 1：比如把Max Pool Size设为5，线程数设为6，确保并发请求数超过连接池上限。
关闭连接复用：在HTTP请求的高级设置中取消勾选"Keep-Alive"，强制每个请求从连接池获取新连接。
添加断言：检查响应是否包含连接池超时的错误信息，验证复现成功。

3. 用代码直接模拟并发请求

写一个控制台程序，持续发起并发请求：

using System.Net.Http;
using System.Threading.Tasks;
using System.Collections.Generic;

class Program
{
    static async Task Main(string[] args)
    {
        var httpClient = new HttpClient();
        var tasks = new List<Task>();
        int maxConcurrent = 10; // 设置为Max Pool Size + 1

        for (int i = 0; i < maxConcurrent; i++)
        {
            tasks.Add(Task.Run(async () =>
            {
                while (true)
                {
                    try
                    {
                        var response = await httpClient.GetAsync("https://你的测试地址/api/Mobile/GetResult?matchType=League&referenceid=1&lastchecked=2024-01-01");
                        response.EnsureSuccessStatusCode();
                        Console.WriteLine($"请求成功: {DateTime.Now}");
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"请求失败: {ex.Message}");
                    }
                    await Task.Delay(1000); // 每秒发起一次请求，确保连接持续被占用
                }
            }));
        }

        await Task.WhenAll(tasks);
    }
}

运行该程序，当并发数超过连接池大小时，会触发连接池超时错误。

三、验证解决方案的有效性

修改代码后（比如优化查询、修复连接泄漏），用上述复现方法测试：

若之前能稳定复现错误，修改后错误不再出现，说明解决方案有效。
可通过SQL Server监控连接数：执行SELECT COUNT(*) FROM sys.dm_exec_connections查看实时连接数，确认连接池未被耗尽。

四、额外优化建议

优化查询性能：为Results.LastUpdated、Results.FixtureId、ResultFrames.ResultId等字段添加索引，减少查询执行时间，降低连接占用时长。
正确使用DbContext在单例服务中：若后台任务或单例服务需访问数据库，使用IServiceScopeFactory创建临时作用域，用完及时释放：

using var scope = _serviceScopeFactory.CreateScope();
var context = scope.ServiceProvider.GetRequiredService<ApplicationDbContext>();
// 执行数据库操作

监控连接池状态：在应用中添加日志，记录连接池的可用连接数、已用连接数等状态（可通过SqlConnection的相关属性获取），方便后续排查问题。

内容的提问来源于stack exchange，提问作者Matthew Warr

.NET 8 EF连接SQL Server连接池耗尽问题及复现测试咨询

相关代码 #

MobileController.cs #

ApplicationDbContext.cs #

Startup.cs #

一、先排查连接池耗尽的真实原因 #

二、可靠复现连接池耗尽的方法 #

1. 修改测试接口，强制占用连接 #

2. 调整JMeter压测配置 #

3. 用代码直接模拟并发请求 #

三、验证解决方案的有效性 #

四、额外优化建议 #