none
Is DataContext ExecuteQuery really safe and no Dispose is needed ? RRS feed

  • Question

  • Hi.

    Our crash repeated few times in a log system. After few hours application crashed without giving us any Fatal exception provided - nothing was logged. Some strange "Faulting application kernel32... bla bla bla" could have been observed in windows Event Log.

    After some time we have started asking ourself about possible SQLConnection leaks in direct database and DataContext-Linq invocations. 

    After checking all direct SqlConnection/Command/Reader code places and making sure there are relevant "using" statements added we have added performance counter monitoring. 

    After startup application counters were giving us quite nice zero values mostly (NumberOfReclaimedConnections=0). After few hours our conter informations started to be different. One of our last logs before application failure reffering to high reclaimed connections value (NumberOfReclaimedConnections=190)

    NumberOfActiveConnectionPools=2,NumberOfReclaimedConnections=190,HardConnectsPerSecond=0,
    HardDisconnectsPerSecond=0,NumberOfActiveConnectionPoolGroups=2,NumberOfInactiveConnectionPoolGroups=0,
    NumberOfInactiveConnectionPools=0,NumberOfNonPooledConnections=0,NumberOfPooledConnections=8,NumberOfStasisConnections=0,
    SoftConnectsPerSecond=6.705363,SoftDisconnectsPerSecond=6.705307,NumberOfActiveConnections=1,NumberOfFreeConnections=7

    After long investigations and checkings we have found a way to repeat problem quickly. Following code was causing our connections to be handled in bad way (this is what I understand from documentations talking about this NumberOfReclaimedConnections meaning):

    OurAppDataContext ctx = new OurAppDataContext();
    ctx.ExecuteQuery("DROP TABLE " + databaseName + ".dbo.[" + tableName + "]", "");

    We have quickly found that acutually no records reading was needed in thsi case so quickly changed this code to the folowing:

    OurAppDataContext ctx = new OurAppDataContext();
    ctx.ExecuteCommand("DROP TABLE " + databaseName + ".dbo.[" + tableName + "]", "");

    our application stopped to have connections reclaimed at all. We have spent next few hours to make sure that this was acutally our problem and found that this ExecuteQuery was causing it.

    Is it true than that such way of working with Linq needs direct Dispose or using execution (This context stuff made in designer) is unsafe or am I doing something wrong ?

    Platform: c# .net 4.0 Full, SQL Server 2008 Dev edition,
    • Changed type pijaw Friday, February 25, 2011 4:25 PM
    • Changed type pijaw Saturday, February 26, 2011 1:48 PM
    Friday, February 25, 2011 4:09 PM

Answers

    • either DataContext must be closed/disposed explicty - because we have sample that shows it may cause sql connection leaks (leading to application timeouts etc..)
    • or DataContext does not need to be closed/Disposed - because (?)

     

    Look, as I said previously, normally you don't need to handle this because the implementation is good enough but in certain cases you might want to handle it yourself.

    This is not a DataContext issue, is a GC issue and how the GC works at releasing resources, meaning it does not do this right away.

    Perhaps my english is not very good as well but my point is this.

    • If you need the resources right away then close/dispose explicitly of the DataContext or force the GC to do it.
    • If you don't need the resources right away then let the GC do its work and the resources will be freed later on.

    Regards

    • Marked as answer by pijaw Tuesday, March 8, 2011 9:30 AM
    Monday, March 7, 2011 1:21 PM

