When working on a project which uses a lot of database connections, I've been encountering a number of errors relating to timeouts when acquiring a connection from the pool using MSSQL.
As a matter of course, all the DB connectivity is in using blocks, so any connections should be cleared down properly when they are closed, however one persistent error comes up in a using block where a single line is read in a reader object, then a break statement is hit, as shown below.
using (SqlConnection connection = new SqlConnection(connectionString)) {
connection.Open();
using (SqlCommand command = new SqlCommand(sqlString, connection)) {
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read()) {
//Do something with the data then break
break;
}
}
}
}
The error is intermittent, but occurs on 4 out of every 5 test sessions I run on the system. I realise that leaving the using block should cause the connection to close cleanly and release it to the pool for reuse, however it is curious that this error comes up consistently in this one area.
After changing the read to remove the while and the break, I have not seen this issue occur again
using (SqlConnection connection = new SqlConnection(connectionString)) {
connection.Open();
using (SqlCommand command = new SqlCommand(sqlString, connection)) {
using (SqlDataReader reader = command.ExecuteReader()) {
if (reader.Read()) {
//Do something with the data
//No need to break, as the select uses a unique ID for the row
}
}
}
}
In order to further debug this issue, I need to work out which avenues to explore, and if the system failing to acquire a connection from the pool within the timeout period is simply a coincidence arising from the sheer number of DB connections, I will need to refactor several methods to change the behaviour.
Is the mechanism which causes the DB connection to be returned to the pool triggered by the using block exiting cleanly (i.e. running to the end brace), or by the scope changing?
