Parallel.ForEach causing SQL query to not work

Question

I'm paging through a set of data (example of query below) to verify some information. When I use Parallel.ForEach, the SQL command to get the second page of results comes back empty. If I break on the return statement and move the active line back to the Repository, it will return the results I'm looking for. Changing the Parallel.ForEach to a standard C# foreach works as expected.

Example of the query:

SELECT TOP 500
        StudentID,
        StudentName
    FROM Student
    WHERE StudentVerified IS NULL

Example of the code...

while(true)
{
    using(var rep = new Repository())
    {
        var students = rep.GetStudents();
        if (students.Length == 0) return false;
        Parallel.ForEach(students, (student) =>
        {
            rep.StudentVerified(student.StudentID, true);
        });
    }
}

Any help to figure out why the second page of results is coming back empty is appreciated. Thanks.

likely Repository.StudentVerified is not thread safe. You need to show the code for it. — Scott Chamberlain
– Scott Chamberlain, Commented Jul 27, 2016 at 0:09
It's a simple SQL UPDATE statement. However, it shouldn't matter if it was thread safe or not, the SELECT command should still either return data or there should be exceptions. Returning no results is definitely not expected. — Brian
– Brian, Commented Jul 27, 2016 at 0:13
"Changing the Parallel.ForEach to a standard C# foreach works as expected" - as a follow up to Scott, setting MaxDegreeOfParallelism to 1 in options (just for the sake of it) will likely result in it working too. Thus the problem isn't so much "Parallel.ForEach causing SQL query to not work" rather that your code is potentially not thread-safe. If Repository.StudentVerified are all sharing the same db connection, that is perhaps your problem right there. Same applies to EF — user585968
– user585968, Commented Jul 27, 2016 at 0:14
It is using the same connection, but why would that cause the SELECT to return 0 results? The SELECT is not in the ForEach, so it shouldn't be effected by any issues with unsafe code. As a note, it is straight SqlCommands in the Repository and Repository.StudentVerified is working as expected, no errors and the values are getting set as expected. — Brian
– Brian, Commented Jul 27, 2016 at 0:24
"it shouldn't matter if it was thread safe or not" is very very very wrong.... You still have 1 SqlConnection and that is not thread safe... — user6522773
– user6522773, Commented Jul 27, 2016 at 0:24

Scott Chamberlain · Accepted Answer · 2016-07-27 00:29:03Z

2

From you comments you appear to be sharing a SqlConnection between requests. That class is not thread safe. SqlConnection is optimised to have many short lived connections, create the connection as you need it then dispose of it when you are done by using a using block.

answered Jul 27, 2016 at 0:29

Scott Chamberlain

128k37 gold badges299 silver badges447 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brian Over a year ago

I'm trying to understand the problem better. Do you have any idea why it is silently failing? Or why the code outside of the Parallel.ForEach is failing but not the code inside of it? The behavior seems to be completely counter-intuitive to me.

Collectives™ on Stack Overflow

Parallel.ForEach causing SQL query to not work

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related