MySQL C# async methods doesn't work?

Question

i have a database in a server and it seems like the async method doesn't work.

Here is my code:

static async void Example()
{
    string connectionString =
        "Server=mydomainname.com;" +
        "Port=3306;" +
        "Database=scratch;" +
        "Uid=Assassinbeast;" +
        "Password=mypass123;" +
        "AllowUserVariables= true;";

    MySql.Data.MySqlClient.MySqlConnection sqConnection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
    await sqConnection.OpenAsync();

    Console.Write("Opened. Now Click to close");
    Console.ReadLine();

    sqConnection.Close();
}
static void Main(string[] args)
{
    Console.ReadLine();
    Example();
    Console.WriteLine("Done");
    Console.ReadLine();
}

At the "await" statement, its actually supposed to jump back to the Main() function and writeout "Done". But its not doing that. Its just running synchronously like it wasn't an async method and it will first write "Done" once the function is fully completed.

So what am i doing wrong? Is it a bug?

UPDATE

Okay, so after i got some answers, i actually still couldn't see any difference between the OpenAsync() and Open().

I began trying to test more things out and i think i can conclude that the async method DOES NOT WORK

Here is my new code:

static async Task Example()
{
    string connectionString =
        "Server=mydomainname.com;" +
        "Port=3306;" +
        "Database=scratch;" +
        "Uid=Assassinbeast;" +
        "Password=mypass123;" +
        "AllowUserVariables= true;";

    using (var sqConnection = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
    {
        Console.WriteLine("Opening");
        await sqConnection.OpenAsync();
        Console.WriteLine("Opened. Now Closing");
    }
}
static async Task Example2()
{
    //Lets pretend this is a database that my computer will try to connect to

    Console.WriteLine("Opening");
    await Task.Delay(1000); //Lets say it takes 1 second to open
    Console.WriteLine("Opened. Now Closing");
}
static void Main(string[] args)
{
    Console.ReadLine();

    Task.Run(() => Example());
    Task.Run(() => Example());
    Task.Run(() => Example());
    Task.Run(() => Example());
    Task.Run(() => Example());
    Task.Run(() => Console.WriteLine("Done"));
    Console.ReadLine();
}

Here when i run the Example() 5 times, it will output like this:

It takes up to 3 seconds before it will write out "Done". Note that my computer is not working on the CPU at all, because it is only waiting and connecting to the database which takes around 1 second to connect to.

So its actually blocking my computers thread and not running multithreaded, otherwise it would write out "Done" immediedtly.

So if i called Example2() instead of Example(), then i get this result which is what i want and what i would expect:

Here, it is true async method, because i can do over 6 things at a time on my computer which has only 2 cores. But the first example, i could only do two things at a time because MySQL async method does not work.

I also tested it with sqConnection.Open(), which had the exact same result as sqConnection.OpenAsync()

So right now, i just cant figure out how to connect to the database 5 times at the same time.

Answer 1

Judging from some old code (6.7.2), it appears that the mysql ADO.NET provider does not implement any of the async functionality correctly. This includes the TAP pattern and the older style Begin..., End... async patterns. In that version, the Db* async methods appear to not be written at all; they would be using the base class ones in .NET which are synchronous and all look something like:

public virtual Task<int> ExecuteNonQueryAsync(...) {
    return Task.FromResult(ExecuteNonQuery(...));
}

(100% synchronous with the added overhead of wrapping it in a task; reference source here)

If the Begin and End versions were written correctly (they aren't) it could be implemented something like this:

public override Task<int> ExecuteNonQueryAsync(...) {
    return Task<int>.Factory.FromAsync(BeginExecuteNonQueryAsync, EndExecuteNonQueryAsync, null);
}

(reference source for that method for SqlCommand)

Doing this is dependent on some sort of callback api for the underlying socket to eventually deal with in a pattern where the caller sends some bytes over the socket and then a registered method gets called back from the underlying network stack when it is ready.

However, the mysql connector doesn't do this (it doesn't override that method in the first place; but if it did, the relevant begin and end methods aren't async on some underlying socket api). What the mysql connector does instead is build a delegate to an internal method on the current connection instance and invokes it synchronously on a separate thread. You cannot in the meantime for example execute a second command on the same connection, something like this:

private static void Main() {
    var sw = new Stopwatch();
    sw.Start();
    Task.WaitAll(
        GetDelayCommand().ExecuteNonQueryAsync(),
        GetDelayCommand().ExecuteNonQueryAsync(),
        GetDelayCommand().ExecuteNonQueryAsync(),
        GetDelayCommand().ExecuteNonQueryAsync(),
        GetDelayCommand().ExecuteNonQueryAsync(),
        GetDelayCommand().ExecuteNonQueryAsync(),
        GetDelayCommand().ExecuteNonQueryAsync(),
        GetDelayCommand().ExecuteNonQueryAsync(),
        GetDelayCommand().ExecuteNonQueryAsync(),
        GetDelayCommand().ExecuteNonQueryAsync(),
        GetDelayCommand().ExecuteNonQueryAsync(),
        GetDelayCommand().ExecuteNonQueryAsync());
    sw.Stop();
    Console.WriteLine(sw.Elapsed.Seconds);
}

private static DbCommand GetDelayCommand() {
    var connection = new MySqlConnection (...);
    connection.Open();
    var cmd = connection.CreateCommand();
    cmd.CommandText = "SLEEP(5)";
    cmd.CommandType = CommandType.Text;
    return cmd;
}

(assuming you are connection pooling and the number of tasks there is more than the maximum pool size; if async worked this code would get a number depending on the number of connections in the pool instead of a number depending on both that and the number of threads that can run concurrently)

This is because the code has a lock on the driver (the actual thing that manages the network internals; *). And if it didn't (and the internals were otherwise thread safe and some other way was used to manage connection pools) it goes on to perform blocking calls on the underlying network stream.

So yeah, no async support in sight for this codebase. I could look at a newer driver if someone could point me to the code, but I suspect the internal NetworkStream based objects do not look significantly different and the async code is not looking much different either. An async supporting driver would have most of the internals written to depend on an asynchronous way of doing it and have a synchronous wrapper for the synchronous code; alternatively it would look a lot more like the SqlClient reference source and depend on some Task wrapping library to abstract away the differences between running synchronously or async.

* locking on driver doesn't mean it couldn't possibly be using non-blocking IO, just that the method couldn't have been written with a lock statement and use the non-blocking Begin/End IAsyncResult code that could have been written prior to TAP patterns.

Edit: downloaded 6.9.8; as suspected there is no functioning async code (non-blocking IO operations); there is a bug filed here: https://bugs.mysql.com/bug.php?id=70111

Update July 6 2016: interesting project on GitHub which may finally address this at https://github.com/mysql-net/MySqlConnector (could probably use more contributors that have a stake in its success [I am no longer working on anything with MySql]).

Answer 2

Well, it could be that there's a pooled connection that's already open. In which case, it would be returned to you synchronously.

The thing to notice is that await does not necessarily return control back to the calling method. It only returns to the calling method if the status of the task being awaited is TaskStatus.Running. If the task has completed, then execution carries on as normal.

Try awaiting this method, which returns a task with status RanToCompletion:

public Task<int> SampleMethod()
{
    return Task.FromResult(0);
}

Answer 3

Avoid using void as return type in async methods. Async void is just meant for event handlers. All other async methods should return Task or Task<T>. Try this:

static async Task Example()
{
    string connectionString =
        "Server=mydomainname.com;" +
        "Port=3306;" +
        "Database=scratch;" +
        "Uid=Assassinbeast;" +
        "Password=mypass123;" +
        "AllowUserVariables= true;";

    MySql.Data.MySqlClient.MySqlConnection sqConnection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
    await sqConnection.OpenAsync();

    Console.Write("Opened. Now Click to close");
    Console.ReadLine();

    sqConnection.Close();
}
static void Main(string[] args)
{
    Console.ReadLine();
    Task.Run(() => Example()).Wait();
    Console.WriteLine("Done");
    Console.ReadLine();
}

UPDATE

As explained by dcastro, this is how async-await works:

If the awaited task hasn't finished and is still running, Example() will return to its calling method, thus the main thread doesn't get blocked. When the task is done then a thread from the ThreadPool (can be any thread) will return to Example() at its previous state and continue execution.

Or A second case would be that the task has already finished its execution and the result is available. When reaching the awaited task the compiler knows that it has the result and will keep on executing code on the very same thread.