Parallel.Foreach loop creating multiple db connections throws connection errors?

Question

The 'login failed' error has never resurfaced, curious? However the other connection errors seem to be explained by slamming the server. The randomness with which the errors occur, still a mystery.

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication

I wanted to get my code running in parallel, so I changed my foreach loop to a parallel foreach loop. It seemed simple enough. Each loop connects to the database, looks up some stuff, performs some logic, adds some stuff, closes the connection. But I get the above error?

I'm using my local sql server and entity framework (each loop uses it's own context). Is there some problem with connecting multiple times using the same local login or something? How did I get around this?

I have (before trying to covert to a parallel.foreach loop) split my list of objects that I am foreach looping through into four groups (separate csv files) and run four concurrent instances of my program (which ran faster overall than just one, thus the idea for parallel). So it seems connecting to the db shouldn't be a problem?

Any ideas?

EDIT: Here's before

var gtgGenerator = new CustomGtgGenerator();
var connectionString = ConfigurationManager.ConnectionStrings["BioEntities"].ConnectionString;

var allAccessionsFromObs = _GetAccessionListFromDataFiles(collectionId);

ForEach(cloneIdAndAccessions in allAccessionsFromObs)
    DoWork(gtgGenerator, taxonId, organismId, cloneIdAndAccessions, connectionString));

after

var gtgGenerator = new CustomGtgGenerator();
var connectionString = ConfigurationManager.ConnectionStrings["BioEntities"].ConnectionString;

var allAccessionsFromObs = _GetAccessionListFromDataFiles(collectionId);

Parallel.ForEach(allAccessionsFromObs, cloneIdAndAccessions => DoWork(gtgGenerator, taxonId, organismId, cloneIdAndAccessions, connectionString));

Inside the DoWork I use the BioEntities

using (var bioEntities = new BioEntities(connectionString)) {...}

It gets curiouser and curiouser...

I added some code in my DoWork method:

Debug.WriteLine(“Executing “ + itemName + ” as “ + WindowsIdentity.GetCurrent().Name);

and it mysteriously started working (quite well/fast actually). But eventually I got the same excepetion (after about an hour). But I was able to trace this..?

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

EDIT2: Hmm.. I came in to work and fired it up this morning (obsessed with understanding why it would work for a little bit) and it is cruising along working fine (and fast!) so far. Occasional : The thread '' (0x27c8) has exited with code 0 (0x0). The thread '' (0x26b8) has exited with code 0 (0x0).

But other than that it hasn't messed up yet? Which, frankly, just worries me more. This is a one-off thing so I may use it (if it works), but what is going on? Could it be NLOG, logging in the multiple threads clogs stuff up and causes timeouts in the sql? I really don't get it. Once it stops working again, I'll try to figure things out. Is there a way to reset the sql server, I'm not sure why it would work for an hour, then mess up, then never work for more than a couple seconds, and now (after a reboot) work (for about 30 minutes so far)?

Answer 1

Your identity isn't flowing to the worker thread executing the underlying tasks of your parallel for.each.

The answer here is a good one, so I won't duplicate it. TPL Task in WCF service fails to use correct IIS security Credentials (SQL Connection)

Answer 2

Okay sorry for answering my own question, but it might be valuable to someone, somewhere, sometime.

Upon adding (and subsequently removing) the following code in order to see what identity each thread was using, the 'login failed' error never occurred again. I'm not sure if using it affected something, but I don't use it now and it never seems to have the identity problem discussed in the links and answers that were initially given.

Debug.WriteLine("Executing " + cloneIdAndAccessions.Item1.ToString() + " as " + WindowsIdentity.GetCurrent().Name);

There must have been two problems going on here, and the first was masking the latter. But I was still getting the following two types of connection errors. Particularly (I noticed) after opening SQL Server Management Studio and perhaps straining the server.

System.Data.EntityCommandExecutionException: An error occurred while reading from the store provider's data reader. See the inner exception for details. ---> System.Data.SqlClient.SqlException: The query processor could not start the necessary thread resources for parallel query execution.

And...

System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

So, my solution (since I could not find rhyme nor reason to when the errors would arise) was to simply catch the errors and record which object in the Parallel.ForEach loop threw it, then rerun those objects. Still MUCH faster than the simple foreach loop.

EDIT: Well, this is interesting, I found some rhyme/reason to the errors (not when they are thrown but maybe why). If I chunk the number of objects in my Parallel.ForEach loop into smaller (10,000 objects as opposed to 100,000) groups then I never got any exceptions thrown. NICE.

So the summary - check identity, then maybe break up your groups being run in the foreach loop... and someone figure out why this is necessary and report back :)

EDIT /end

Not the greatest solution, but relatively simple as compared to delving deeper into tasks and thread craziness.