C＃Mysql-在数据库查询中使用锁以用于异步等待服务器

问题描述

我有 TcpListener 类，并且正在使用 async / await 进行读写。

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.

但是对于多个 TcpClient 我一直在收到异常：

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

其他信息：已经有一个打开的 DataReader 与
与此 Connection 必须先关闭。

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

如果我理解正确，那么一次只能进行一个数据库查询，一个以上的 async 客户端是一个问题。

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;
}

我也尝试过使用uses方法为每个查询创建新连接， SO社区的某人提到过，但是此方法比锁慢得多：

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;
        }
    }

我使用Stopwatch对这种方法和查询进行了基准测试与锁的连接在+-20ms内执行，这是+-仅网络延迟，但由于 .Open（）方法需要+-35ms，因此使用时为+-55ms。

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.

为什么如果性能差得多，为什么很多人都使用uses方法？还是我做错了什么？

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

推荐答案

您是对的，打开连接非常耗时。为了减轻这种情况，ADO.NET具有连接池。查看以了解详细信息。

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

如果继续进行性能测试并检查后续连接的时间，则应该看到 connection.Open（）改善并接近于0 ms，因为连接实际上是从Pool中获取的。

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.

这篇关于C＃Mysql-在数据库查询中使用锁以用于异步等待服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！