I have a Windows Service that is running a Parallel.ForEach series which performs an array of tasks that update information in a SQL Server database.
Within the task itself, I have a method that retrieves data specific to that task via a DataTable. While the SqlConnection, SqlDataAdapter, etc... are being constructed within the scope of the task, there are still issues. The DataTable itself is not a global variable and is created only within the scope of the task as well.
All of that stated, the entire application will crash with a large number of timeouts coming from the database server - like it locked up and could not process the requests. I am specifically using WITH (NOLOCK) commands in my stored procedures but they have had no effect.
I think, given what I know, that the thread pool managing the connections to the database is being overwhelmed. To that end, I actually tried filtering down the number of concurrent tasks to 50 running at once, and even 20, but I still get these errors when the database stops responding.
Do I somehow need to lock the database connection? Doesn't that sound contrary to database pooling in the first place?