.NET应用用EF Core写PostgreSQL遇Winsock 10054错误致程序挂起求助

问题背景 #

开发的.NET应用使用Entity Framework Core将数据写入PostgreSQL v12数据库，应用会监控文件系统，发现变化时执行多表插入与更新操作。多数情况下运行正常，但程序频繁出现挂起无响应现象，且待写入数据量越大，问题出现越频繁。重启应用后问题有时解决，有时依旧，挂起均发生在调用DbContext.SaveChanges方法时。

PostgreSQL日志信息 #

LOG: could not receive data from client: unrecognized winsock error 10054
LOG: incomplete message from client（德语翻译）
LOG: could not receive data from client: unrecognized winsock error 10054
LOG: incomplete message from client

挂起时堆栈跟踪 #

[Übergang von Verwaltet zu Nativ]       Annotated Frame
System.Net.Sockets.dll!System.Net.Sockets.SocketPal.Receive(System.Net.Sockets.SafeSocketHandle handle, System.Span<byte> buffer, System.Net.Sockets.SocketFlags socketFlags, out int bytesTransferred) Unknown Non-user code. Skipped loading symbols.
 System.Net.Sockets.dll!System.Net.Sockets.Socket.Receive(byte[] buffer, int offset, int size, System.Net.Sockets.SocketFlags socketFlags, out System.Net.Sockets.SocketError errorCode)    Unknown Non-user code. Skipped loading symbols.
 System.Net.Sockets.dll!System.Net.Sockets.NetworkStream.Read(byte[] buffer, int offset, int size)  Unknown Non-user code. Skipped loading symbols.
    Npgsql.dll!Npgsql.NpgsqlReadBuffer.Ensure.__EnsureLong|40_0(Npgsql.NpgsqlReadBuffer buffer, int count, bool async, bool readingNotifications)   Unknown Non-user code. Skipped loading symbols.
    Npgsql.dll!Npgsql.NpgsqlConnector.ReadMessage.__ReadMessageLong|194_0(Npgsql.NpgsqlConnector connector, bool async, Npgsql.DataRowLoadingMode dataRowLoadingMode, bool readingNotifications, bool isReadingPrependedMessage)    Unknown Non-user code. Skipped loading symbols.
    Npgsql.dll!Npgsql.NpgsqlConnector.ReadMessage(bool async, Npgsql.DataRowLoadingMode dataRowLoadingMode, bool readingNotifications)  Unknown Non-user code. Skipped loading symbols.
    Npgsql.dll!Npgsql.NpgsqlDataReader.NextResult(bool async, bool isConsuming, System.Threading.CancellationToken cancellationToken)   Unknown Non-user code. Skipped loading symbols.
    Npgsql.dll!Npgsql.NpgsqlDataReader.NextResult() Unknown Non-user code. Skipped loading symbols.
    Npgsql.dll!Npgsql.NpgsqlCommand.ExecuteReader(System.Data.CommandBehavior behavior, bool async, System.Threading.CancellationToken cancellationToken)   Unknown Non-user code. Skipped loading symbols.
    Npgsql.dll!Npgsql.NpgsqlCommand.ExecuteReader(System.Data.CommandBehavior behavior) Unknown Non-user code. Skipped loading symbols.
    Npgsql.dll!Npgsql.NpgsqlCommand.ExecuteDbDataReader(System.Data.CommandBehavior behavior)   Unknown Non-user code. Skipped loading symbols.
    System.Data.Common.dll!System.Data.Common.DbCommand.ExecuteReader() Unknown Non-user code. Skipped loading symbols.
    Microsoft.EntityFrameworkCore.Relational.dll!Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(Microsoft.EntityFrameworkCore.Storage.RelationalCommandParameterObject parameterObject)  Unknown Non-user code. Skipped loading symbols.
    Microsoft.EntityFrameworkCore.Relational.dll!Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(Microsoft.EntityFrameworkCore.Storage.IRelationalConnection connection)    Unknown Non-user code. Skipped loading symbols.
    Microsoft.EntityFrameworkCore.Relational.dll!Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(System.Collections.Generic.IEnumerable<Microsoft.EntityFrameworkCore.Update.ModificationCommandBatch> commandBatches, Microsoft.EntityFrameworkCore.Storage.IRelationalConnection connection)  Unknown Non-user code. Skipped loading symbols.
    Microsoft.EntityFrameworkCore.Relational.dll!Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(System.Collections.Generic.IList<Microsoft.EntityFrameworkCore.Update.IUpdateEntry> entries)  Unknown Non-user code. Skipped loading symbols.
    Microsoft.EntityFrameworkCore.dll!Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(System.Collections.Generic.IList<Microsoft.EntityFrameworkCore.Update.IUpdateEntry> entriesToSave) Unknown Non-user code. Skipped loading symbols.
    Microsoft.EntityFrameworkCore.dll!Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Microsoft.EntityFrameworkCore.DbContext _, bool acceptAllChangesOnSuccess) Unknown Non-user code. Skipped loading symbols.
    Npgsql.EntityFrameworkCore.PostgreSQL.dll!Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute<bool, int>(bool state, System.Func<Microsoft.EntityFrameworkCore.DbContext, bool, int> operation, System.Func<Microsoft.EntityFrameworkCore.DbContext, bool, Microsoft.EntityFrameworkCore.Storage.ExecutionResult<int>> verifySucceeded)  Unknown Non-user code. Skipped loading symbols.
    Microsoft.EntityFrameworkCore.dll!Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(bool acceptAllChangesOnSuccess)    Unknown Non-user code. Skipped loading symbols.
    Microsoft.EntityFrameworkCore.dll!Microsoft.EntityFrameworkCore.DbContext.SaveChanges(bool acceptAllChangesOnSuccess)   Unknown Non-user code. Skipped loading symbols.
    Microsoft.EntityFrameworkCore.dll!Microsoft.EntityFrameworkCore.DbContext.SaveChanges() Unknown Non-user code. Skipped loading symbols.
