C#多线程(并行)使用锁访问静态MySQL连接{get; set;} [英] C# Multiple threads (Parallel) accessing static MySQL Connection {get;set;} with locks
问题描述
我有一个从DataTable运行Parral.Foreach的应用程序.在该Parallel中(3-10个并行中的任何位置)，该类将执行update或select语句.我有一个{get; set;}参见下面的MySQL连接.
I have an application that runs a Parral.Foreach from a DataTable. Within that Parallel (anywhere from 3-10 parallels) the class executes either an update or select statement. I have a MySQL connection that {get;set;} see below.
public static MySqlConnection Connection { get; set; }
public static MySqlConnection OpenCon(string ServerAddress,int PortAddress, string UserID, string Password,int ConnectionTimeOut)
{
MySqlConnection masterOpenCON = new MySqlConnection("server=" + ServerAddress + ";Port=" + PortAddress + ";UID=" + UserID + ";PASSWORD=" + Password + ";connectiontimeout="+ConnectionTimeOut+";");
masterOpenCON.Open();
return masterOpenCON;
}
这是我的平行
Parallel.ForEach(urlTable.AsEnumerable(), drow =>
{
WebSiteCrawlerClass WCC = new WebSiteCrawlerClass();
if (drow.ItemArray[0].ToString().Contains("$"))
{
WCC.linkGrabberwDates(drow.ItemArray[0].ToString(), "www.as.com");
}
});
现在，在WCC.LinkGrabberwDates中执行这样的mysql命令
Now within WCC.LinkGrabberwDates executes a mysql command like so
string mysql_Update = "update trad_live" + StaticStringClass.tableID + " set price = '"+priceString+"',LastProcessDate = Now() where ListingID = 'AT"+ IDValue+"'";
MySQLProcessing.MySQLProcessor.MySQLInsertUpdate(mysql_UpdateExistingr,"mysql_UpdateExisting");
这是MySQLInsertUpdate
And here is MySQLInsertUpdate
public static void MySQLInsertUpdate(string MySQLCommand,string mysqlcommand_name)
{
try
{
MySqlCommand MySQLCommandFunc = new MySqlCommand(MySQLCommand, Connection);
MySQLCommandFunc.CommandTimeout = 240000;
MySQLCommandFunc.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
与我有关的两件事是性能和数据完整性.我知道添加锁会降低性能，但会增加数据完整性.
The two things that concern me is performance and data integrity. I know adding a lock will slow done the performance but will increase data integrity.
我不希望与服务器建立10个以上的连接，所以我的问题是这个.
I would prefer not to create 10+ connections to the server, so my question is this.
根据sql语句或在公共无效的MySQLInsertUpdate内部，将锁实际放置在上述代码中的何处.我的下一个问题是，除了每个线程的锁/附加连接之外，还有其他更好的方法吗?
Where would the lock actually be placed in the above code, per sql statement or inside of the public void MySQLInsertUpdate. My next question is, is there a better way besides lock/additional connections per thread?
我确实意识到这目前是静态的，但是我正在更改静态状态
I do realize that this is currently static but i am in the process of changing the static status
推荐答案
IMO并行访问单个静态连接并尝试自己管理锁似乎是一个糟糕的设计.
IMO accessing a single static connection in parallel and trying to manage the lock yourself seems like a bad design.
为什么不使用内置的连接池?这将确保只有X个打开的连接(无论您想要多少个X).因此，如果仅需要1个DB连接，则只需将连接池的最小和最大大小设置为1.
Why not use the connection pooling built in? That will ensure that there are only X number of open connections (X being however many you want). So if you only want 1 DB connection, you could just set the connection pool min and max sizes to 1.
这也可以让您扩大"配置中的并发数据库查询数.
That would also let you "scale up" the number of concurrent DB queries in configuration.
在该处的代码中，我也看不到任何事务处理，因此您的实现可能会根据您的处理方式而有所不同.如果并行出现1个故障，是否所有更新/插入都会一起回滚?还是每个插入/更新都是自己的提交?
I also don't see any transaction handling in your code there, so your implementation might vary based on how you want to handle that. If there is a failure in 1 parallel, would all the updates/inserts roll back together? Or would each insert/update be its own commit?
这篇关于C#多线程(并行)使用锁访问静态MySQL连接{get; set;}的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
