C# Mysql and multiple threads?

Question

I am hosting a WCF service which uses the mysql connector. For some reason once every 5-10 minutes I get an error that mysql is already in use. What should I do? Set it up so that it blocks, have it create a new connection per thread or setup a manager which creates a set number of connections and will block until a connection becomes available. Actually do the asynchronous calls already do that last part? Lastly whats the best way to block until another thread completes and get information from that thread?

public static int Query(this IDbConnection olddb, string query, params object[] args)
{
    using (var db = olddb.CloneEx())
    {
        db.Open();
        using (var com = db.CreateCommand())
        {
            com.CommandText = query;
            for (int i = 0; i < args.Length; i++)
                com.AddParameter("@" + i, args[i]);

            return com.ExecuteNonQuery();
        }
    }
}

Unhandled Exception: System.NotImplementedException: The requested feature is not implemented.
  at MySql.Data.MySqlClient.MySqlConnection.EnlistTransaction (System.Transactions.Transaction transaction) [0x00000] in :0
  at MySql.Data.MySqlClient.MySqlConnection.Open () [0x00000] in :0

Darin Dimitrov · Accepted Answer · 2011-07-28 22:16:05Z

15

Everytime you need to query your SQL server simply:

using (var conn = new MySqlConnection("Some connection string"))
using (var cmd = conn.CreateCommand())
{
    conn.Open();
    cmd.CommandText = "SELECT foo FROM bar";
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            // process the results
        } 
    }
}

This ensures that connections are returned into the connection pool that ADO.NET manages for you and so that you don't get any risks of creating multiple connections that could slow things up. Also this code is perfectly reentrant and thus by definition thread safe => it could be executed concurrently from as many threads as you wish.

answered Jul 28, 2011 at 22:16

Darin Dimitrov

1.0m276 gold badges3.3k silver badges3k bronze badges

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

5 Comments

Will Over a year ago

I don't think creating a connection for ever query is a good idea.

Darin Dimitrov Over a year ago

@High, this doesn't create a connection for every request. It draws one from the connection pool that ADO.NET manages for you. And at the end of the using statement this connection is simply returned to the connection pool so that it can be reused. In terms of performance that will be the fastest possible way. Read the following topic for more information about connection pooling: dev.mysql.com/doc/refman/5.0/en/…

Will Over a year ago

Ah, thank you for the information. Had no idea the connector worked that way.

Darin Dimitrov Over a year ago

@High, yes it works that way. SQL Server also works that way. In fact when you run the application for the first time ADO.NET creates multiple connections to the database and stores them inside a connection pool (this pool is per connection string per application domain). So when you write conn.Open() you are not opening a new connection you are taking one that already exists from the pool and at the end of the using statement the connection is returned to the pool. It's very efficient.

Will Over a year ago

Hey it seems that it throws an exception when I try to open 2 at once. Adding the code/exception above.

Collectives™ on Stack Overflow

C# Mysql and multiple threads?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related