c# MySql stored procedure timeout

Question

I have a c# application that calls the same mysql stored procedure multiple times with different parameters. Its called about 250 times and each call takes about 30 seconds to complete. There are some cases when for some reason a call takes much more time, and it blocks the next ones from running, so I would like to set a timeout for the stored procedures to stop when it takes more than say like 5 minutes. This way the others could still run and only the one that took too much time would be skipped. I tried to use the command timeout of the mysql connection, but this does not kill the running stored procedure, only throws an exception in code which is not ideail because the next call will start while the previous one is still running.

Is there a way to set a mysql timout for the connection, or just kill a mysql thread/process (the sp) if it takes too much time? Closing the mysql command or connection did not do it, and clearing the connection pool did not help either.

Answer 1

To kill a running stored procedure, use MySqlCommand.Cancel (using the same MySqlCommand object that was used to start that stored procedure). Because MySqlCommand.ExecuteNonQuery (or ExecuteReader, etc.) will block the thread that called it, this will have to be done from another thread. One way to accomplish this would be with CancellationTokenSource, then registering a callback that will cancel the command:

// set up command
using (var command = new MySqlCommand("sproc_name", connection))
{
    command.CommandType = CommandType.StoredProcedure;

    // register cancellation to occur in five minutes
    using (var cts = new CancellationTokenSource(TimeSpan.FromMinutes(5)))
    using (cts.Token.Register(() => command.Cancel())
    {
        // execute the stored procedure as normal
        using (var reader = command.ExecuteReader())
        {
            // use reader, or just call command.ExecuteNonQuery instead if that's what you need
        }
    }
}