Multithreading and database connection(s)

Question

Hey all-

So I'm trying to figure out best practices on my database connection. I have a large .NET GUI that serves as the front end for the MySQL db. Currently I open a connection on application load and use it for whatever interactions I need. However, the entire GUI is single-threaded.

As I start to add BackgroundWorkers for large queries and executes I'm concerned about my open connection. I know, for example, that I can only have one dataReader at a time open on that connection. With multiple threads, the user could try to instantiate more than that.

What are the advantages / disadvantages of keeping one open connection for the application vs opening a new connection for every interaction?

What are some common design patterns for this?

Thanks-

Jonathan

Answer 1

Use a thread-safe connection pool and keep connections thread-specific (don't share connections across threads).

I believe the MySQL .NET connection framework comes with one built in. If you use the same connection string for all connections, simply add "pooling=true" to your connection string. (Source -- there's no hyperlink fragment, so look for "pooling" in the table)

The drawback of this approach is that some threads will block until a connection is available. You'll need to account for this in your program structure.

Answer 2

With one open connection you should have maximal throughput with the database. However you end up writing complex code in your application to share the connection.

Here is an article on resource sharing patterns. The examples are in Ada but I'm sure you can read it anyway.

Answer 3

Connecting to a data source can be time consuming. To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling, which minimizes the cost of repeatedly opening and closing connections. Connection pooling is handled differently for the .NET Framework data providers.

from MSDN.

see here Connection Pooling (ADO.NET)

Answer 4

There are 2 ways:

1. Single connection - in this case, the connection is created 1 times and is divided among all requests, that if a large number of simultaneous requests leads to loss of productivity and you must control thread saving by youself.

2. Connection per request - in this case, you must open connection and close it immediately after executing the request. But the number of simultaneously open connections can be limited by the server. Here, the overhead of creating connections are always present, but are solved using thread-safe connection pooling, which was good practice.

You need to analyze and predict the behavior of your application and select the appropriate path. If you have a simple application, you probably will not need to use the pools. If the application requires a serious load and future scalability, it would be correct to use the pools.

Answer 5

We have done some test before deciding which way to go. I mean between ConnectionAlwaysOpen or ConnectAndDisconnectEachTime.

Apparently from .NET with SQL Server (never tried with MySQL) there were no visible performance penalty in either of approaches (nothing surprising, since the lower layers do not really disconnect immediately in ConnectAndDisconnectEachTime scenario). But there was a subtle difference that made us decide for ConnectionAlwaysOpen. The reason was Transaction support. If you intend to use transactions then the connect/disconnect will NOT work, I think it obviously why.

ConnectionAlwaysOpen of course can be improved using existing connection pools or some manual lazy cache of connection pools, but the basic idea stays the same. On the other hand, in Web application it is a little harder to implement and thread-safe this approach, but it might worth the effort.