All replies

  •  

    Hi pijaw,

    Thanks for your post.

    We can distinguish the two methods  with their names. DataContext.ExecuteQuery executes SQL queries directly on the database and returns objects.( http://msdn.microsoft.com/en-us/library/bb361109.aspx ) but DataContext.ExecuteCommand executes SQL commands directly on the database( http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.executecommand.aspx). Let's see them in reflector:

    public int ExecuteCommand(string command, params object[] parameters)
    {
       
    this.CheckDispose();
       
    if (command == null)
        {
           
    throw Error.ArgumentNull("command");
        }
       
    if (parameters == null)
        {
           
    throw Error.ArgumentNull("parameters");
        }
       
    return (int) this.ExecuteMethodCall(this, (MethodInfo) MethodBase.GetCurrentMethod(), new object[] { command, parameters }).ReturnValue;
    }

    public IEnumerable<TResult> ExecuteQuery<TResult>(string query, params object[] parameters)
    {
       
    this.CheckDispose();
       
    if (query == null)
        {
           
    throw Error.ArgumentNull("query");
        }
       
    if (parameters == null)
        {
           
    throw Error.ArgumentNull("parameters");
        }
       
    return (IEnumerable<TResult>) this.ExecuteMethodCall(this, ((MethodInfo) MethodBase.GetCurrentMethod()).MakeGenericMethod(new Type[] { typeof(TResult) }), new object[] { query, parameters }).ReturnValue;
    }

    They all invoke ExecuteMethodCall with the different method return value.

    I think you may use parameters instead of contact strings and you may use ExecuteCommand method to excute the command  instead of ExecuteQuery method.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 28, 2011 3:41 AM
    Moderator
  • yo, 

    I think your answer does not exactly touch my problem of unproperly released dataContext connections (I know I have messied it up a little bit). 

    For this reason I have prepared small code sample. Just copy it, run and let me know what is the console output you have (you will need to wait 30 seconds).

    It is strange that altough all documentations claim that DataContext is safe (automatically releases connecitons) in this case performance counter says I had 38 connections unproperly released.

     

    using System;
    using System.Data.Linq;
    using System.Diagnostics;
    using System.Threading;
    
    namespace ConsoleApplication2
    {
     class Program
     {
      static void Main(string[] args)
      {
       for (int i = 1; i < 60; i++)
       {
        MyOperation op = new MyOperation();
        op.DoWork();
        Thread.Sleep(500);
       }
    
       string instanceName = System.Reflection.Assembly.GetEntryAssembly().GetName().Name + "[" + Process.GetCurrentProcess().Id + "]";
       PerformanceCounter counter = new PerformanceCounter(".NET Data Provider for SqlServer", "NumberOfReclaimedConnections", instanceName);
       Console.WriteLine(counter.NextValue());
      }
    
      public class MyOperation
      {
       public void DoWork()
       {
        DataContext dctx = new DataContext("Data Source=.\\SQLServer2008;Integrated Security=True");
        dctx.ExecuteQuery(typeof(int), "SELECT Count(*) FROM sys.objects", "");
       }
      }
     }
    }
    
    

    In following materials:

    • The same forum thread: http://social.msdn.microsoft.com/Forums/en/linqtosql/thread/44bffc3c-1a18-427e-abfe-10ca15a9a1ac  Matt Warren says : "The DataContext always uses the same connection, it just opens and closes it for each operation. This is normally efficient since the connections are pooled. If you want to force it to stay open across multiple operations you can open it manually. The DataContext will then keep it open until you close it yourself. In addition as long as you have an ongoing transaction the DataContext will not close the connection"
    • MSDN documentation: http://msdn.microsoft.com/en-us/library/ms254503(v=vs.80).aspx they say "NumberOfReclaimedConnectionsThe number of connections that have been reclaimed through garbage collection where Close or Dispose was not called by the application. Not explicitly closing or disposing connections hurts performance."
    So again my question is 

    Is DataContext really safe and no direct Close or Dispose invocation is needed ? Isn't it something wrong that connections are not closed well and than reclaimed through garbage colletor ?


    Piotr Jaworski kk-electronic.com
    • Edited by pijaw Monday, February 28, 2011 8:31 AM post view modifications only
    Monday, February 28, 2011 8:30 AM
  • Hmm, it might take a while for the Garbage Collector to reclaim those resources (if you read the documentation on how this work and how objects are promoted you will get added insight into this).  If your application is time critical and need those resources you can force this by either manually doing this or manipulating the GC into disposing your objects.

    Regards

    Tuesday, March 1, 2011 8:32 PM
  • Hi pijaw,

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions?  
     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, March 7, 2011 3:30 AM
    Moderator
  • Hi. Alan

    Thanks for your care.

    However a straight clear answer to question put in subject is not provided (or my english understanding is insufficient). What I expect is that someone will confirm that:

     

    • either DataContext must be closed/disposed explicty - because we have sample that shows it may cause sql connection leaks (leading to application timeouts etc..)
    • or DataContext does not need to be closed/Disposed - because (?)

     


    Piotr Jaworski kk-electronic.com
    • Edited by pijaw Monday, March 7, 2011 11:38 AM spelling corrections :-)
    Monday, March 7, 2011 11:37 AM
    • either DataContext must be closed/disposed explicty - because we have sample that shows it may cause sql connection leaks (leading to application timeouts etc..)
    • or DataContext does not need to be closed/Disposed - because (?)

     

    Look, as I said previously, normally you don't need to handle this because the implementation is good enough but in certain cases you might want to handle it yourself.

    This is not a DataContext issue, is a GC issue and how the GC works at releasing resources, meaning it does not do this right away.

    Perhaps my english is not very good as well but my point is this.

    • If you need the resources right away then close/dispose explicitly of the DataContext or force the GC to do it.
    • If you don't need the resources right away then let the GC do its work and the resources will be freed later on.

    Regards

    • Marked as answer by pijaw Tuesday, March 8, 2011 9:30 AM
    Monday, March 7, 2011 1:21 PM
  • Thanks.

    I have experimented with code (increased counter, removed Sleep and added exception catch) and than found timeouts quickly coming.

     

    using System;
    using System.Data.Linq;
    using System.Diagnostics;
    using System.Threading;
    
    namespace ConsoleApplication2
    {
      class Program
      {
        static void Main(string[] args)
        {
          for (int i = 1; i < 200; i++)
          {
            MyOperation op = new MyOperation();
            op.DoWork();
          }
    
          string instanceName = System.Reflection.Assembly.GetEntryAssembly().GetName().Name + "[" + Process.GetCurrentProcess().Id + "]";
          PerformanceCounter counter = new PerformanceCounter(".NET Data Provider for SqlServer", "NumberOfReclaimedConnections", instanceName);
          Console.WriteLine(counter.NextValue());
        }
    
        public class MyOperation
        {
          public void DoWork()
          {
            DataContext dctx = new DataContext("Data Source=.\\SQLSERVER2008;Integrated Security=True");
            dctx.ExecuteQuery(typeof(int), "SELECT Count(*) FROM sys.objects", "");
          }
        }
      }
    }
    
    

     

    After putting small change where added "GC.Collect()"  (not so nice I think :-) )

          for (int i = 1; i < 200; i++)
          {
            MyOperation op = new MyOperation();
            op.DoWork();
            GC.Collect();
          }
    

    Timeouts do not occur any more !

    So you are right!. This is more a matter of Garbage Collection. 

    I don't know how other applications use databases (maybe my one does it too extensively) but case of this app has proven to me that all theories regarding safe database access without need of explicit Close/Dispose/using statements can be send to scrap yard :-)

    Thanks for explanation. Regards !


    Piotr Jaworski kk-electronic.com
    Tuesday, March 8, 2011 9:28 AM
  • I confirm that also calling "dctx.Dispose()" drops NumberOfReclaimedConnections counter to zero. 

    Thank you everyone, this helped me a lot.

    Friday, November 29, 2019 12:43 AM