回滚Npgsql.NpgsqlTransaction失败时PostgreSQL事务状态及处理问询

环境与核心问题 #

使用PostgreSQL服务器版本v12，Npgsql版本7.0.0。当调用Npgsql.NpgsqlTransaction.Rollback()失败时，需要明确：

• PostgreSQL数据库端的事务处于什么状态？
• 后续应如何处理？

故障案例 #

案例1：回滚时协议消息类型异常 #

|2023-01-16 08:51:28.355303 [Ingestion] ERROR [<obfuscated>Apps.<obfuscated>.Ingestion.Core.IngestionBootstrap_23] Run: 
Npgsql.NpgsqlException (0x80004005): Received backend message NoData while expecting CommandCompleteMessage. Please file a bug.
   at Npgsql.Util.Statics.ThrowIfMsgWrongType[T](IBackendMessage msg, NpgsqlConnector connector)
   at Npgsql.Internal.NpgsqlConnector.ExecuteInternalCommand(Byte[] data, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlTransaction.Rollback(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlTransaction.Rollback()
   at Microsoft.EntityFrameworkCore.Storage.RelationalTransaction.Rollback()
   at <obfuscated>.Persistence.Repository.WriteRepository`2.Save(List`1 entities) in D:\repo\ptbuild\PT\Public\Apps\<obfuscated>\<obfuscated>.Persistence.Bulk\Repository\I<obfuscated>WriteRepository.cs:line 64
   at <obfuscated><obfuscated>DivideAndRetrySaveStrategy`1.Save(List`1 entities, Int32 left, Int32 right, Dictionary`2 failed) in D:\repo\ptbuild\PT\Public\Apps\<obfuscated>\<obfuscated>.Ingestion\Ingest\DivideAndRetrySaveStrategy.cs:line 34
   at <obfuscated><obfuscated>DivideAndRetrySaveStrategy`1.Save(List`1 entities) in D:\repo\ptbuild\PT\Public\Apps\<obfuscated>\<obfuscated>.Ingestion\Ingest\DivideAndRetrySaveStrategy.cs:line 29
   at<obfuscated>.OrderIngest.Flush() in D:\repo\ptbuild\PT\Public\Apps\<obfuscated>\<obfuscated>.Ingestion\Ingest\OrderIngest.cs:line 62
   at <obfuscated>Apps.<obfuscated>.Ingestion.Core.<obfuscated>MsgProcessor.FinishLoop() in <obfuscated><obfuscated>MsgProcessor.cs:line 276
   at <obfuscated>Accessor.MonitoringAccessor.MonitoringAccessor.Refresh(IService fromService, DateTime timeStamp)
   at <obfuscated>AccessLayer.Service.Core.DefaultService.Refresh(DateTime timeStamp) in D:\repo\ptbuild\PT\Public\AccessLayer.Service\Core\DefaultService.cs:line 297
   at <obfuscated>Accessor.MonitoringAccessor.Service.MonitoringClientService.Refresh(DateTime now)
   at <obfuscated>Apps.<obfuscated>.Ingestion.Core.IngestionBootstrap.Run() in <obfuscated>IngestionBootstrap.cs:line 137

案例2：连接超时导致事务对象已释放 #

|2023-01-13 14:01:37.154638 [Ingestion] ERROR [Microsoft.EntityFrameworkCore.Database.Command] Failed executing DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TEMP TABLE "#OrdersTemp2811321c" AS TABLE "Orders" WITH NO DATA;
|2023-01-13 14:01:37.163111 [Ingestion] ERROR [Microsoft.EntityFrameworkCore.Database.Transaction] An error occurred using a transaction.
|2023-01-13 14:01:37.163637 [Ingestion] ERROR [<obfuscated>Apps.<obfuscated>.Ingestion.Core.IngestionBootstrap_22] Run: 
System.ObjectDisposedException: NpgsqlTransaction
 ---&gt; Npgsql.NpgsqlException (0x80004005): Exception while reading from stream
 ---&gt; System.TimeoutException: Timeout during reading attempt
   at Npgsql.Internal.NpgsqlReadBuffer.<Ensure>g__EnsureLong|42_0(NpgsqlReadBuffer buffer, Int32 count, Boolean async, Boolean readingNotifications)
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|221_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.ExecuteSqlRaw(DatabaseFacade databaseFacade, String sql, IEnumerable`1 parameters)
   at EFCore.BulkExtensions.SQLAdapters.PostgreSql.PostgreSqlAdapter.MergeAsync[T](DbContext context, Type type, IList`1 entities, TableInfo tableInfo, OperationType operationType, Action`1 progress, Boolean isAsync, CancellationToken cancellationToken)
   at EFCore.BulkExtensions.SQLAdapters.PostgreSql.PostgreSqlAdapter.Merge[T](DbContext context, Type type, IList`1 entities, TableInfo tableInfo, OperationType operationType, Action`1 progress)
   at EFCore.BulkExtensions.DbContextBulkTransaction.Execute[T](DbContext context, Type type, IList`1 entities, OperationType operationType, BulkConfig bulkConfig, Action`1 progress)
   at EFCore.BulkExtensions.DbContextBulkExtensions.BulkInsertOrUpdate[T](DbContext context, IList`1 entities, BulkConfig bulkConfig, Action`1 progress, Type type)
   at <obfuscated>.Persistence.Repository.WriteRepository`2.Save(List`1 entities) in D:\repo\ptbuild\PT\Public\Apps\<obfuscated>\<obfuscated>.Persistence.Bulk\Repository\I<obfuscated>WriteRepository.cs:line 62
   --- End of inner exception stack trace ---
   at Npgsql.NpgsqlTransaction.CheckDisposed()
   at Npgsql.NpgsqlTransaction.CheckReady()
   at Npgsql.NpgsqlTransaction.Rollback(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlTransaction.Rollback()
   at Microsoft.EntityFrameworkCore.Storage.RelationalTransaction.Rollback()
   at <obfuscated>.Persistence.Repository.WriteRepository`2.Save(List`1 entities) in D:\repo\ptbuild\PT\Public\Apps\<obfuscated>\<obfuscated>.Persistence.Bulk\Repository\I<obfuscated>WriteRepository.cs:line 62
   at <obfuscated><obfuscated>DivideAndRetrySaveStrategy`1.Save(List`1 entities, Int32 left, Int32 right, Dictionary`2 failed) in D:\repo\ptbuild\PT\Public\Apps\<obfuscated>\<obfuscated>.Ingestion\Ingest\DivideAndRetrySaveStrategy.cs:line 34
   at <obfuscated><obfuscated>DivideAndRetrySaveStrategy`1.Save(List`1 entities) in D:\repo\ptbuild\PT\Public\Apps\<obfuscated>\<obfuscated>.Ingestion\Ingest\DivideAndRetrySaveStrategy.cs:line 29
   at<obfuscated>.OrderIngest.Flush() in D:\repo\ptbuild\PT\Public\Apps\<obfuscated>\<obfuscated>.Ingestion\Ingest\OrderIngest.cs:line 60
   at <obfuscated>Apps.<obfuscated>.Ingestion.Core.<obfuscated>MsgProcessor.FinishLoop() in <obfuscated><obfuscated>MsgProcessor.cs:line 276
   at <obfuscated>Accessor.MonitoringAccessor.MonitoringAccessor.Refresh(IService fromService, DateTime timeStamp)
   at <obfuscated>AccessLayer.Service.Core.DefaultService.Refresh(DateTime timeStamp) in D:\repo\ptbuild\PT\Public\AccessLayer.Service\Core\DefaultService.cs:line 297
   at <obfuscated>Accessor.MonitoringAccessor.Service.MonitoringClientService.Refresh(DateTime now)
   at <obfuscated>Apps.<obfuscated>.Ingestion.Core.IngestionBootstrap.Run() in <obfuscated>IngestionBootstrap.cs:line 136

事务状态分析 #

案例1：协议消息异常 #

该错误属于Npgsql客户端与PostgreSQL后端的协议交互bug：客户端发送回滚命令后，收到NoData消息而非预期的CommandCompleteMessage。

• 数据库端大概率已完成回滚：回滚是无结果返回的命令，NoData消息本身符合回滚执行后的返回逻辑，只是Npgsql的协议处理逻辑误判了消息类型。
• 极小概率存在异常：若网络波动导致回滚命令未被后端接收，事务可能仍处于活跃状态。

案例2：连接超时+事务已释放 #

错误根源是数据库连接读取超时，导致Npgsql连接被销毁，关联的事务对象也被释放。

• 数据库端事务状态不确定：PostgreSQL会在连接断开时自动回滚未提交的事务，但如果超时发生在回滚命令发送后，事务可能已完成回滚；若超时发生在业务命令执行阶段，事务会被后端自动终止回滚。
• 客户端已无法通过原事务对象操作数据库。

处理方案 #

1. 立即丢弃当前连接：无论哪种情况，回滚失败后当前连接状态已不可靠，必须关闭并丢弃该连接，避免后续操作引发更多异常。
2. 禁止重复回滚：不要尝试再次调用Rollback()，尤其是案例2中事务对象已被释放，重复调用会抛出更多异常。
3. 验证数据一致性：重新建立连接后，查询相关业务数据、临时表等，确认之前的操作是否已被回滚。
4. 修复客户端bug（案例1）：升级Npgsql到最新稳定版本（7.0.x后续补丁或更高版本），修复协议交互的逻辑问题。
5. 优化连接配置（案例2）：调整Npgsql的连接超时、命令超时参数，提升网络稳定性，避免因超时导致连接异常。
6. 业务重试：若业务允许，在确认事务已回滚后，重新执行原本的业务操作。

内容的提问来源于stack exchange，提问作者yi.han