7

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: An error occurred while executing the command definition. See the inner exception for details.

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: The semaphore timeout period has expired

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): The specified network name is no longer available

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: The specified network name is no longer available`

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: An error occurred while executing the command definition. See the inner exception for details.

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: 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.

EDIT:

I am not running Azure.

I am also getting plenty of primary key violation errors too:

System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.

System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details.

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).

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
FlipperBizkut
  • 423
  • 1
  • 5
  • 15

1 Answers1

4

Annoying aren't they? Yes, you'll get these in any high volume site.

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.

You can switch this on for Azure (you don't say if you are using Azure but I suspect not). See https://learn.microsoft.com/en-us/azure/sql-database/sql-database-connectivity-issues for the connection string changes required.

https://msdn.microsoft.com/en-us/library/dn456835(v=vs.113).aspx also covers EF6 on Azure.

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.

Ryan O'Neill
  • 5,410
  • 4
  • 46
  • 69
  • I have edited my original post with further info. I am currently not doing any retries at all (that I know of). Unless Entity Framework has retries baked in (which I don't think it does), I should never see primary key violations (but I am getting them all the time - most common error I get). – FlipperBizkut Jan 29 '17 at 23:45
  • You don't need a retry logic to be implemented to get duplicate key errors. If you commit big objects with multiple relationships in a concurrent fashion for example. I would dig down in the code to see exactly where the duplicate key errors occurs and try to figure out a way to limit them. Not much you can do for the network errors apart from a retry policy. – Etienne Jan 30 '17 at 00:19