生产环境中无法解释的SQL错误-可能与网络有关 [英] Unexplained SQL errors in production environment - possibly network related
问题描述
我正在做一些相当密集的数据库工作，最终将许多记录插入数据库中。为了最大程度地减少上下文膨胀，我一次执行了这些插入100次，处理了上下文，然后重新创建了上下文。
I am doing some fairly intensive database work, and end up inserting many, many records into the database. In an effort to minimize context bloat, I do these inserts 100 at a time, dispose of the context, and recreate the context.
我遇到了一些奇怪的错误，不明白这些错误仅在我们的生产服务器上发生，而在开发服务器上都很好。如果有人能对他们认为可能的问题有所了解，我将不胜感激。
I am getting some odd errors that I don't understand. These errors only occur on our production server, but all is well on the development server. If anyone could shed some light on what they think might be the problem, I would sure appreciate it.
System.Data.Entity.Core .EntityCommandExecutionException：执行命令定义时发生错误。有关详细信息，请参见内部异常。
System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.
System.Data.SqlClient.SqlException：从服务器接收结果时发生传输级错误。 （提供者：TCP提供程序，错误：0-信号灯超时时间已到期。）
System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)
System.ComponentModel.Win32Exception：信号灯超时时间已过期
System.ComponentModel.Win32Exception: The semaphore timeout period has expired
System.Data.SqlClient.SqlException（0x80131904）：从服务器接收结果时发生传输级错误。 （提供者：TCP提供程序，错误：0-指定的网络名称不再可用。）
System.Data.SqlClient.SqlException (0x80131904): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
System.ComponentModel.Win32Exception（0x80004005）：指定的网络名称为no更长的可用时间
System.ComponentModel.Win32Exception (0x80004005): The specified network name is no longer available
System.Data.Entity.Infrastructure.CommitFailedException：提交数据库事务时报告了一个错误，但无法确定该事务是成功还是失败数据库服务器。请参阅内部异常和 http://go.microsoft.com/fwlink/?LinkId=313468 获取更多信息。 >
System.Data.SqlClient.SqlException：从服务器接收结果时发生传输级错误。 （提供者：TCP提供程序，错误：0-指定的网络名称不再可用。）
System.Data.Entity.Infrastructure.CommitFailedException: An error was reported while committing a database transaction but it could not be determined whether the transaction succeeded or failed on the database server. See the inner exception and http://go.microsoft.com/fwlink/?LinkId=313468 for more information. > System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
System.ComponentModel.Win32Exception：指定的网络名称不再可用`
System.ComponentModel.Win32Exception: The specified network name is no longer available`
System.Data.Entity.Core.EntityException：引发了一个异常，该异常很可能是由于瞬时故障引起的。如果要连接到SQL Azure数据库，请考虑使用SqlAzureExecutionStrategy。
System.Data.Entity.Core.EntityException: An exception has been raised that is likely due to a transient failure. If you are connecting to a SQL Azure database consider using SqlAzureExecutionStrategy.
System.Data.Entity.Core.EntityCommandExecutionException：执行命令定义时发生错误。有关详细信息，请参见内部异常。
System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.
System.Data.SqlClient.SqlException：将请求发送到服务器时发生了传输级错误。 （提供者：TCP Provider，错误：0-远程主机强制关闭了现有连接。）-
System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) -
System.ComponentModel.Win32Exception：现有连接被强制关闭由远程主机
System.ComponentModel.Win32Exception: An existing connection was forcibly closed by the remote host
这些错误在过程中何时发生是完全随机的（我有一个计数器告诉我在哪里） 。查找这些错误，似乎它们是网络错误。我无权访问数据库服务器日志，因此无法查看。我的网络服务器日志无法显示正在发生的一切。任何帮助都太棒了。
These errors are completely random as to when they occur in the process (I have a counter that tells me where). Looking these errors up, it appears as if they are network errors. I don't have access to my database server logs, so I can't look there. My webserver logs don't shine any light on what is happening. Any help would be fantastic.
编辑：
我没有运行Azure 。
I am not running Azure.
我也遇到了很多主键冲突错误：
I am also getting plenty of primary key violation errors too:
系统.Data.Entity.Infrastructure.DbUpdateException：更新条目时发生错误。有关详细信息，请参见内部异常。
System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
System.Data.Entity.Core.UpdateException：更新条目时发生错误。有关详细信息，请参见内部异常。
System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details.
System.Data.SqlClient.SqlException：违反主键约束PK_dbo.MissileDataReferences。无法在对象dbo.MissileDataReferences中插入重复密钥。重复的键值为（4277，2，448388）。
System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_dbo.MissileDataReferences'. Cannot insert duplicate key in object 'dbo.MissileDataReferences'. The duplicate key value is (4277, 2, 448388).
推荐答案
他们？是的，您可以在任何大容量站点中获得这些信息。
Annoying aren't they? Yes, you'll get these in any high volume site.
这似乎就是为什么甚至Microsoft在SQL Azure中引入了Azure重试策略的原因。您希望甚至他们都可以保证其网站与同一网络上同一数据中心中的数据库之间的连接。但是他们不能。
This seems to be why even Microsoft introduced the Azure retry policy in SQL Azure. You'd hope that even they could guarantee a connection between their web site and and their database in the same data centre on the same network. But they can't.
您可以为Azure打开此功能（您不说是否在使用Azure，但我怀疑不是）。请参阅 https://docs.microsoft.com / en-us / azure / sql-database / sql-database-connectivity-issues 更改连接字符串。
You can switch this on for Azure (you don't say if you are using Azure but I suspect not). See https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connectivity-issues for the connection string changes required.
https://msdn.microsoft.com/zh-CN/library/dn456835 （v = vs.113）.aspx 也涵盖了Azure上的EF6。
https://msdn.microsoft.com/en-us/library/dn456835(v=vs.113).aspx also covers EF6 on Azure.
也许这不是您想要的答案，但我认为您应该看看使用 Polly库，因为它使您可以为每个命令显式设置重试逻辑。
Perhaps it is not the answer you want, but I think you should look at using the Polly library as it enables you to set retry logic explicitly for each command.
您为什么不希望为每个调用都这样做（以节省每个命令的编码重试次数）？好吧，过去我遇到了很多问题，因为我们尝试了插入等的逻辑，但稍后又重复了一次（导致主键冲突），因为响应没有返回到客户端。
Why wouldn't you want to do this for every call (to save coding up retry per command)? Well, I've had quite a few problems in the past where we've retried logic that inserts etc. and gets repeated again a moment later (resulting in a primary key violation) because the response didn't get back to the client.
因此仅对安全的只读调用这样做，如果您必须对写入进行重试，请放置处理程序以检测重复的插入，然后使用服务器并询问用户他们想做什么。
So only do it for safe 'read only' calls and if you must do a retry on writes, put handlers in place to detect dupe inserts, then double check with the server and ask the user what they want to do.
这篇关于生产环境中无法解释的SQL错误-可能与网络有关的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
