Async call to database inside for loop

Question

I am trying to make call to database and store result in record, stored proc always returns 4 records, but some time I got 3 records and reader shows 4 count but null in first record. What is wrong with code ?

List record = new List();

List<Task> listOfTasks = new List<Task>();

for (int i = 0; i < 2; i++)
{
    listOfTasks.Add(Task.Factory.StartNew(() => {
        IDataCommand cmd = ds.CreateCommand("DropTicket", "returnTableTypeData",
            CommandType.StoredProcedure);
        IDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            TicketTextOutputRecord rec = new TicketTextOutputRecord();
            rec.ValidationNumber = (string)reader["ValidationNumber"];
            rec.IsSuccess = (bool)reader["IsSuccess"];
            rec.Error = (string)reader["Error"];
            record.Add(rec);
        }
        //reader.Close();
        //reader.Dispose();
    }));
}

Task.WaitAll(listOfTasks.ToArray());

return record;

The `List` class is not thread-safe. Take a look at this: [List thread safety](https://stackoverflow.com/questions/5020486/listt-thread-safety). — Theodor Zoulias, Apr 01 '20 at 12:04

score 1 · Answer 1 · answered Apr 01 '20 at 11:44

1

This sounds like a concurrency error; it is not intended that a connection is accessed concurrently; you are allowed overlapping readers (if MARS is enabled), but the actual access must still not be concurrent in terms of multiple threads trying to do things at the same time. The moment you do that, all behavior is undefined. Frankly, I'd just execute these sequentially, not concurrently. You are allowed to work concurrently if you use completely unrelated connections, note.

answered Apr 01 '20 at 11:44

Marc Gravell

1,026,079
266
2,566
2,900

my requirement is , calling service from UI in a loop of 200 or more count and sending 10000 records , and then in service dividing this 10000 records into small chunk lets say 100, and call DB 100 times and save result from 100 calls in one collection and send back to UI. Any suggestion would be really helpful for me. – A.Verma Apr 01 '20 at 11:50
Open multiple connections then @A.Verma. – mjwills Apr 01 '20 at 11:53
2

@A.Verma in addition to what mjwillis suggests, consider also `Parallel.For` so you can control the max-dop – Marc Gravell Apr 01 '20 at 11:53

weichch · Answer 2 · 2020-04-01T19:34:33.743

0

I fixed exactly the same error before.

List is not thread safe.

When adding items concurrently, the internal pointer of list can get confused and can cause item to return null even if non null value was added.

This produces the problem:

var list = new List<object>();
var listOfTasks = new List<Task>();

for (var i = 0; i < 10; i++)
{
    listOfTasks.Add(Task.Factory.StartNew(() => list.Add(new object())));
}

Task.WaitAll(listOfTasks.ToArray());

Use a thread safe list will fix the problem. But I’d change the task to return the result rather than adding it to a list. Then use LINQ or Task.WhenAll to get those results.

var listOfTasks = new List<Task<object>>();

for (var i = 0; i < 10; i++)
{
    listOfTasks.Add(Task.Factory.StartNew(() => new object()));
}

var list = await Task.WhenAll(listOfTasks.ToArray());

// OR

Task.WaitAll(listOfTasks.ToArray());
var list = listOfTasks.Select(t => t.Result).ToList();

edited Apr 01 '20 at 19:34

answered Apr 01 '20 at 12:31

weichch

9,306
1
13
25

Does `List record = new List();` compile for you @weichch? To be clear, `listOfTasks.Add` is **100% safe**. `record.Add` might (or might not) be safe. We don't know enough to make that judgement. – mjwills Apr 02 '20 at 01:23
@mjwills The OP may or may not share code that can compile. I wouldn't think the `List` is not `List` just because the code can't compile. – weichch Apr 02 '20 at 01:24
@weichch: I did not try that you mentioned as Parallel.For solved my problem. May be with real data I might need your solution. – A.Verma Apr 04 '20 at 12:30
@weichch : Now I am using the solution you mentioned as Parallel.for not worked in my case. But the problem is : I have list of records and i have to send records in batch depending on app.config value. Lets say value is 10, So I have to send 10 records at one time to DB and continue sending asynchronously 10 records to DB untill list is empty. I have one table type variable for which I am creating DataTable from that list. – A.Verma Apr 13 '20 at 06:01

Async call to database inside for loop

2 Answers2