title: C# Mysql - Using of locks on database query for async await server
body: <p>I have <code>TcpListener</code> class and I'm using <code>async/await</code> reading and writing.</p>
  
  <p>For this server I have created single database instance where I have prepared all database queries.</p>
  
  <p>But for more then one <code>TcpClient</code> I'm keep getting exception:</p>
  
  <blockquote>
    <p>An exception of type <code>MySql.Data.MySqlClient.MySqlException</code> occurred
    in <code>MySql.Data.dll</code> but was not handled in user code</p>
    
    <p>Additional information: There is already an open <code>DataReader</code> associated
    with this <code>Connection</code> which must be closed first.</p>
  </blockquote>
  
  <p>If I understand it correctly there can't be more then one database query at time which is problem with more then one <code>async</code> client.</p>
  
  <p>So I simply added locks in my queries like this and everything seems fine.</p>
  
  <pre><code>   // 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 &gt; 0;
  }
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>    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 &gt; 0;
          }
      }
  </code></pre>
  
  <p>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 <code>.Open()</code> method which takes +- 35ms.</p>
  
  <p>Why a lot of people use method with usings if there is much worse performance? Or am I doing something wrong?</p>