ADO.NET: tu conexion no se cierra?, el poder de using

Aunque en desarrollo using sólo será una instrucción, cuando hagas deployment verás el poder de using :).


Sobre todo cuando empiecen a tener este mensajillo: “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.


Como decía en uno los links, este mensaje principalmente se presenta cuando la web esta en producción, más no en desarrollo (ya que al detener la app, o el servidor virtual de asp.net, se reciclan todas las conexiones), pero cuando esta tu server, tendriás que reiniciar el servicio.


La causa de este problema (en mi escenario), es que las conexiones no se estaban cerrando como yo pensaba, la llamada al método Close(), nunca se daba.


Revisen esta entrada: Connection Pooling and the “Timeout expired” exception FAQ, pero más importante que revisarla, hagan el ejemplo. Cuando hagan el ejemplo revisen en el managment studio, las conexiones con el siguiente sp, SP_WHO:



   1: exec SP_WHO
   2: go

Verán como crece exorbitantemente el número de conexiones. Un ejemplo de los resultados es el siguiente pantallazo:



Como se puede apreciar todas las conexiones actuales a un servidor de base de datos, los login con nombre propio son de las estaciones de trabajo, se puede deteminar el patrón de quién esta generando conexiones de más, que ususario en que PC contra que base de datos, entre otras. Nótese que el spid, es el PrimaryKey del registro :D.


Otra herramienta de ayuda para diagnosticar en que parte del código o juego de consultas tenemos problemas con las conexiones, es usar el SQL Profiler:



Acá podemos determinar en que juego de columnas se están abriendo nuevas conexiones, cada consulta tiene un SPID, y junto con los resultados del SP_WHO, podemos buscar la raíz del problema.


Si tenemos problemas como el primer mensaje, la primera tarea sería identificar si estamos administrando bien las conexiones dentro de nuestra aplicación. Por cierto no es una solución, no en primera instancia, aumentar el número de conexiones máximas (modificando la propiedad Max Pool Size, que tiene un valor por defecto de 100), ya que cada vez van necesitar seguir aumentado más, este problema de escalabilidad con una buena administración de las conexiones no tiene porque dar mucha lata :D.


Ejemplo:



   1: using (OleDbConnection cnExcel = new OleDbConnection(strCnExcel))
   2: {
   3:    using (OleDbCommand cmd =
   4:                new OleDbCommand(“SELECT * FROM [Sheet1$]”, cnExcel))
   5:        {
   6:           cnExcel.Open();
   7:           using (OleDbDataReader reader = cmd.ExecuteReader())
   8:           {
   9:              //….
  10:           }
  11:        }
  12: }

P.D.: Using en general es usando para forzadamente liberar lo recursos usados, es por eso que comúnmente lo vemos ahora en el manejo de archivo, o en el uso de TransactionScope, y en general con cualquier objeto que implementa la interfaz IDisposable.


 Actualización (20070118): Revisar los comentarios y aclaraciones que hace Unai, con respecto al tema de la entrada.


Saludos,


Post cruzado

10 comentarios en “ADO.NET: tu conexion no se cierra?, el poder de using”

  1. Creo, en mi opinión que estás cometiendo un error de apreciacion, NO es cierto que Close no sea SUFICIENTE, de hecho es otro nombre para Dispose. El problema es que si pones using te garantizas que cuando el objeto IDisposable quede fuera de su ambito automáticamente su método Dispose será llamado mientras que si no lo pones por causa de alguna excepcion en la ejecución del comando nunca llegarías a llamar a Close. Pero el mismo resultado tendrías si usaras TRY CATCH FINALLY con un conn.Close en el FINALLY

  2. Deberías cambiar también la frase
    “La causa de este problema (en mi escenario), es por no cerrar correctamente las conexiones y los objetos relacionados a ellas (command, reader), el famoso close() no es suficiente.”

    Porque es aquí donde está el error, Close es igual de suficiente que Dispose

    Saludos
    Unai

  3. Unai, cuando decía que no es suficiente quize referirme a Close(), de por si sólo no es la mejor práctica, sólo basta probar el siguiente código:

    //—————————

    using System;

    using System.Data;

    using System.Data.SqlClient;

    public class Repro

    {  

    public static int Main(string[] args)

    {

       Repro repro = new Repro();

       for (int i = 0; i <= 5000; i++)

       {

         try{

           Console.Write(i+" ");

           repro.LeakConnections();

          }

         catch (SqlException){}

       }

       return 1;

    }

    public void LeakConnections()

    {          

       SqlConnection sqlconnection1 =

           new SqlConnection("Server=.\SQLEXPRESS ;Integrated security=sspi;connection timeout=5");

       sqlconnection1.Open();

       SqlCommand sqlcommand1 =

           sqlconnection1.CreateCommand();

       sqlcommand1.CommandText =

           "raiserror (‘This is a fake exception’, 17,1)";

       sqlcommand1.ExecuteNonQuery();  //this throws a SqlException every time it is called.

       sqlconnection1.Close(); //We are calling connection close, and we are still leaking connections (see above comment for explanation)

     }

    }

    //———————–

    En el código anterior Close(), no es suficiente, o agregamos el Using, o como bien mencionas usamos el try/catch/finally, pero no solo. Y esa sería la recomendación de esta entrada.

    Saludos,

  4. Creo que no me estás entendiendo. Sigo pensando que la frase no está correctamente redactada y da lugar a equívocos. El problema no está en que Close no sea suficiente como se da a entender, el problema es que no llegues a llamarlo, ese es el mensaje correcto que debería quedar.

    Siento ser tan pesado,y te pido disculpas, pero creo que es posible que mucha gente se confunda tal y como está puesto..

  5. Unai ya está, y hombre gracias a tí por ser pesado, fue la única manera de enteder lo que intentando decirme…

    Por este tipo de comentarios, es agradable estar por Geeks 😀

    Saludos,

  6. Estoy tenidndo el problema que se describe en este articulo, el escenario es el siguiente:
    una pagina aspx en l acual acceden al rededor de 30 personas de manera casi simultanea(diferencia de 2 a 3 segundos o menos) pero las conexiones no se cierran, yo uso el bloque try, para garantizar que se cierren pero nada. tambien he notado que las conexiones se cierran solas despued de 10 o 15 minutos.. alguna idea ?

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *