实体框架核心:尽管设置了更高的超时，但执行超时已过期的SqlException

Question

我的代码非常简单，如下所示：

private void Insert(IList<T> data)
{
    using (MyDbContext dbContext = MyDbContextFactory.CreateDbContext())
    {
        dbContext.AddRange(data);
        dbContext.SaveChanges();
    }
}

这主要是可行的，但有时(虽然我只是尝试插入50行.即使它们可能包含稍微大一点的气泡)，它也会在这样的堆栈跟踪中失败：

Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) in E:\SqlClientInternal\agent-1\_work\5\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnection.cs:line 820  
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) in E:\SqlClientInternal\agent-1\_work\5\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\TdsParser.cs:line 1572  
at Microsoft.Data.SqlClient.TdsParserStateObject.ThrowExceptionAndWarning(Boolean callerHasConnectionLock, Boolean asyncClose) in E:\SqlClientInternal\agent-1\_work\5\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\TdsParserStateObject.cs:line 1070  
at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) in E:\SqlClientInternal\agent-1\_work\5\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\TdsParserStateObject.cs:line 2744  
at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync() in E:\SqlClientInternal\agent-1\_work\5\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\TdsParserStateObject.cs:line 2258  
at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket() in E:\SqlClientInternal\agent-1\_work\5\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\TdsParserStateObject.cs:line 2182  
at Microsoft.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer() in E:\SqlClientInternal\agent-1\_work\5\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\TdsParserStateObject.cs:line 1222  
at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value) in E:\SqlClientInternal\agent-1\_work\5\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\TdsParserStateObject.cs:line 1450  
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) in E:\SqlClientInternal\agent-1\_work\5\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\TdsParser.cs:line 2190  
at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) in E:\SqlClientInternal\agent-1\_work\5\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\TdsParser.cs:line 2088  
at Microsoft.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest) in E:\SqlClientInternal\agent-1\_work\5\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\TdsParser.cs:line 9384  
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest) in E:\SqlClientInternal\agent-1\_work\5\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:line 1311  
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest) in E:\SqlClientInternal\agent-1\_work\5\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:line 1091  
at Microsoft.Data.SqlClient.SqlInternalTransaction.Commit() in E:\SqlClientInternal\agent-1\_work\5\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\sqlinternaltransaction.cs:line 344  
at Microsoft.Data.SqlClient.SqlTransaction.Commit() in E:\SqlClientInternal\agent-1\_work\5\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlTransaction.cs:line 202  
at Microsoft.EntityFrameworkCore.Storage.RelationalTransaction.Commit()  
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)  
at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList`1 entries)  
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1 entriesToSave)  
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(DbContext _, Boolean acceptAllChangesOnSuccess)  
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)  
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)  
at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)  
at Microsoft.EntityFrameworkCore.DbContext.SaveChanges()

这是使用带有实体框架核心3.1.5的.NET 4.8。

我们在这个项目中也使用了EFCore.BulkExtensions 3.1.1，但是代码似乎运行得很好。

为了解决这个问题，我增加了超时，如下所示：

static class MyDbContextFactory
{
    public static MyDbContext CreateDbContext()
    {
        Action<SqlServerDbContextOptionsBuilder> setDbCommandTimeout = null;
        int? dbCommandTimeout = GlobalConfiguration.DbCommandTimeout;

        if (dbCommandTimeout.HasValue)
        {
            setDbCommandTimeout = (opts => opts.CommandTimeout(dbCommandTimeout.Value));
        }

        DbContextOptions<MyDbContext> options = new DbContextOptionsBuilder<MyDbContext>()
                                                            .UseSqlServer(GlobalConfiguration.GetConnectionString(), setDbCommandTimeout)
                                                            .Options;

        return new MyDbContext(options);
    }
}

还包括：

sealed class MyDbContext : DbContext
{
    public MyDbContext(DbContextOptions<SharePointExportContext> options) : base(options)
    {
    }

    // [...]
}

即使将超时配置为1200 (20分钟)，它仍然会失败，有时只需90秒左右。

你知道是什么导致了这件事吗?或者我如何调试它？

我怀疑它实际上与超时无关，而是存在某种类型的死锁，从而导致事务被取消。

服务器正在运行server 2016 (v13.0.4001.0)。

我甚至尝试删除和重新创建数据库，但没有效果。

Answer 1

我不知道我正在运行Microsoft.Data.SqlClient (1.1.1)和Microsoft.Data.SqlClient.SNI (1.1.0)的旧版本。

将Microsoft.Data.SqlClient升级到4.1.0，将Microsoft.Data.SqlClient.SNI升级到4.0.0解决了这个问题。