SQLServerInfo

E’ uno degli errori più comunemente rilevati nelle applicazioni .NET e riguarda l’esaurimento del pool di connessioni verso il database.

Di solito questo errore si verifica perchè nell’applicazione vengono chiusi gli oggetti usati per leggere o scrivere i dati (ad esempio un DataReader), ma non viene chiusa la corrispondente connessione.

In molte applicazioni mi è capitato di assistere a soluzioni empiriche come aumentare il numero di connessioni disponibili nel pool, ma come è facilmente intuibile non si tratta di una vera e propria soluzione, ma solo un rinvio del problema.

Il messaggio di errore riportato dall’applicazione è il seguente:

.NET 1.1

System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.
This may have occurred because all pooled connections were in use and max pool size was reached.
at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()

.NET 2.0

System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.
This may have occurred because all pooled connections were in use and max pool size was reached.
     at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()

Il codice per l’accesso ai dati dovrebbe seguire la regola “toccata e fuga”, per cui il blocco di codice apro la connessione/eseguo il comando/chiudo la connessione è il più breve possibile: acquisire una nuova connessione dal pool è un’operazione veloce e poco costosa: è quindi preferibile mantenere la connessione aperta solamente per il tempo necessario all’esecuzione del comando e chiuderla immediatamente dopo.

Un esempio per riprodurre l’errore:

static void Main(string[] args)
{
   string connectionString = @"Data Source=<your server name or address>;Initial Catalog=Northwind;Integrated Security=True; Max Pool Size=20; Connection Timeout=10";

   try
   {
      for (int i = 0; i < 50; i++)
      {
         // Crea la connessione ed il comando da eseguire
         SqlConnection conn = new SqlConnection(connectionString);
         SqlCommand cmd= new SqlCommand("SELECT * FROM Shippers", conn);

         // Apro la connessione
         conn.Open();
  
         // Debug
         Console.WriteLine("Numero di connessioni aperte: {0}", i.ToString());

         // Questa istruzione causa l’errore
         SqlDataReader dr = sCmd.ExecuteReader();

         dr.Close();

         // Soluzione 1: Chiudo la connessione subito dopo la lettura dei dati
         // utilizzando l’opzione CloseConnection del comando ExecuteReader
         //SqlDataReader dr = 
               cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        //dr.Close();
 
        // Soluzione 2: chiudo esplicitamente la connessione
        //conn.Close();
      }

     // Soluzione 3: eseguo tutti i comandi all’interno di un blocco using
     // for (int i = 0; i < 50; i++)
     //{
     //   using (SqlConnection conn = new SqlConnection(connString))
     //   {
     //      SqlCommand cmd= new SqlCommand("SELECT * FROM Shippers", conn);
     //      conn.Open();
     //      Console.WriteLine("Numero di connessioni aperte " + i.ToString());
     //      SqlDataReader sdr = cmd.ExecuteReader();
     //      dr.Close();
     //   }
     //}
     }
     catch (Exception e)
     {
         Console.WriteLine(e);
     }
}

In questo scenario, la connessione viene aperta ed utilizzata per popolare il datareader, ma non viene mai chiusa.
Tra l’altro il pool viene limitato a 20 connessioni con 10 secondi di timeout, mentre il default è di 100 connessioni con 15 secondi di timeout.

Le soluzioni possibili sono tre:

1. Uso l’opzione CommandBehavior.CloseConnection per chiudere la connessione immediatamente dopo aver letto i dati
2. Chiudo manualmente la connessione dopo aver terminato la lettura
3. Racchiudo il blocco di codice per la lettura dei dati all’interno di un blocco using.
   In questo modo si è sicuri che la connessione viene chiusa e distrutto l’oggetto in memoria.

La documentazione riguardo al connection pooling e ad alcuni suggerimenti di programmazione li trovate ai links seguenti:

".NET Framework Class Library - SqlConnection ConnectionString Property "
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

".NET Framework Class Library - CommandBehavior Enumeration"
http://msdn.microsoft.com/en-us/library/system.data.commandbehavior.aspx

"C# Language Reference - using Statement (C# Reference)"
http://msdn.microsoft.com/en-us/library/yh598w02.aspx

"Connection Pooling for the .NET Framework Data Provider for SQL Server"
http://msdn2.microsoft.com/en-us/library/8xx3tyca(VS.80).aspx

"The .NET Connection Pool Lifeguard"
http://msdn2.microsoft.com/en-us/library/aa175863(SQL.80).aspx