MultiThreading error: There is already an open DataReader associated with this Connection which must be closed first

Question

I have a Parallel.Foreach loop

var options = new ParallelOptions();
options.MaxDegreeOfParallelism = 1;
Parallel.ForEach(urlTable.AsEnumerable(),drow =>
{
    using (var WCC = new MasterCrawlerClass())
    {
        WCC.MasterCrawlBegin(drow);
    }
 }

This loop calls the class and loops through all my DataRows, however each of those datarows either does a DataTable fill, or does an update command to a MySQL DB. The code I have for both of those is below.

private static DataTable DTTable(string mysqlQuery, string queryName)
{
    DataTable DTTableTable = new DataTable();
    try
    {
        MySqlDataAdapter DataDTTables = new MySqlDataAdapter(mysqlQuery, MySQLProcessing.MySQLStatic.Connection);
        DataTable DataDTTablesDT = new DataTable();
        DataDTTables.SelectCommand.CommandTimeout = 240000;
        DataDTTables.Fill(DataDTTablesDT);
        DTTableTable = DataDTTablesDT;

    }
    catch (Exception ex)
    {

        GenericLogging("Failed MySQLquery: " + ex.Message.ToString(), "MySQLProcessor", "DTTable", "", "MysqlError", "", queryName, mysqlQuery);

    }
    return DTTableTable;
}

private static void MySQLInsertUpdate(string MySQLCommand, string mysqlcommand_name)
{
    try
    {
        MySqlCommand MySQLCommandFunc = new MySqlCommand(MySQLCommand, MySQLProcessing.MySQLStatic.Connection);
        MySQLCommandFunc.CommandTimeout = 240000;
        MySQLCommandFunc.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        GenericLogging("Failed MySQLquery: " + ex.Message.ToString(), "MySQLProcessor", "DTTable", "", "MysqlError", "", mysqlcommand_name, MySQLCommand);
    }
}

The thing is WCC contains 10 or so voids, each of these voids accesses the MySQL functions at least once. So if locking is the answer, is it possible to create 1 locking function for all voids? If so how? If there is another way, please let me know

Thanks!

Okay as suggested i consolidated the questions

The code has now been updated to reflect locking, please see below.

static readonly object _object = new object();

public static DataTable DTTable(string mysqlQuery, string queryName)
        {
            lock (_object)
            {
                DataTable DTTableTable = new DataTable();
                try
                {
                    using (MySqlDataAdapter DataDTTables = new MySqlDataAdapter(mysqlQuery, MySQLProcessing.MySQLStatic.Connection))
                    {
                        using (DataTable DataDTTablesDT = new DataTable())
                        {
                            DataDTTables.SelectCommand.CommandTimeout = 240000;
                            DataDTTables.Fill(DataDTTablesDT);
                            DTTableTable = DataDTTablesDT;
                            DataDTTables.Dispose();
                        }
                    }

                }
                catch (Exception ex)
                {

                    GenericLogging("Failed MySQLquery: " + ex.Message.ToString(), "MySQLProcessor", "DTTable", "", "MysqlError", "", queryName, mysqlQuery);

                }
                return DTTableTable;
            }
        }

This is the only code that calls the DataReader, how can there be two open data readers if there is a lock?

If I'm not mistaken, the problem is in the method that returns the DataTable, not the one that returns void. You might implement a connection pool to get around the problem. — phoog, Apr 09 '12 at 23:19

score 2 · Answer 1 · answered Apr 10 '12 at 00:31

The problem is that ADO.NET data providers generally do not allow for more than one open data reader at a time per connection. SQL Server has the concept of multiple active result sets (MARS), but as far as I know MySQL does not yet support it.

You will probably need to specify a different connection other than MySQLProcessing.MySQLStatic.Connection. There is nothing stopping you from using more than one connection. The problem here is that connections are expensive resources so you are supposed to use them sparingly.

score 0 · Answer 2 · answered Apr 10 '12 at 00:35

0

You're using the same connection at the same time.

Do you have several thread ? Because it seems that 2 threads use the same connection to concurrently make a call.

answered Apr 10 '12 at 00:35

Fabske

2,106
18
33

you are correct, but i put a lock in the void so how could this even be calling it while its locked – Apr 10 '12 at 00:37
1

There is no lock in the code pasted above. But are you sure it's the only method where you use this connection ? Also, you can create several connection it's low-cost as .NET have a pool of connection and doesn't create a new connection if one is already available. – Fabske Apr 10 '12 at 00:40
Sigh... i voted to close this one and the code with lock is http://stackoverflow.com/questions/10081513/c-sharp-there-is-already-an-open-datareader-associated-with-this-connection-whi – Apr 10 '12 at 00:41
yes shit happens :). Anyway, be sure you don't use the connection at several place. And if you want to be quiet for future usage, just create a new connection. – Fabske Apr 10 '12 at 00:42
2

Fabske well you would think you could close your own question without 5 votes – Apr 10 '12 at 00:43
@Mike: Yeah I hear you. You should be able to delete it right? That does not require any voting. – Brian Gideon Apr 10 '12 at 00:47
Fabske I use that one connection for each Thread, but no no other code it in use – Apr 10 '12 at 00:47
Do i lock the connection instead of the process? – Apr 10 '12 at 00:49
I notice my server CPU spike up to 50% could there be a lock on the server – Apr 10 '12 at 00:50
3

I don't get it. All this could be avoided by simply not using the static connection. – Chris Farmer Apr 10 '12 at 00:51
So you need to lock(yourconnection) {} But then **all** your code that use the connection need to do the same, otherwise it'll not lock. And I don't think it's a recommended way. – Fabske Apr 10 '12 at 00:51
@ChrisFarmer And call a new connection per DataTable holy cow thats 10,000 connections – Apr 10 '12 at 01:02
1

No it's not. You use it and dispose it and you're done with it. `using (var conn = CreateSomeConnection()) { /* use it. */ }`. It's one connection. – Chris Farmer Apr 10 '12 at 01:21
I admittedly am not familiar with MySQL's implementation of connection pooling, but this kind of thing works great in Oracle and MS SQL Server. – Chris Farmer Apr 10 '12 at 01:30

MultiThreading error: There is already an open DataReader associated with this Connection which must be closed first

2 Answers2

Linked

Related