多线程错误：已经有与此连接关联的开放的DataReader，必须先关闭 [英] MultiThreading error: There is already an open DataReader associated with this Connection which must be closed first
问题描述
我有一个Parallel.Foreach循环
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);
}
}
这循环调用类和遍历我所有的数据行，但其中每个数据行的任一做一个DataTable填充或不更新命令到MySQL数据库。我有这两个的代码如下。
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);
}
}
事情是WCC含有10左右的空隙，每这些空隙的访问MySQL函数至少一次。所以，如果锁定是答案，是有可能创造1锁定功能适用于所有的空隙？如果是的话怎么样？如果有另一种方式，请让我知道
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
谢谢！
没关系，建议我巩固了问题
Okay as suggested i consolidated the questions
中的代码已经被更新，以反映锁定，请参见下文。
The code has now been updated to reflect locking, please see below.
静只读对象_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;
}
}这是调用DataReader的唯一代码，如何能有两个打开的数据读取器，如果有一个锁？
This is the only code that calls the DataReader, how can there be two open data readers if there is a lock?
推荐答案
的问题是，ADO.NET数据提供商通常不允许超过一个开放的数据读取器在同一时间每个连接。 SQL Server有
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.
您可能需要指定比<$ C $其他不同的连接C> MySQLProcessing.MySQLStatic.Connection 。没有什么使用多个连接阻止你。这里的问题是，连接是昂贵的资源，所以你应该有节制地使用它们。
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.
这篇关于多线程错误：已经有与此连接关联的开放的DataReader，必须先关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
