ExecuteNonQuery永远挂在.NET 4.6.1中

Question

当进程在ExecuteNonQuery执行期间冻结时，会出现一种罕见的情况。

cmd.ExecuteNonQuery();

CommandTimeout设置为0(没有超时)，并且正在执行一个简单的UPDATE语句。这通常是快速和问题可以复制平均每周一次。但当它发生时，这是一个完全冻结/挂在过程中。上一次它被复制时，我们使用ProcDump来获取该特定进程的转储文件，并且可以在那里看到这个堆栈跟踪。其他问题之间唯一的区别是前几行(死锁？)

OS Thread Id: 0x4168 (34)
Current frame: ntdll!NtWaitForSingleObject+0xc
ChildEBP RetAddr  Caller, Callee
23eed994 7587f699 KERNELBASE!WaitForSingleObjectEx+0x99, calling ntdll!NtWaitForSingleObject
23eeda08 7587f5f2 KERNELBASE!WaitForSingleObject+0x12, calling KERNELBASE!WaitForSingleObjectEx
23eeda1c 5829e5e2 System_Data!Np::ReadSync+0x205, calling KERNELBASE!WaitForSingleObject
23eeda58 58293812 System_Data!SNIReadSync+0x64
23eeda88 5828a795 System_Data!SNIReadSyncOverAsync+0x25, calling System_Data!SNIReadSync
23eedaa4 53ce748e (MethodDesc 5395aa60 +0x46 DomainBoundILStubClass.IL_STUB_PInvoke(SNI_ConnWrapper*, SNI_Packet**, Int32))
23eedac8 53ce748e (MethodDesc 5395aa60 +0x46 DomainBoundILStubClass.IL_STUB_PInvoke(SNI_ConnWrapper*, SNI_Packet**, Int32))
23eedae4 53cf3c57 (MethodDesc 539498d0 +0x53 SNINativeMethodWrapper.SNIReadSyncOverAsync(System.Runtime.InteropServices.SafeHandle, IntPtr ByRef, Int32)), calling 08a34a78
23eedb04 53e00e5e (MethodDesc 5394a534 +0x5e System.Data.SqlClient.TdsParserStateObject.GetTimeoutRemaining()), calling (MethodDesc 53950710 +0 System.Data.SqlClient.TdsParserStaticMethods.GetTimeoutMilliseconds(Int64))
23eedb18 53e027fb (MethodDesc 5394a90c +0x73 System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()), calling (MethodDesc 539498d0 +0 SNINativeMethodWrapper.SNIReadSyncOverAsync(System.Runtime.InteropServices.SafeHandle, IntPtr ByRef, Int32))
23eedb4c 53e02726 (MethodDesc 5394a8f8 +0x6e System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()), calling (MethodDesc 5394a90c +0 System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync())
23eedb5c 53e0181b (MethodDesc 5394a624 +0x63 System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()), calling (MethodDesc 5394a8f8 +0 System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket())
23eedb68 53e019b3 (MethodDesc 5394a660 +0x2b System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte ByRef)), calling (MethodDesc 5394a624 +0 System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer())
23eedb78 53dedda0 (MethodDesc 5394f064 +0x20c System.Data.SqlClient.TdsParser.TryRun(System.Data.SqlClient.RunBehavior, System.Data.SqlClient.SqlCommand, System.Data.SqlClient.SqlDataReader, System.Data.SqlClient.BulkCopySimpleResultSet, System.Data.SqlClient.TdsParserStateObject, Boolean ByRef)), calling (MethodDesc 5394a660 +0 System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte ByRef))
23eedc0c 53d1d4f0 (MethodDesc 538fe598 +0x7c System.Data.SqlClient.SqlCommand.FinishExecuteReader(System.Data.SqlClient.SqlDataReader, System.Data.SqlClient.RunBehavior, System.String, Boolean, Boolean, Boolean)), calling (MethodDesc 5394f064 +0 System.Data.SqlClient.TdsParser.TryRun(System.Data.SqlClient.RunBehavior, System.Data.SqlClient.SqlCommand, System.Data.SqlClient.SqlDataReader, System.Data.SqlClient.BulkCopySimpleResultSet, System.Data.SqlClient.TdsParserStateObject, Boolean ByRef))
23eedc50 53d1d00b (MethodDesc 538fe56c +0xa2f System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(System.Data.CommandBehavior, System.Data.SqlClient.RunBehavior, Boolean, Boolean, Int32, System.Threading.Tasks.Task ByRef, Boolean, Boolean, System.Data.SqlClient.SqlDataReader, Boolean)), calling (MethodDesc 538fe598 +0 System.Data.SqlClient.SqlCommand.FinishExecuteReader(System.Data.SqlClient.SqlDataReader, System.Data.SqlClient.RunBehavior, System.String, Boolean, Boolean, Boolean))
23eedc68 746b59db clr!JIT_Security_Prolog_Framed+0x15c, calling clr!_EH_epilog3
23eedd18 53d1c27d (MethodDesc 538fe53c +0x605 System.Data.SqlClient.SqlCommand.RunExecuteReader(System.Data.CommandBehavior, System.Data.SqlClient.RunBehavior, Boolean, System.String, System.Threading.Tasks.TaskCompletionSource`1<System.Object>, Int32, System.Threading.Tasks.Task ByRef, Boolean ByRef, Boolean, Boolean)), calling 08a34c1c
23eedd40 72017e17 (MethodDesc 71da0878 +0x37 System.Collections.Hashtable.get_Item(System.Object))
23eedda8 53d16fa3 (MethodDesc 538fe22c +0x187 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(System.Threading.Tasks.TaskCompletionSource`1<System.Object>, System.String, Boolean, Int32, Boolean ByRef, Boolean, Boolean)), calling 08a34b9c
23eede04 53d15f98 (MethodDesc 538fe160 +0xd0 System.Data.SqlClient.SqlCommand.ExecuteNonQuery()), calling 08a38678

我也发现了几个关于这个话题的问题，但是没有一个答案看起来是正确的。

1. 需要帮助从C#诊断Server奇怪的查询超时
2. SqlDataReader挂起GetValue()方法和SNIReadSyncOverAsync
3. ADO.Net SQLCommand.ExecuteReader()减慢或挂起
4. SqlDataReader.GetValue挂起
5. Windbg -在SNIReadSyncOverAsync上冻结了进程，但没有发现死锁
6. https://social.microsoft.com/Forums/en-US/3f62d9ba-87e8-446d-b8ba-a3dfdf78a8e9/no-connection-could-be-made-because-the-target-machine-actively-refused-the-connection?forum=sharepointadmin

Answer 1

我认为在堆栈跟踪的最后一个框架：WaitForSingleObjectEx中清楚地指出了这个问题。

等待，直到指定的对象处于信号状态，I/O完成例程或异步过程调用(APC)排队到线程，或者超时间隔过去。 ..。 如果超时dwMilliseconds为无限，则该函数仅在对象被发送信号或I/O完成例程或APC排队时才返回。 ..。 使用没有超时间隔的等待函数的线程可能导致系统成为死锁的。

所以，是的，我认为这是一个死锁，我建议永远不要使用无限超时；在多个重试循环中使用一个合理的超时值(5应该足够)，总是在尝试之间等待随机的时间。注随机等待对于解决死锁很重要，否则死锁受害者将在相同的时间之后，完全面对循环中的下一项，并且由于操作可能需要相同的时间，它很容易再次成为死锁的受害者。