Parallel.Foreach循环创建多个db连接会引发连接错误？ [英] Parallel.Foreach loop creating multiple db connections throws connection errors?

问题描述

登录失败错误从未重现，好奇？但是，其他连接错误似乎通过砰击服务器来解释。错误发生的随机性仍然是一个谜。

登录失败。登录名来自不受信任的域，不能与Windows身份验证一起使用

我想让我的代码并行运行，所以我改变了我的foreach循环到一个平行的foreach循环。看起来很简单每个循环连接到数据库，查找一些东西，执行一些逻辑，添加一些东西，关闭连接。但是我得到上面的错误？

我使用我的本地sql server和实体框架（每个循环使用它自己的上下文）。使用相同的本地登录或某些连接多次连接有问题吗？我如何解决这个问题？

我有（在尝试隐藏到一个parallel.foreach循环之前）将我的foreach对象列表分成四组（单独的csv文件）并运行我的程序的四个并发实例（整体运行速度比只有一个，因此并行的想法）。所以似乎连接到db不应该是一个问题？

任何想法？

编辑：
以下是

  var gtgGenerator = new CustomGtgGenerator（）; 
 var connectionString = ConfigurationManager.ConnectionStrings [BioEntities]。ConnectionString; 
 
 var allAccessionsFromObs = _GetAccessionListFromDataFiles（collectionId）; 
 
 ForEach（allAccessionsFromObs中的cloneIdAndAccessions）
 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））; 
  

在DoWork内部我使用BioEntities

  using（var bioEntities = new BioEntities（connectionString））{...} 
  

它有好奇和好奇...

我在DoWork方法中添加了一些代码：

  Debug.WriteLine（Executing+ itemName +as+ WindowsIdentity.GetCurrent（）。Name）; 
  

，它神秘地开始工作（实际上很好/快）。但最终我得到了同样的优异（约一个小时之后）。但是我能够跟踪这个..？

 发生了类型System.Data.SqlClient.SqlException的第一次机会异常在System.Data.dll 
  

EDIT2：
嗯今天凌晨，我进来工作并开火了（痴迷于为什么会有一些工作），而且到目前为止，它正在巡航（正常运行）。偶尔：线程（0x27c8）已退出代码0（0x0）。
线程''（0x26b8）已退出代码0（0x0）。

但除此之外它还没有搞砸了吗？其中坦白说，只是让我更担心。这是一个一次性的事情，所以我可以使用它（如果它的工作），但发生了什么？可能是NLOG，登录多线程的堵塞填充并导致超时在sql？我真的不明白一旦停止工作，我会尝试弄清楚事情。有没有办法重置sql server，我不知道为什么它会工作一个小时，然后搞砸，然后永远不会工作超过几秒钟，现在（重新启动后）工作（约30分钟至今）？

解决方案

对不起，回答我自己的问题，但对某个人，某个地方有时可能是有价值的。 b
$ b

在添加（并随后删除）以下代码以查看每个线程正在使用的身份时，登录失败错误从未再次出现。我不知道如果使用它影响了某些东西，但是我现在不使用它，它似乎没有在最初给出的链接和答案中讨论的身份问题。

  Debug.WriteLine（Executing+ cloneIdAndAccessions.Item1.ToString（）+as+ WindowsIdentity.GetCurrent（）。Name）; 
  

这里一直存在两个问题，第一个是掩盖后者。
但是我仍然收到以下两种类型的连接错误。特别是（我注意到）打开SQL Server管理工作室，也许打破了服务器。

  System.Data.EntityCommandExecutionException：发生错误同时从商店提供商的数据阅读器阅读。查看内部例外情况。 ---> System.Data.SqlClient.SqlException：查询处理器无法启动并行查询执行所需的线程资源。 
  

And ...

  System.Data.EntityCommandExecutionException：执行命令定义时发生错误。查看内部例外情况。 ---> System.Data.SqlClient.SqlException：超时过期。在完成操作或服务器之前经过的超时时间没有响应。 
  

所以，我的解决方案（因为我的解决方案（因为错误会出现）要简单地捕获错误并记录Parallel.ForEach循环中的哪个对象抛出它，然后重新运行这些对象。仍然比简单的foreach循环更快。

编辑：嗯，这很有趣，我发现一些韵律/错误原因当他们被抛出，但也许为什么）。如果我将Parallel.ForEach循环中的对象数量分成较小的（10,000个对象而不是100,000个）组，那么我从来没有抛出任何异常。 NICE。

所以总结 - 检查身份，然后可能会破坏您的组正在foreach循环中运行...有人弄清楚为什么这是必要的并且回报：）

编辑/结束

不是最大的解决方案，而是比较简单与深入研究任务和线程疯狂相比。

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

解决方案

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.

这篇关于Parallel.Foreach循环创建多个db连接会引发连接错误？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！