C# Mysql - Using of locks on database query for async await server

Question

I have TcpListener class and I'm using async/await reading and writing.

For this server I have created single database instance where I have prepared all database queries.

But for more then one TcpClient I'm keep getting exception:

An exception of type MySql.Data.MySqlClient.MySqlException occurred in MySql.Data.dll but was not handled in user code

Additional information: There is already an open DataReader associated with this Connection which must be closed first.

If I understand it correctly there can't be more then one database query at time which is problem with more then one async client.

So I simply added locks in my queries like this and everything seems fine.

   // One MySqlConnection instance for whole program.

   lock (thisLock)
   {
    var cmd = connection.CreateCommand();

    cmd.CommandText = "SELECT Count(*) FROM logins WHERE username = @user AND password = @pass";
    cmd.Parameters.AddWithValue("@user", username);
    cmd.Parameters.AddWithValue("@pass", password);

    var count = int.Parse(cmd.ExecuteScalar().ToString());
    return count > 0;
}

I have also try the method with usings which create new connection for every query as mentioned from someone of SO community but this method is much more slower than locks:

    using (MySqlConnection connection = new MySqlConnection(connectionString))
    {
        connection.Open();   // This takes +- 35ms and makes worse performance than locks

        using (MySqlCommand cmd = connection.CreateCommand())
        {
            cmd.CommandText = "SELECT Count(*) FROM logins WHERE username = @user AND password = @pass";
            cmd.Parameters.AddWithValue("@user", username);
            cmd.Parameters.AddWithValue("@pass", password);

            int count = int.Parse(cmd.ExecuteScalar().ToString());
            return count > 0;
        }
    }

I used Stopwatch to benchmarks this methods and queries with one connection with locks are performed in +- 20ms which is +- only delay of network but with usings it is +- 55ms because of .Open() method which takes +- 35ms.

Why a lot of people use method with usings if there is much worse performance? Or am I doing something wrong?

Answer 1

You're right, opening connection is a time-consuming operation. To mitigate this, ADO.NET has Connection pooling. Check this article for details.

If you go on with your performance test and check timings for subsequent connections, you should see that time for connection.Open() improves and gets close to 0 ms because connections are actually taken from the Pool.

With your lock implementation, you actually use connection pool with just one connection. While this approach could show better performance within a trivial test, it will show very poor results in highly loaded applications.