>   SPCDataWriterWorker.dll!Promess.MeasurementResults.SPCDataWriterWorker.Globals.SaveDBChangesWithEvent(Promess.MeasurementResults.Model.ResultContext db, string sTablename) Line 56 C#  Symbols loaded.

引用包版本 #

• EF Core 5.0.7
• NodaTime 3.0.3
• Npgsql 5.0.6

相关代码 #

internal static void SaveDBChangesWithEvent(ResultContext db, string sTablename)
{
    if (!db.ChangeTracker.AutoDetectChangesEnabled)
    {
        db.ChangeTracker.DetectChanges();
    }

    int iSavedRowCount = db.SaveChanges();
    Debug.Trace($"{iSavedRowCount} geänderte Datensätze in {sTablename}");
    Globals.FireProcessFileStep(string.Format(
        Texts.ProcessStepWriteDBChangeCount, iSavedRowCount, sTablename), Globals.EventsSeverity.Debug);
}

排查方案与参数调整建议 #

1. 检查DbContext生命周期与并发安全 #

• DbContext并非线程安全，禁止多线程同时操作同一个实例，确保每个数据操作使用短生命周期的DbContext实例（比如每次写入操作创建新实例）。
• 若复用DbContext，需确保在每次批量操作后清理变更跟踪器（db.ChangeTracker.Clear()），避免内存堆积和状态混乱。

2. 优化批量写入逻辑 #

• 拆分大数据量为多个小批次提交，比如每次SaveChanges处理100-500条数据，降低单次操作的资源压力。
• 批量操作前关闭自动变更检测（db.ChangeTracker.AutoDetectChangesEnabled = false），手动控制实体状态，减少不必要的性能开销。

3. 调整PostgreSQL连接参数 #

可在连接字符串中添加以下参数优化稳定性：

• CommandTimeout=120：延长命令超时时间，适配大数据量写入的耗时需求。
• MaxPoolSize=200：增加连接池大小，避免高并发场景下连接池耗尽导致等待。
• KeepAlive=30：设置TCP保持连接时间，防止连接被系统防火墙或网络组件断开。
• NoResetOnClose=true：减少连接池重置开销，需确保连接状态干净无残留。

4. 升级驱动版本 #

当前使用的Npgsql 5.0.6和EF Core 5.0.7存在已知的连接稳定性bug，建议升级到对应大版本的最新补丁：

• Npgsql升级至5.0.x系列最新版（如5.0.17）
• EF Core升级至5.0.x系列最新版（如5.0.17）

5. 系统与数据库配置排查 #

• 执行netsh winsock reset重置Winsock目录，排除底层网络组件异常。
• 检查系统资源：挂起时监控CPU、内存、磁盘IO使用率，确认是否因资源耗尽导致阻塞。
• 调整PostgreSQL配置文件postgresql.conf：
  • 提高max_connections值，确保数据库能处理足够连接。
  • 增大work_mem和maintenance_work_mem，优化大数据量操作的内存分配。

6. 添加异常捕获与重试机制 #

在写入方法中针对连接异常添加捕获和重试逻辑，同时配置EF Core的执行策略：

internal static void SaveDBChangesWithEvent(ResultContext db, string sTablename)
{
    if (!db.ChangeTracker.AutoDetectChangesEnabled)
    {
        db.ChangeTracker.DetectChanges();
    }

    try
    {
        int iSavedRowCount = db.SaveChanges();
        Debug.Trace($"{iSavedRowCount} geänderte Datensätze in {sTablename}");
        Globals.FireProcessFileStep(string.Format(
            Texts.ProcessStepWriteDBChangeCount, iSavedRowCount, sTablename), Globals.EventsSeverity.Debug);
    }
    catch (NpgsqlException ex) when (ex.Message.Contains("10054") || ex.InnerException is SocketException)
    {
        Debug.Trace($"写入失败：{ex.Message}");
        // 可添加重试逻辑，或抛出自定义异常让上层处理
        throw;
    }
}

配置EF Core重试策略：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseNpgsql("你的连接字符串")
                  .EnableRetryOnFailure(
                      maxRetryCount: 3,
                      maxRetryDelay: TimeSpan.FromSeconds(5),
                      errorNumbersToAdd: new[] { -2 }); // 对应Socket连接错误的Npgsql错误码
}

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