c# mysqlreader multithread fail

Question

I'm writing a Database class using c# that interacts with a multithread sdk. I should use only one connection(No need to say!) However, I always get errors about connection and datareaders. Anly help appreciated.

Here is the structure of my code

static class Db
{
  static MySqlConnection conn = new MySqlConnection(connectionString);

  private static void Connect()
  {
    if (conn.State == ConnectionState.Closed)
                    conn.Open();
  }

   private static void DisConnect()
   {
     if (conn.State == ConnectionState.Open)
     conn.Close();
    }  


    static int Insert()
    {
        Connect();
        MySqlCommand cmd = new MySqlCommand(sql, conn);
        cmd.ExecuteNonQuery();
        return Convert.ToInt32(cmd.LastInsertedId);
        DisConnect();
    }

     public static DataTable select(string sql) //Especially fails here
     {
         Connect();
         MySqlCommand cmd = new MySqlCommand(sql, conn);
         using (MySqlDataReader read = cmd.ExecuteReader())
         {
             if (read.HasRows){ Some Code... }
         }
         DisConnect();
     }
  }

1. Probable I/O race condition detected while copying memory, 2. There is an already open connection. 3. Connection must be valid and open. — fatih, Jan 21 '15 at 07:53
Why do you use everything static? Give an instance per thread? — Sriram Sakthivel, Jan 21 '15 at 07:56
Current code with static connection, which gets accessed by multiple threads is a recipe for failure, multiple threads try using it in the command object and fails due to AV issue. You should preferably use a connection pool, which user defined size, and then have multiple threads wait for connection to be free and use, but pool with just one thread, you can better lock and work with it, it would serialize access of all threads — Mrinal Kamboj, Jan 21 '15 at 08:10

score 1 · Accepted Answer · answered Jan 21 '15 at 10:17

Note that MySqlConnection instance is not guaranteed to be thread safe. You should avoid using the same MySqlConnection in several threads at the same time. It is recommended to open a new connection per thread and to close it when the work is done. Actually, connections will not be created/disposed every time with the Pooling=true; connection string option - connections will be stored in the connection pool. This boosts performance greatly.

I also suggest to use the 'using' clause on creating a connection so that it will be automatically disposed/closed and returned to the connection pool.

c# mysqlreader multithread fail

1 Answers1