SqlDataAdapter.Fill fails fails in multithreading

Question

Can someone explain me why SqlDataAdapter.Fill fails in multithreading? I've prepared an example in LinqPad:

var connectionString = "Data Source=<server>;Initial Catalog=<DB>;Integrated Security=True";
var connection = new SqlConnection(connectionString);
connection.Open();

var lck = new object();
var tasks = new List<Task>();
for(var i=0; i<64; i++)
tasks.Add(Task.Factory.StartNew(() => 
{
    using(var command = new SqlCommand(string.Format("select {0}", i), connection))
    using(var ds = new DataSet())
    using(var da = new SqlDataAdapter(command))
    {
        //lock(lck)
        {
            da.Fill(ds);
        }
    }
    Console.Write("Ok ");
}));
Task.WaitAll(tasks.ToArray());

It gives me different errors all the time. And sometimes just hangs.

But if you uncomment the commented lock statement - everything will work fine.

Do you have MARS enabled? https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-multiple-active-result-sets — mjwills, Aug 24 '17 at 12:19
You are sharing an `SqlConnection` which is not thread safe. Create one as needed, pooling will optimise this. — Alex K., Aug 24 '17 at 12:19

score 0 · Answer 1 · answered Aug 24 '17 at 12:20

Your tasks share the SqlConnection. When the tasks run concurrently each one opens a data reader (by using SqlDataAdapter). The exception occurs because this is not allowed. Only one data reader may be open.

As far as I know there are 2 ways to solve it:

Open a new connection in each task.
Activate MARS, i.e. add MultipleActiveResultSets=true to your connection string.

SqlDataAdapter.Fill fails fails in multithreading

1 Answers1