C # MySQL connection pool limits and flushing connections

Question

I have a simple DB manager class (a larger name than it deserves):

class DbManager { private MySqlConnectionStringBuilder _connectionString; public DbManager() { _connectionString = new MySqlConnectionStringBuilder(); _connectionString.UserID = Properties.Database.Default.Username; _connectionString.Password = Properties.Database.Default.Password; _connectionString.Server = Properties.Database.Default.Server; _connectionString.Database = Properties.Database.Default.Schema; _connectionString.MaximumPoolSize = 5; } public MySqlConnection GetConnection() { MySqlConnection con = new MySqlConnection(_connectionString.GetConnectionString(true)); con.Open(); return con; } } 

Then I have another class in another place that represents the entries in one of the tables, and I populate it like this:

 class Contact { private void Populate(object contactID) { using (OleDbConnection con = DbManager.GetConnection()) { string q = "SELECT FirstName, LastName FROM Contacts WHERE ContactID = ?"; using (OleDbCommand cmd = new OleDbCommand(q, con)) { cmd.Parameters.AddWithValue("?", contactID); using (OleDbDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { reader.Read(); this.FirstName = reader.GetString(0); this.LastName = reader.GetString(1); this.Address = new Address(); this.Address.Populate(ContactID) } } } } } } class Address { private void Populate(object contactID) { using (OleDbConnection con = DbManager.GetConnection()) { string q = "SELECT Address1 FROM Addresses WHERE ContactID = ?"; using (OleDbCommand cmd = new OleDbCommand(q, con)) { cmd.Parameters.AddWithValue("?", contactID); using (OleDbDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { reader.Read(); this.Address1 = reader.GetString(0); } } } } } } 

Now I thought that all using statements guarantee that the connections will be returned to the pool as they are finished, ready for the next use, but I have a loop that creates hundreds of these contacts and populates them, and it seems that the connections are not released.

The connection, command, and reader are in their own using .

Answer 1

If the application is multithreaded, you are potentially going to say that 10 threads are running at the same time. Each of them needs its own connection, but if you limit the pool size to 5, then you, the 6th thread, cannot get the connection from the pool.

You may be limiting your flows in some way, but I would suggest that you significantly increase the size of your application pool to make sure that you have more connections available than you can have threads. As an indicator, the default size (which is usually good enough for most people) is 100.

Also, if you have any recursion inside your using block (e.g. calling populate again), as you pointed out in the comments, unlike the code above, you will run into additional problems.

If you call populate inside the use block, then you will have the connection from the parent open and usable (therefore it cannot be reused), and then the child call will open another connection. If this happens just a few times, you will not have a dedicated connection.

To prevent this, you want to move the secondary call to populate from the use block. The easiest way is not to iterate over your recordset to fill in each identifier, to add identifiers to the list, and then after you have closed your connection and then filled in all new identifiers.

Alternatively, you can lazily evaluate things like address. They stored the addressID in a private field, and then made the address "Property", which checks if its support field (and not the address identifier) ​​is filled, and if not, it looks through it with the AddressID. This has the advantage that if you never look at the address, you do not even make a database call. Depending on the use of the data, this can save you a lot of hits on the databases, but if you definitely use all the details, then they just change them, potentially spreading them a little more, which can help in performance or maybe just doesn't make any difference at all,:)

In general, with access to the database, I try to simply extract all the data and close the connection as soon as I can, preferably before deleting any complex calculations on the data. Another good reason is that depending on your query to the database, etc. You can potentially hold locks on tables that you access with your queries, which can cause lock problems on the database side.

Answer 2

I would suggest a couple of changes:

1) Explicitly close the connection before using. Looking through the source code of OleDbConnection (and DbConnection and DbConnectionInternal), I believe that the connection is clearly not closed for deletion, it is simply abandoned.

2) Modify Address.Populate to accept the connection parameter, so you do not need to create two open connections when you need only one, and transfer the open connection from the contact. If there are cases where Address.Populate will be called when the connection is not yet available, you can create a version of Populate in Address overflow that opens the connection before calling the Populate overflow with the connection object. Update with Chris's suggestion . I made the assumption that people will know to close the open reader in this case, but this is not necessarily the case. If this method is used, any open reader must be explicitly closed before passing the open connection to the method.

Update

Just a confirmed offer number 1. From the MSDN Documentation :

"If the DbConnection goes out of scope, it is not closed, so you must explicitly close the connection by calling Close or Dispose, which are functionally equivalent. If the pool connection pool is set to true or yes, this also releases the physical connection."