TPL Producer consumer pattern - Start may not be called on a task that was already started

Question

I am trying to implement parallel processing inside sql server using custom sql clr code, like the example in below link.

http://www.sqlnotes.info/2015/12/03/run-t-sql-in-parallel/

The problem with the example in the above link is its using threads and locks and that's consuming lot of time. Hence the gains of parallel processing are negligible in many cases. So to fix this, I am trying to create a similar utility which uses Producer-Consumer pattern using TPL.

Producers - are sql procs which call the code to execute sql commands. Consumer - The utility should should parallel process these queries.

I will send the queries to be processed in batches, by using begin transaction, commit and sp_getapplock. The consumer should start at begin and keep looking for new tasks to process and end when the transaction is committed. For this I am trying to use below code:

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

    void StartWorker()
    {
        (Task.Factory.StartNew(() =>
        {
            while (!IsClosed)
            {
                Parallel.ForEach(PendingTaskList, new ParallelOptions { MaxDegreeOfParallelism = 10 }, task =>
                {
                    if (task.Status == TaskStatus.Created)
                    {
                        task.Start();
                    }
                });
                while ((!IsClosed) && (PendingTaskList.Count == 0)) Task.Delay(1);
            }
        }, TaskCreationOptions.LongRunning)).Start();
    }


    void Enqueue(string commandText, SqlParameter[] paramList, string queryName, string attributeSysCode)
    {
        //TODO: Localize variables in here ...
        //TODO: Do parameter checking on the T-SQL command text paased here ...
        //TODO: Consider caching pre-compiled versions of popular T-SQL parameters by hashvalue of param string here as key ...

        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = commandText;
        cmd.CommandTimeout = 0;
        cmd.CommandType = CommandType.Text;

        AsyncTaskItem item = new AsyncTaskItem() { Command = cmd, sqlParamList = paramList, QueryName = queryName, AttributeSysCode = attributeSysCode };
        var task = new Task(() => RunOne(item));
        PendingTaskList.Add(task.ContinueWith(t =>
            {
                PendingTaskList.Remove(t);
                CompletedTaskList.Add(t);
            }));

    }

The above code is throwing error "Start may not be called on a task that was already started.'"

How do I get around this issue?

What is `PendingTaskList`? You may be confusing two alternate forms of achieving parallelism -- `Task.Factory.StartNew` and `Parallel.ForEach`. If you've created a collection of tasks using the former, then they're already running in parallel; there's no need to start them within a `Parallel.ForEach`. — Douglas, Aug 10 '17 at 19:17
PendingTaskList is a list of tasks to be processed. Tasks are added to this list when ever a sql proc calls the function to execute a command. I edited the question to add the code where I am adding taks to the PendingTaskList. Hope it clarifies. — Nitheesh, Aug 10 '17 at 19:30

score 0 · Accepted Answer · answered Aug 10 '17 at 19:20

0

Remove the call to Start(). Since StartNew() starts the task it creates, there's no need for the Start().

answered Aug 10 '17 at 19:20

Jon Hanna

110,372
10
146
251

TPL Producer consumer pattern - Start may not be called on a task that was already started

1 Answers1