SqlConnections, Parallel.For, old C # application and occasional freezes when initializing SqlConnections

Question

my problem: earlier this week I had a task to speed up the task in our program. I looked at it and immediately understood the idea of ​​using a parallel foreach loop for a function in this task.

I implemented it, went through a function (including all subfunctions) and modified SqlConnections (and other things) so that it could work in parallel. I started it all, and everything went well and quickly (alone it reduced the time for this task by ~ 45%)

Now we wanted to try the same thing with some additional data and ... I had some strange problem: whenever a parallel function was called, it worked ... but sometimes one of the threads hung for at least 4 minutes (time Outs are set for one minute, for the connection command I).

If I pause the program during this, I see that only one thread is still active from this loop, and it hangs on

connection.Open() 

After ~ 4 minutes, the program simply goes without error (except for the message in the "Output" field, saying that an exception occurred somewhere, but it was not caught by my application, but somewhere in the SqlConnection / SqlCommand object).

I can kill all connections on MSSQLServer if nothing happens, also MSSQLServer does nothing in these 4 minutes, all connections are idle.

This is the procedure that is used to send update / insert / delete statements to the database:

 int i = 80; bool itDidntWork = true; Random random = new Random(); while (itDidntWork && i > 0) { try { using (SqlConnection connection = new SqlConnection(sqlConnectionString)) { connection.Open(); lock (connection) { command.Connection = connection; command.ExecuteNonQuery(); } itDidntWork = false; } } catch (Exception ex) { if (ex is SqlException && ((SqlException)ex).ErrorCode == -2146232060) { Thread.Sleep(random.Next(500, 5000)); } else { SqlConnection.ClearAllPools(); } Thread.Sleep(random.Next(50, 110)); i--; if (i == 0) { writeError(ex); } } } 

just in case: in small databases, deadlocks may occur (error number 2146232060), so if this happens, I have to make counter-statements at different times. Works great even on small databases / small servers. If the error was not caused by a dead end, most likely the connection was faulty, so I clear all the failed connections.

There are powerful functions for executing scalars, populating data / datasets (yes, the application is old) and executing stored procedures.

And yes, they are all used in a parallel loop.

Does anyone know what could be there? Or the idea of ​​how I can find out what's going on there?

* edit the command object:

it is assigned to a function, the command object is always a new object when it is specified in a function.

about blocking: if I postpone the blocking, I get dozens and hundreds of “closed connections” or “the connection is already open” because the Open () function just gets the connection from the .NET connection pool. The lock works as intended.

Code example:

 using(SqlCommand deleteCommand = new SqlCommand(sqlStatement)) { ExecuteNonQuerySafely(deleteCommand); // that the function that contains the body I posted above } 

* edit 2

I have to make a fix: it hangs on this

 command.Connection = connection; 

At least I assume this is happening because when I pause the application, the “step” label is green and on

 command.ExecuteNonQuery(); 

saying that this is an operator that will be executed as follows.

* edit 3 just to make sure that I just started a new test without any locks around the connection object ... it will take several minutes to get the results.

* change 4 well, I was wrong. I deleted the lock statements and ... it still worked. Perhaps the first time I tried, there was a repeated connection or something like that. Thanks for pointing this out.

* change 5 I get the feeling that this happens only with one specific call to a specific database procedure. I do not know why. C # wise there is no difference between this call and other calls, see Edit 6. And since he was not executing the instruction at that moment (I think maybe someone can fix me about this. If in debug mode, the line green is marked (instead of yellow), it has not yet executed this statement, but is waiting for expression before this line ends, is that right?) This is strange.

* edit 6 There were three command objects that were reused all the time. They were defined above the parallel function. I do not know how bad this is. They were used only to call one stored procedure (each of them was called a different procedure), of course, with different parameters and a new connection (using the aforementioned method).

* edit 7 is normal, this is only valid when invoking one specific stored procedure. Except that when assigning a connection object it hangs (the next line is marked in green). Trying to find out what is the reason for this.

* change 8 yay, this happened only on another command. So here it is.

* change 9 OK. The problem is solved. Hangs were actually CommandTimeouts that were set for 10 minutes (!). They were installed for only two teams (the one that I mentioned in edition 7 and the one that I mentioned in edition 8). Since I found both of them while I was rebuilding my commands to make them look like devundef, I marked his answer as the one that solved my problem. In addition, his proposal to limit the number of threads used by my loop accelerated the process even further.

Special thanks to Mark Gravel for explaining things and hanging himself here with me on Saturday;)

Answer 1

I think the problem can be found in your edit 6: edit 6 There were 3 command objects that were reused all the time.

Any data used inside the parallel must be created inside the loop or must have the proper synchronization code to protect it for use by two or more threads at the same time. I do not see this code inside ExecuteNonQuerySafely . Connection blocking is not affected, since the connection object is created inside the method. Locking this command does not guarantee thread safety, because perhaps you set the command parameters before locking it inside the method. A lock(command) will work if you lock the command before calling ExecuteNonQuerySafely , but locking inside the parallel loop is not a good thing, better avoid this and create a new command for each iteration. Better yet, do a little refactory on ExecuteNonQuerySafely so that it ExecuteNonQuerySafely callback action instead of SqlCommand. Example:

 public void ExecuteCommandSafely(Action<SqlCommand> callback) { ... do init stuff ... using (var connection = new SqlConnection(...)) { using (var command = new SqlCommand() { command.Connection = connection; try{ callback(command); } ... error handling stuff ... } } } 

And use:

 ExecuteCommandSafely((command) => { command.CommandText = "..."; ... set parameters .. command.ExecuteNonQuery(); }); 

Finally, the fact that you get errors executing parallel commands is a sign that parallel execution is not very good in this case. You are wasting server resources (sql / web ...) to get errors, and connections are expensive. Try using the MaxDegreeOfParalellism option to adjust the workload for this particular cycle (remember that the optimal value will change depending on the equipment / server / network / etc.). The Parallel.ForEach method has an overload that takes ParallelOptions parameters, where you can configure how many threads you want to execute (http://msdn.microsoft.com/en-us/library/system.threading.tasks. Paralleloptions.maxdegreeofparallelism.aspx )