1

I have a strange problem. I have a simple application which copies some rows from a view in a sql server database to a table in another sql server database. It first check count of both source view and destination table, and if they differs, fetch data. This process re-occurs indefinitely using a timer.

The strange thing is, after some iterations, the result freeze and query return same old value on each run. But, if I close and reopen the application, the query return updated results.

I can't understand why this should happen. I am using LINQ to SQL. I've tried everything, convert linq db context to local variables, turn Object Tracking enabled or disabled, used transactions, but none of them works. Here is a code snippet of one of my functions (some casts and not needed codes are removed):

    private void DoEvals()
    {
        [...]

        dbDataContext db = new dbDataContext { ObjectTrackingEnabled = true };
        localDataContext localdb = new localDataContext { ObjectTrackingEnabled = true };

        List<Evaluation> evals = new List<Evaluation>();
        try
        {
            var countOnline = (from x in db.Leads where x.StateCode == 0 select x).Count();
            var countOffline = (from x in localdb.Evaluations select x).Count();

            if (countOnline == countOffline)
            {
                return;
            }

            int stepCount = 500;

            int steps = countOnline / stepCount;

            for (int currentStep = 0; currentStep <= steps; currentStep++)
            {
                var q = (from l in db.Leads
                         where l.StateCode == 0
                         select l);

                q = currentStep != steps ? q.Skip(currentStep * stepCount).Take(stepCount) : q.Skip(currentStep * stepCount);

                evals.AddRange(q.ToList());
            }

        }
        catch (Exception exc)
        {
        }

        [...]
    }

Do you have any idea on why this is happening? I have tried this application in two environments (remote view, local table - local view, local table), so I don't think this is because of caching in network nodes.

Shahab
  • 794
  • 2
  • 12
  • 23
  • This is strange... Might be an uncommitted transaction if you are using optimistic locking? – Shnugo Aug 24 '15 at 08:19
  • @Shnugo Yes, I am totally confused. I've used linq to sql from day one and I've never seen such thing before. No transactions used and I re new db context each time. – Shahab Aug 24 '15 at 09:41
  • I don't use linq-to-sql, but I'd assume there are implicit transactions. Some more questions: What version of sql-server (please edit the tag too!)? Which locking level? Have you monitored for transactions? What happens, if you try to get the same data with a SQL call directly? – Shnugo Aug 24 '15 at 09:46
  • @Shnugo, I am using SQL Server 2014. Yes, there is implicit transactions in linq. But the problem is this happen just after some time! So I can't debug it easily or monitor queries, I don't know when it happens! It just happen at some point. I don't know how to check locking level? – Shahab Aug 24 '15 at 10:03
  • Isolation or transaction level: look here: http://stackoverflow.com/questions/1038113/how-to-find-current-transaction-level – Shnugo Aug 24 '15 at 10:12
  • @Shnugo it is `ReadCommitted` – Shahab Aug 24 '15 at 10:26
  • What happens if you try to get the same data with a sql call directly (at the same time of course... – Shnugo Aug 24 '15 at 11:18
  • @Shnugo I think this would work fine, because just by restarting my Application, query return correct dataset. So if I run that query using anything else, this should also work fine. I think this should be something instance related or a signature thing for LINQ to SQL. But I'll try that anyway after seeing error again. – Shahab Aug 24 '15 at 11:23
  • I see your point (which is probably true), but "should" is not enough in such strange situations :-) – Shnugo Aug 24 '15 at 11:27
  • @Shnugo sure :) I'll test it as soon as I get same error. Currently I am working on it and waiting for error to occur. – Shahab Aug 24 '15 at 11:54

1 Answers1

0

You need to dispose of each database context. Also, never eat an Exception.

using (dbDataContext db = new dbDataContext())
using (localDataContext localdb = new localDataContext())
{
   // do your stuff
}
Richard Schneider
  • 34,944
  • 9
  • 57
  • 73