MySQL Threading Issues in my static DAL

Question

I'm using the MySQL .Net libraries in an older C# application I'm rewriting. The Data Access Layer is rather obsolete but I'm trying to make the best of it. But now I ran into some really nasty threading issues.

I have a series of about 20 Select statements which are used to process a report. They take about 5 seconds to complete and I'm displaying a progress bar while the Select statements run. I'm launching the operations via a simple ThreadPool call:

[LATER EDIT: What happens is that I called the method below twice due to a bug in my UI - this doesn't devalue the question though, merely explains why my threads were racing against each other.]

ThreadPool.QueueUserWorkItem(new WaitCallback(UpdateChart));

Sometimes it works.
Sometimes it crashes with a "possible IO stream race condition".
Sometimes it crashes with "connection should be open and valid".
Sometimes it crashes with "object reference not set...".

All classes in my DAL are Static because I thought this is a good way of improving performance (not having to create new class instances for every little operation).

And all my DAL classes use the same "root" DAL class which builds Connections:

public static class MySQLConnectionBuilder
{

private static MySqlConnectionStringBuilder ConnectionStringBuilder = new MySqlConnectionStringBuilder();

//I'm initializing the ConnectionStringBuilder with my server password & address.

public static MySqlConnection GetConnection ()
{
  return new MySqlConnection(ConnectionStringBuilder.ConnectionString);
}

}

All my DAL classes have functions which are similar to the crashing function. The crashing function looks like this:

public static STDS.UserPresence.user_presenceDataTable GetPresence (int aUserID, DateTime aStart, DateTime aEnd)
{
  ta.Connection = MySQLConnectionBuilder.GetConnection();
  ds = ta.GetPresenceForUserBetweenDates(aUserID, aStart, aEnd);
  ta.Connection.Close();
  return ds;
}

Ideas? Tips on improvement? Will the threading issue go away if I switch to a more object-oriented (instance-driven) DAL?

Whats so wrong with a 5 seconds delay, or is it 5 seconds per SQL Call, so 20 x 5? From whats posted I would stop and look at how you are making your Db connections and calls and do some research on ConnectionPooling and performance. — Lloyd
– Lloyd, Commented Mar 2, 2012 at 12:30
The MySQL library uses ConnectionPooling but it definitely doesn't work right in my multi-threaded situation. That's why I posted here, because I'm curious about possible solutions. As for 5 seconds: it's a long time if you click a button in a desktop application which should be super-fast. Not only that, but I expect this duration to go up to 20 seconds in the coming months, as more data gets into the database. — Axonn
– Axonn, Commented Mar 2, 2012 at 12:35
You are better off defining, initialising and using a new MySqlConnection in each static method, within a Using statement ie: using (MySqlConnection conn = new MySqlConnection(connectionString)) { // } and let the framework manage the pooling as it is designed to do, what you are doing simply creates its own mess. In your current methods if an exception is thrown how is the connection closed? — Lloyd
– Lloyd, Commented Mar 2, 2012 at 12:53
I tried "using (ta.Connection = WTCoreMySQLDB.GetConnection()) { select_statement_here() } " and I'm getting the exact same errors. — Axonn
– Axonn, Commented Mar 2, 2012 at 12:58
Do not reuse or reference a connection that is outside the current scope of the Method you are in. ie: ditch the ta.??? and Instantiate a new MySqlConnection every method call. — Lloyd
– Lloyd, Commented Mar 2, 2012 at 13:02

C.Evenhuis · Accepted Answer · 2012-03-02 12:41:36Z

1

The line

ta.Connection.Close()

Closes the connection last assigned to ta.Connection - not always the connection created in the same thread. This may close a connection on which a query is currently running in another thread.

If you want to quickly determine if this is what's happening, mark the connection variable with a [ThreadStatic] attribute in the class ta points to:

[ThreadStatic]
private static MySqlConnection connection;

I wouldn't use that approach for your final solution though, as it may cause the GC not to collect them.

A simple solution (for that problem, I can't determine if your classes have other multithreading issues) is to add the connection as a parameter to each of your DAL methods, allowing you to remove the class global Connection:

public static STDS.UserPresence.user_presenceDataTable GetPresence (int aUserID, DateTime aStart, DateTime aEnd)
{
  using (MySqlConnection connection = MySQLConnectionBuilder.GetConnection())
  {
      ds = ta.GetPresenceForUserBetweenDates(connection, aUserID, aStart, aEnd);
      return ds;
  }
}

Threading issues never simply go away - they require attention. If you are unsure about what's happening, forget about the slight performance boost (if a query takes 5 seconds, any possible performance gain of using static classes would be below 1% anyway).

edited Mar 2, 2012 at 12:41

answered Mar 2, 2012 at 12:34

C.Evenhuis

26.4k2 gold badges60 silver badges73 bronze badges

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

7 Comments

Axonn Over a year ago

I read about that ThreadStatic attribute in some other place here on SO as well. I'll try to see if it cures the issue. But overall, you'd recommend I dump my static classes and go for instances, right? One more thing, you said "add the connection as a parameter" but the code you modified uses the "using" statement instead. Is that what you meant?

Axonn Over a year ago

I tried, it doesn't work. I declared a global variable with ThreadStatic and then initialized it in the function like this: "MCon = WTCoreMySQLDB.GetConnection();" where MCon is the global variable. Then, I assigned MCon to the Table Adapter, called the method, closed MCon. Sometimes, I still get errors. It's not constant (like any threading situation hehe).

C.Evenhuis Over a year ago

The part that uses the using is where only a local reference to the connection is kept - other threads cannot access it. The connection is then passed to the GetPresenceForUserBetweenDates method, so that method does not have to access a global connection instance. Overall yes I wouldn't worry about creating instances, first create readable code that works, then find performance bottlenecks if that becomes a problem (those bottlenecks would then turn out to be the 20x5 second queries).

C.Evenhuis Over a year ago

Ahh ta is a table adapter - the Connection property of ta is still overwritten by other threads in that case - forget about [ThreadStatic] (I was just hoping it could quickly reveal the issue).

Axonn Over a year ago

Thank you for all the info ::- ). What I don't understand is why is this happening? I'm not using other threads in the same time. Only 1 function has been added in the ThreadPool and the application does NOTHING in the meantime. So I'm puzzled as to why these conflicts appear. Also: do you think that the "using" statement would fix this? Because if I'm not mistaken, your application of it is pretty much the same thing I've done a bit earlier (except I did it 1 class deeper) and didn't work: see my comment to Lloyd in my original post. LATER EDIT: OMG LOL! I'm calling the ThreadPool twice!

|

Collectives™ on Stack Overflow

MySQL Threading Issues in my static DAL

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related