C# MySql Driver - Async Operations

Question

Lately i have began working with the MySQL driver for C# https://github.com/mysql/mysql-connector-net

Working with async/await i tried to run simple select queries in parallel tasks

This is basically how the code looks:

    private async Task<List<string>> RunQueryA()
    {
        List<string> lst = new List<string>();

        using (MySqlConnection conn = new MySqlConnection(someConnectionString))
        using (MySqlCommand cmd = conn.CreateCommand())
        {
            await conn.OpenAsync();
            cmd.CommandText = "select someField from someTable ...";

            using (var reader = await cmd.ExecuteReaderAsync())
            {
                // ...
            }
        }

        return lst;
    }

    private async Task<List<string>> RunQueryB()
    {
        List<string> lst = new List<string>();

        using (MySqlConnection conn = new MySqlConnection(someConnectionString))
        using (MySqlCommand cmd = conn.CreateCommand())
        {
            await conn.OpenAsync();
            cmd.CommandText = "select someField2 from someTable2 ...";

            using (var reader = await cmd.ExecuteReaderAsync())
            {
                // ...
            }
        }

        return lst;
    }

    public async Task Run()
    {
        await Task.WhenAll(RunQueryA(), RunQueryB());
    }

What i expected was for the both queries to run in parallel, what i saw was that RunQueryA() began to run and only once it was done RunQueryB could begin.

Naturally it would suggest that one or more of the methods that were used in the query are blocking. To find out, i downloaded the latest MySQL driver source code (from their github repo) and looked for the implementation of the async methods.

I looked for instance at the implementation of ExecuteReaderAsync and it led me to the base class System.Data.Common.DbCommand which is part of the BCL

I looked up that class in the .NET Reference source https://referencesource.microsoft.com/#System.Data/System/Data/Common/DBCommand.cs,1875e74763fd9ef2

And what i saw really confused me:

public Task<DbDataReader> ExecuteReaderAsync() {
            return ExecuteReaderAsync(CommandBehavior.Default, CancellationToken.None);
        }

        public Task<DbDataReader> ExecuteReaderAsync(CancellationToken cancellationToken) {
            return ExecuteReaderAsync(CommandBehavior.Default, cancellationToken);
        }

        public Task<DbDataReader> ExecuteReaderAsync(CommandBehavior behavior) {
            return ExecuteReaderAsync(behavior, CancellationToken.None);
        }

        public Task<DbDataReader> ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) {
            return ExecuteDbDataReaderAsync(behavior, cancellationToken);
        }

        protected virtual Task<DbDataReader> ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) {
            if (cancellationToken.IsCancellationRequested) {
                return ADP.CreatedTaskWithCancellation<DbDataReader>();
            }
            else {
                CancellationTokenRegistration registration = new CancellationTokenRegistration();
                if (cancellationToken.CanBeCanceled) {
                    registration = cancellationToken.Register(CancelIgnoreFailure);
                }

                try {
                    return Task.FromResult<DbDataReader>(ExecuteReader(behavior));
                }
                catch (Exception e) {
                    registration.Dispose();
                    return ADP.CreatedTaskWithException<DbDataReader>(e);
                }
            }
        }

It all boils down to this line:

return Task.FromResult<DbDataReader>(ExecuteReader(behavior));

In this line, ExecuteReader would run synchronously and block the calling thread.

ExecuteReader calls an abstract method

abstract protected DbDataReader ExecuteDbDataReader(CommandBehavior behavior);

which is overridden inside the MySQL driver:

protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior)
    {
      return ExecuteReader(behavior);
    } 

The implementation inside the MySQL basically calls the synchronous version of ExecuteReader...

So in short, ExecuteReaderAsync() runs ExecuteReader() synchronously and blocks the calling thread.

Please correct me if i am mistaken but it really seems to be the case.

I can't exactly pinpoint who is to blame here, the DbCommand class of the BCL or the MySQL driver implementation...

On one hand, the MySQL driver should've taken it into account, On the other hand, since DbCommand provides a base implementation of ExecuteDbDataReaderAsync it should at least launch the synchronous version of ExecuteReader in a worker thread (let alone using actual asynchronous I/O) so it would not block.

What do think about it?

What can i do as a work around? I could just launch ExecuteReaderAsync as a task by myself but i don't like this solution.

What do you suggest?

Thanks, Arik

Answer 1

The DbCommand class has been around since (at least) .NET 2.0. When Microsoft added the ExecuteNonQueryAsync, ExecuteReaderAsync etc. methods in .NET 4.5, they had to do it in a backwards-compatible way.

The best way to do this was to do what the .NET framework does: delegate to the existing synchronous method and wrap its return value in a Task. (It's almost never a good idea to make a method "asynchronous" by calling Task.Run in the implementation; for a more detailed explanation, see Should I expose asynchronous wrappers for synchronous methods? and Task.Run Etiquette and Proper Usage.)

To get true asynchronous behaviour, the developer of the database connection library has to convert it to being truly async. This can be difficult; making a large synchronous codebase asynchronous may involve rewriting a large portion of the code.

At the moment, Oracle's MySQL connector for .NET does not implement true asynchronous methods. MySQL Bug 70111 reports this problem in the MySQL connector. It's also discussed further in this question.

I would recommend using a library I've been working on: MySqlConnector on NuGet and GitHub. It is a completely independent, fully async implementation of the MySQL protocol for .NET and .NET Core. The API is the same as the official MySql.Data connector, so it should be a drop-in replacement for most projects (that want true async DB connections).