实体框架核心：具有“执行超时过期”的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()

这是使用.NET 4.8与实体框架Core 3.1.5使用。

在此项目中，我们还使用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秒左右之后。

知道什么可能导致这一点，或者我如何调试它？

我怀疑它实际上与超时无关，而是有某种僵局左右导致交易被取消。

该服务器正在运行SQL 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解决了问题。