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?

Julien Lebosquain · Accepted Answer · 2009-10-05 10:39:39Z

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.

answered Oct 5, 2009 at 10:39

Julien Lebosquain

41.4k9 gold badges108 silver badges120 bronze badges

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

Comments

jerjer · Accepted Answer · 2009-10-05 10:54:16Z

1

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.

edited Oct 5, 2009 at 10:54

answered Oct 5, 2009 at 10:48

jerjer

8,77634 silver badges36 bronze badges

Comments

LukeH · Accepted Answer · 2009-10-05 10:59:41Z

0

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.

answered Oct 5, 2009 at 10:59

LukeH

271k59 gold badges373 silver badges411 bronze badges

Collectives™ on Stack Overflow

Multi-threaded access to MySQL using ADO.Net

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related