C# Multiple threads (Parallel) accessing static MySQL Connection {get;set;} with locks

Question

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;
    }

Here is my Parallel

Parallel.ForEach(urlTable.AsEnumerable(), drow =>
        {
            WebSiteCrawlerClass WCC = new WebSiteCrawlerClass();
            if (drow.ItemArray[0].ToString().Contains("$"))
            {

                WCC.linkGrabberwDates(drow.ItemArray[0].ToString(), "www.as.com");
            }
        });

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");

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.

I would prefer not to create 10+ connections to the server, so my question is this.

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

CodingWithSpike · Accepted Answer · 2012-04-03 15:21:35Z

2

IMO accessing a single static connection in parallel and trying to manage the lock yourself seems like a bad design.

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.

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?

answered Apr 3, 2012 at 15:21

CodingWithSpike

43.9k18 gold badges106 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user222427 Over a year ago

Each is it's own commit, i understand your point about the single static connect, but if feel it's unnecessary to have more than 1 connection. However, I like the idea of locking just waiting for the command to end before the next one starts.

user222427 Over a year ago

why would I not what a static connection here, I don't want it to change, and I also only one to use the one connection

Collectives™ on Stack Overflow

C# Multiple threads (Parallel) accessing static MySQL Connection {get;set;} with locks

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related