Multi-threaded access to MySQL using ADO.Net

Question

I'm developing an ASP.Net web application which makes use of MySQL via the MySQL-Provided Connector/NET, which is an ADO.Net-compatible interface. My understanding of ASP.Net is that concurrent requests will be processed in separate threads, so my code will need to be thread-safe.

I can't find anything in the MySQL documentation that explains unequivocally how to use the API in a thread-safe manner. The documentation on MySqlCommand says:

Public static (Shared in Visual Basic) members of this type are safe for multithreaded operations. Instance members are not guaranteed to be thread-safe

My literal understanding of this is that it's safe to share the connection object between threads, provided I don't call non-static methods concurrently. In other words, I ought to be able to do:

IDbConnection myConnection = GetSomeConnection(); // Returns a shared object
IDbCommand cmd;

lock(myConnection)
{
    cmd = myConnection.CreateCommand();
}

// Some code using cmd

However, I'm concerned that I haven't yet found documentation stating that this is safe, only a lack of documentation stating it is unsafe.

If this is unsafe, is the recommended approach to have thread-local connections, or should I implement some sort of connection pool?

Answer 1

Use a IDbConnection just when you need it and dispose it as soon as your done with your SQL queries. The connection will return to a connection pool and will be reused by another thread the next time you open a connection with the same connection string, even if it's another thread. No need to do any locking here.

Answer 2

using statement may help you:

using(IDbConnection myConnection = GetSomeConnection()){  
   IDbCommand cmd = myConnection.CreateCommand();  
}

The connection will be disposed automatically and will return to the connection pool.

Answer 3

Don't share your connections across threads. Keep your connection objects thread-local, open them as late as possible and close them as early as possible (preferably with a using block).

using (MySqlConnection conn = new MySqlConnection("..."))
{
    // do work
}

As far as I'm aware, the MySql Connector supports connection pooling and it's enabled by default, so just let the Connector worry about managing the connection pool.