Multi threaded data access issues

Harvey Saayman

Hey guys, I'm not sure if this is the right place to post cause I'm not sure of my issue is caused by SQL or Linq I've hit a brick wall with my coding and I have no idea how to proceed. I've been coding my calculation routine for the time and attendance package I've been working on for the past year. I just recently got to the point where i can actually execute the code and start testing it. But something very weird is happening. But first a little background info... The calculation logic all sits in a class named EmployeeCalculationClass, i pass the unique employee ID to it when I create an instance of this class. So each instance of EmployeeCalculationClass will calculate the data for a single employee. When the calculation command is received by the calculation program I query the db to get a list of all the employees, I then foreach this list, create an instance of EmployeeCalculationClass for each one and then i call the CalcDate() method for each one in its own thread using Smart Thread Pool[^] Here is the code for that part

private void StartCalculation()
{
try
{
SmartThreadPool smartThreadPool =
new SmartThreadPool(60 * 1000, 20, 1); // Timeout in ms, Max threads, Min Threads;

    CalcInProgress = true;

    base.triggerSendCommand("Calculation Started");

    using (EmployeeDataContext ctx = new EmployeeDataContext(GlobalMethods.SqlConnectionString))
    {
        var result = from items in ctx.Employees
                     select items.EmployeeID;

        EmployeeCalculationClass ecc;

        DateTime calcStartTime = DateTime.Now;

        foreach (long item in result)
        {
            ecc = new EmployeeCalculationClass(item);
            smartThreadPool.QueueWorkItem(new WorkItemCallback(ecc.CalcData));
        }

        //Tell the thread pool to wait for all the threads to finish before continueing
        smartThreadPool.WaitForIdle();

        DateTime calcStopTime = DateTime.Now;

        base.triggerSendCommand("Calculation Completed --> Time taken: " + (calcStopTime - calcStartTime).ToString());

        CalcInProgress = false;
    }
}
catch (Exception ex)
{
    GlobalMetho

Howard Richards

We can't see whats happening in the Calc class so that is probably where the error might be. You're not trying to share the datacontext among threads which might be risky.. One suggestion is that the 'result' variable is IQueryable (since you didn't force the query using .ToArray or .ToList). When you enter the loop it holds open a datareader to read each item in turn from the result. Try changing this to:

var result = (from items in ctx.Employees
select items.EmployeeID).ToList();

This runs the query at the start and might solve the problem.

'Howard

Harvey Saayman

Howard Richards wrote:

We can't see whats happening in the Calc class

For two reasons 1) its a few thousand lines of code 2) I'd get fired for putting intellectual property in the public domain :laugh: I can however tell you that each thread does ALOT of queries.

Howard Richards wrote:

You're not trying to share the datacontext among threads

What exactly do you mean by this? Every instance of the EmployeeCalculationClass does the same thing, but each thread only calculates a specific employees data, which is defined when passing the unique employeeID at creation of each instance, so naturally the same DataContextClass is being used by all the threads at the same time.

Harvey Saayman - South Africa Software Developer .Net, C#, SQL you.suck = (you.Passion != Programming & you.Occupation == jobTitles.Programmer) 1000100 1101111 1100101 1110011 100000 1110100 1101000 1101001 1110011 100000 1101101 1100101 1100001 1101110 100000 1101001 1101101 100000 1100001 100000 1100111 1100101 1100101 1101011 111111

Howard Richards

Harvey Saayman wrote:

Howard Richards wrote: You're not trying to share the datacontext among threads What exactly do you mean by this?

I mean you're not creating a single datacontext and passing it to the threads to share. That's good - MS says an instance of a datacontext isn't thread-safe. Your code looks like the calc is creating its own datacontext within each thread instance. This should be fine.

'Howard

Harvey Saayman

Howard Richards wrote:

Your code looks like the calc is creating its own datacontext within each thread instance

This assumption is correct... The CalcData method (The method that executes in the seperate thread)inside of EmployeeCalculationClass is a "calculation overview" if you will. Its content is as follows

//
// Check data and throw exceptions where necicary
CheckData();

//
// Prepare The Process Period List
PopulateProcessPeriodList();

//
// Prepare the Organized Punches List
PrepareOrganizedPunches();

//
// Loop each Process Period And do the calculation for
// That specific day.
foreach (ProcessPeriod pp in ProcessPeriodList)
{
ProcessDailyData(pp);
}

Each one of those methods start of with a using (EmployeeDataContext ctx = new EmployeeDataContext(sqlConnectionString)) code block. ProcessDailyData(); is an "overview method" for daily calculations so it also has helper methods that all have their own using (EmployeeDataContext ctx = new EmployeeDataContext(sqlConnectionString)) code block. So do you have any idea why I'm getting the exceptions when I'm running 20 threads at a time but not when I'm only running one thread at a time? Thanks Howard

Harvey Saayman - South Africa Software Developer .Net, C#, SQL you.suck = (you.Passion != Programming & you.Occupation == jobTitles.Programmer) 1000100 1101111 1100101 1110011 100000 1110100 1101000 1101001 1110011 100000 1101101 1100101 1100001 1101110 100000 1101001 1101101 100000 1100001 100000 1100111 1100101 1100101 1101011 111111

modified on Wednesday, February 11, 2009 7:55 AM