Cancelling thread task

Question

I've got a MySQL connection which is executing a query in a separate thread, represented by a Task.

Through the cancellation token, I am stopping this thread when the user clicks a button.

My problem is that an error is being presented saying the following: Lock wait timeout exceeded; try restarting transaction

This is my code:

cancellationToken = new CancellationTokenSource();

 await Task.Run(() =>
     {
       using (MySqlConnection connection = new MySqlConnection(sourceDatabaseConnectionString))
            {
               connection.Open();

                using (MySqlCommand command = new MySqlCommand(sql, connection))
                        {
                            command.CommandTimeout = 0;
                            command.Parameters.AddWithValue("@month", datePicker.Value.Month);
                            command.Parameters.AddWithValue("@year", datePicker.Value.Year);
                            int rows = command.ExecuteNonQuery();
                            }
                        }
            }
     }, cancellationToken.Token);

On button press, I'm calling cancellationToken.Cancel();

When I try to execute the SQL statement again by starting the task, I get the error message shown above. What could be causing it?

I bet the most intresting part is under "//Execute query" comment — athabaska, Oct 13 '14 at 11:29
http://stackoverflow.com/questions/5836623/getting-lock-wait-timeout-exceeded-try-restarting-transaction-even-though-im — vtortola, Oct 13 '14 at 11:46
@vtortola - That question does not seem to explain how to solve it though, other than manually killing the process within MySQL. When cancelling the thread/Task, the query should be cancelled too, no? — desperate, Oct 13 '14 at 11:50
I think the problem maybe in the SQL you are using here and in other queries and not directly related to your C# code. Probably when you call "Cancel", a exception is thrown in that code, that you should catch to "Rollback" the transaction, otherwise locks still exists when you try to run again. — vtortola, Oct 13 '14 at 11:54
Why will the task be cancelled? Nothing is there to actually observe the cancellation. Don't you want `ExecuteNonQueryAsync(cancellationToken.Token)`? Do you just presume it to be cancelled? I doubt that the TPL will `Thread.Abort` a `Task` to cancel it. — ta.speot.is, Oct 13 '14 at 12:05

score 0 · Answer 1 · answered Oct 13 '14 at 12:02

This article may be useful for you: http://blogs.msdn.com/b/pfxteam/archive/2012/10/05/how-do-i-cancel-non-cancelable-async-operations.aspx

Briefly, it is written there how to allow program to continue upon a cancellation request even if the operation being waited on hasn’t completed yet. This should be also useful for handling such an error.

score 0 · Accepted Answer · answered Oct 13 '14 at 12:10

Through the cancellation token, I am stopping this thread when the user clicks a button.

Passing a cancellation token to Run(Action, CancellationToken) doesn't magically make the Task (once executing) cancelable. Look at the example which uses CancellationToken.ThrowIfCancellationRequested.

If it was possible to say okay arbitrary Task executing arbitrary code, please cancel yourself then there'd be no need for cancellation tokens because you could simply do Task.Cancel().

You probably intend to use OpenAsync(CancellationToken) and ExecuteNonQueryAsync(CancellationToken).

You end up with a lock timeout because you're waiting on your "cancelled" non-cancelled task.

Cancelling thread task

2 Answers2