C# DbContext并发操作异常：二次数据库查询报错求助

错误信息 #

System.InvalidOperationException: '在之前的操作完成前，已在此上下文实例上启动了第二个操作。这通常是由不同线程同时使用同一个DbContext实例导致的。有关如何避免DbContext线程问题的更多信息'

错误触发于第二次数据库查询操作：_context.UserPTO.Where(up => up.UserId == user.Id).FirstOrDefault()

问题根源 #

1. 同步/异步方法混用：调用异步方法SaveChangesAsync()但未用await等待完成，导致DbContext在处理未完成的异步操作时，又被用于执行新查询，引发并发冲突。
2. DbContext生命周期管理不当：Worker Service的Worker是单例生命周期，若错误持有Scoped的DbContext实例，会增加多线程冲突风险；同时代码使用类级别_context变量，放大了上下文复用问题。
3. 循环内操作未串行化：循环中执行查询和异步保存时，未等待操作完成就进入下一次迭代，导致上下文同时处理多个未完成请求。

解决方法 #

1. 修正异步调用，确保操作串行执行 #

将UpdatePTO改为异步方法，所有数据库操作使用异步版本并添加await，同时用局部上下文变量替代类级别变量：

public async Task UpdatePTO()
{
    try
    {
        using (var context = new DataContext(GetAllOptions()))
        {
            var users = await context.Users.ToListAsync();

            if (users?.Count > 0)
            {
                foreach (var user in users)
                {
                    var plusPTO = 0.00M;
                    var workedYear = DateTime.Now.Date.Year - user.StartWorkDate.Date.Year;

                    // 修正原代码重复的case条件，根据实际业务调整逻辑
                    switch (workedYear)
                    {
                        case int n when n > 3:
                            plusPTO = 1.50M;
                            break;
                        case int n when n <= 3:
                            plusPTO = 1.25M;
                            break;
                    }

                    var userPTO = await context.UserPTO
                        .Where(up => up.UserId == user.Id)
                        .FirstOrDefaultAsync();

                    if (userPTO == null)
                    {
                        continue;
                    }

                    userPTO.Pto += plusPTO;

                    var userPTOHistory = new UserPTOHistory
                    {
                        UserId = user.Id,
                        PTOType = "",
                        UserPTOId = userPTO.Id,
                        CurrentPTO = userPTO.Pto,
                        NeedPTO = 0,
                        VerifiedType = "Accepted",
                        CreatedDate = DateTime.Now,
                        VerifiedDate = DateTime.Now,
                        CalculatedPTO = userPTO.Pto
                    };

                    context.Add(userPTOHistory);
                    await context.SaveChangesAsync();
                }
            }
        }
    }
    catch (Exception)
    {
        throw;
    }
}

2. 优化Worker Service中DbContext的注册与使用 #

Worker是单例服务，需通过IServiceScopeFactory创建作用域获取Scoped生命周期的DbContext：

修正Program.cs注册逻辑

using Microsoft.EntityFrameworkCore;
using WorkerService;
using WorkerService.Services;

IHost host = Host.CreateDefaultBuilder(args)
    .ConfigureServices((hostContext, services) =>
    {
        IConfiguration configuration = hostContext.Configuration;
        AppSettings.Configuration = configuration;
        AppSettings.ConnectionString = configuration.GetConnectionString("DefaultConnection");

        services.AddDbContext<DataContext>(options =>
            options.UseSqlServer(AppSettings.ConnectionString));

        services.AddHostedService<Worker>();
    })
    .Build();

await host.RunAsync();

在Worker中通过作用域获取DbContext

public class Worker : BackgroundService
{
    private readonly IServiceScopeFactory _scopeFactory;

    public Worker(IServiceScopeFactory scopeFactory)
    {
        _scopeFactory = scopeFactory;
    }

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            using (var scope = _scopeFactory.CreateScope())
            {
                var ptoService = scope.ServiceProvider.GetRequiredService<YourPTOService>();
                await ptoService.UpdatePTO();
            }

            await Task.Delay(TimeSpan.FromHours(24), stoppingToken);
        }
    }
}

3. 优化查询，减少上下文操作次数 #

通过Include提前加载关联数据，避免循环内重复查询：

var users = await context.Users.Include(u => u.UserPTO).ToListAsync();

// 循环内直接使用关联数据
var userPTO = user.UserPTO;

内容的提问来源于stack exchange，提问作者キムヨンイン