Concurrent users on Application result in MySQL Database Error

Question

I have a C# web application that connects to a MySQL database. When multiple users access the site at the same time we see a "there is already an open datareader associated with this command which must be closed first" error. The application works fine when only one person is accessing the site.

I found multiple articles that sited MultipleActiveResultSets=True in the connection string, but that only applies to SQL Server not MySql.

I traced the error to my runSQL function that handles the bulk of my database queries but am unable to find a solution.

This is a fairly straight forward function, it takes raw sql code, a list of parameters, an enum that translates to one of many possible database connection strings, and a bool that determines if we need to set up a transaction.

I am at a loss.

public DataTable runSQL(string QueryStr, List<MySqlParameter> Parameters, ConnectionType Connection, bool Transaction)
{
    DataTable results = new DataTable();
    MySqlConnection con = new MySqlConnection(getConnection(Connection));
    MySqlTransation trans;
    MySqlCommand command;

    con.Open();

    //if a transaction was requested, tie one to the query
    if(Transaction)
    {
        trans = con.BeginTransaction();
        command = new MySqlCommand(QueryStr, con, trans);
    }
    else
    {
        command = new MySqlCommand(QueryStr, con);
    }

    //if parameters were provided add them to the query
    if(Parameters.Count > 0)
        foreach(MySqlParameter parm in Parameters)
            command.Parameters.Add(parm);

    try
    {
        //send the command and get the results
        MySqlReader rdr = command.ExecureReader();

        //populate the column names
        string columnName;
        Type type;
        foreach(DataViewRow row in rdr.GetSchemaTable().DefaultView)
        {
            columnName = row["ColumnName"].ToString();
            type = (Type)row["DataType"];
            results.Columns.Add(columnName, type);
        }

        //populate the results
        results.Load(rdr);

        //so far so good, close the transaction if one was requested
        if(Transaction)
        {
            command.Transaction.Commit();
        }

        con.Close();
    }
    catch (Exception up)
    {
        //something bad happened, rollback if there was a transaction
        if(Transaction)
        {
            command.Transaction.Rollback();
        }

        con.Close();

        //report the error for handling at source.
        throw up;
    }

    return results;
}

Answer 1

Thanks Travis.

I just solved the problem by making the function static and removing the singleton pattern from the database connection. I built it that way to save memory, but in this case it caused more problem than it solved.

Answer 2

Concurrency in MySql is a nightmare. Sorry to start out like this, but if it at all possible you should port to MSSQL since you are using c# and it integrates very easily.

Concurrency is specifically poor when using the MyISAM MySQL database engine. First, and a large red flag, is that MyISAM does not support transactions. This means that you cannot change the isolation levels for any of your reads or updates. Second, and related to the first, is that using a read on a table issues a low level table lock. However, in order for an update to occur, it must have an exclusive table lock, and anything else (even low level) will prevent that from happening and it will go into the lock queue.

There is no fixing this because it is by design.