任务过多导致 SQL 数据库超时
我的问题是，我显然使用了太多的任务（线程？）来调用查询 SQL Server 2008 数据库的方法。代码如下：
for(int i = 0; i < 100000 ; i++)
{
Task.Factory.StartNew(() => MethodThatQueriesDataBase()).ContinueWith(t=>OtherMethod(t));
}
一段时间后，我收到 SQL 超时异常。我想让缓冲区的实际线程数保持在 100000 以下，“一次不超过 10 个”。我知道我可以使用 ThreadPool 管理自己的线程，但我希望能够通过 ContinueWith 来利用 TPL 的优点。
我查看了 Task.Factory.Scheduler.MaximumConcurrencyLevel 但它没有设置器。
我该怎么做？
提前致谢！
更新1
我刚刚测试了 LimitedConcurrencyLevelTaskScheduler 类（由 Skeet 指出），并且仍在执行相同的操作（SQL 超时）。
顺便说一句，该数据库每天接收超过 800000 个事件，并且从未因这些事件而崩溃或超时。这听起来有点奇怪。
My problem is that I'm apparently using too many tasks (threads?) that call a method that queries a SQL Server 2008 database. Here is the code:
for(int i = 0; i < 100000 ; i++)
{
Task.Factory.StartNew(() => MethodThatQueriesDataBase()).ContinueWith(t=>OtherMethod(t));
}
After a while I get a SQL timeout exception. I want keep the actual number of threads low(er) than 100000 to a buffer of say "no more than 10 at a time". I know I can manage my own threads using the ThreadPool, but I want to be able to use the beauty of TPL with the ContinueWith.
I looked at the Task.Factory.Scheduler.MaximumConcurrencyLevel but it has no setter.
How do I do that?
Thanks in advance!
UPDATE 1
I just tested the LimitedConcurrencyLevelTaskScheduler class (pointed out by Skeet) and still doing the same thing (SQL Timeout).
BTW, this database receives more than 800000 events per day and has never had crashes or timeouts from those. It sounds kinda weird that this will.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可以创建一个
TaskScheduler< /a> 并发程度有限，如此处所述，然后从中创建一个TaskFactory，然后使用该工厂来启动任务而不是Task.Factory。You could create a
TaskSchedulerwith a limited degree of concurrency, as explained here, then create aTaskFactoryfrom that, and use that factory to start the tasks instead ofTask.Factory.任务与线程不是 1:1 - 任务被分配线程以从线程池中执行，并且线程池通常保持相当小的（线程数 == CPU 核心数），除非任务/线程被阻塞等待长时间运行的同步结果 - 例如同步网络调用或文件 I/O。
因此，启动 10,000 个任务不应导致产生 10,000 个实际线程。然而，如果这些任务中的每一个都立即陷入阻塞调用，那么您可能会得到更多线程，但它仍然不应该是 10,000 个。
这里可能发生的情况是，您一次会因为太多请求而压垮 SQL 数据库。即使系统只为数千个任务设置了少数线程，如果调用的目标是单线程的，少数线程仍然可能导致堆积。如果每个任务都调用 SQL 数据库，并且 SQL 数据库接口或数据库本身通过单个线程锁协调多线程请求，那么所有并发调用将堆积起来等待线程锁进入 SQL 数据库执行。无法保证接下来将释放哪些线程来调用 SQL 数据库，因此您很可能会遇到一个“不幸”的线程，该线程提前开始等待对 SQL 数据库的访问，但不会进入 SQL 数据库调用在阻塞等待超时之前。
SQL 后端也可能是多线程的，但由于许可级别限制了并发操作的数量。也就是说，SQL 演示引擎仅允许 2 个并发请求，但完全许可的引擎支持数十个并发请求。
无论哪种方式，您都需要采取一些措施将并发性降低到更合理的水平。 Jon Skeet 关于使用 TaskScheduler 来限制并发的建议听起来是一个不错的起点。
Tasks are not 1:1 with threads - tasks are assigned threads for execution out of a pool of threads, and the pool of threads is normally kept fairly small (number of threads == number of CPU cores) unless a task/thread is blocked waiting for a long-running synchronous result - such as perhaps a synchronous network call or file I/O.
So spinning up 10,000 tasks should not result in the production of 10,000 actual threads. However, if every one of those tasks immediately dives into a blocking call, then you may wind up with more threads, but it still shouldn't be 10,000.
What may be happening here is you are overwhelming the SQL db with too many requests all at once. Even if the system only sets up a handful of threads for your thousands of tasks, a handful of threads can still cause a pileup if the destination of the call is single-threaded. If every task makes a call into the SQL db, and the SQL db interface or the db itself coordinates multithreaded requests through a single thread lock, then all the concurrent calls will pile up waiting for the thread lock to get into the SQL db for execution. There is no guarantee of which threads will be released to call into the SQL db next, so you could easily end up with one "unlucky" thread that starts waiting for access to the SQL db early but doesn't get into the SQL db call before the blocking wait times out.
It's also possible that the SQL back-end is multithreaded, but limits the number of concurrent operations due to licensing level. That is, a SQL demo engine only allows 2 concurrent requests but the fully licensed engine supports dozens of concurrent requests.
Either way, you need to do something to reduce your concurrency to more reasonable levels. Jon Skeet's suggestion of using a TaskScheduler to limit the concurrency sounds like a good place to start.
我怀疑您处理数据库连接的方式有问题。 Web 服务器可能有数千个并发页面请求，所有这些请求都在 SQL 活动的各个阶段运行。我敢打赌，尝试减少并发任务数量实际上掩盖了另一个问题。
您能分析 SQL 连接吗？检查 perfmon 以查看有多少活动连接。看看是否可以尽快抓取-使用-释放连接。
I suspect there is something wrong with the way you're handling DB connections. Web servers could have thousands of concurrent page requests running all in various stages of SQL activity. I'm betting that attempts to reduce the concurrent task count is really masking a different problem.
Can you profile the SQL connections? Check out perfmon to see how many active connections there are. See if you can grab-use-release connections as quickly as possible.