C# Multithreaded application and SQL connections help...

Question

I need some advice regarding an application I wrote. The issues I am having are due to my DAL and connections to my SQL Server 2008 database not being closed, however I have looked at my code and each connection is always being closed.

The application is a multithreaded application that retrieves a set of records and while it processes a record it updates information about it.

Here is the flow:

The administrator has the ability to set the number of threads to run and how many records per thread to pull.

Here is the code that runs after they click start:

Adapters are abstractions to my DAL here is a sample of what they look like:

public class UserDetailsAdapter: IDataAdapter<UserDetails>
{
     private IUserDetailFactory _factory;

        public UserDetailsAdapter()
        {
            _factory = new CampaignFactory();
        }

        public UserDetails FindById(int id){
             return _factory.FindById(id);
        }
}

As soon as the _factory is called it processes the SQL and immediately closes the connection.

Code For Threaded App:

private int _recordsPerthread;


private int _threadCount;

    public void RunDetails()
    {
        //create an adapter instance that is an abstration
        //of the data factory layer
        var adapter = new UserDetailsAdapter();

        for (var i = 1; i <= _threadCount; i++)
        {
            //This adater makes a call tot he databse to pull X amount of records and 
            //set a lock filed so the next set of records that are pulled are differnt.
            var details = adapter.FindTopDetailsInQueue(_recordsPerthread);
            if (details != null)
            {
                var parameters = new ArrayList {i, details};
                ThreadPool.QueueUserWorkItem(ThreadWorker, parameters);
            }
            else
            {
                break;
            }
        }
    }

    private void ThreadWorker(object parametersList)
    {
        var parms = (ArrayList) parametersList;
        var threadCount = (int) parms[0];
        var details = (List<UserDetails>) parms[1];
        var adapter = new DetailsAdapter();


        //we keep running until there are no records left inthe Database
        while (!_noRecordsInPool)
        {
            foreach (var detail in details)
            {
                var userAdapter = new UserAdapter();
                var domainAdapter = new DomainAdapter();

                var user = userAdapter.FindById(detail.UserId);
                var domain = domainAdapter.FindById(detail.DomainId);

                //...do some work here......

                adapter.Update(detail);
            }

            if (!_noRecordsInPool)
            {
                details = adapter.FindTopDetailsInQueue(_recordsPerthread);


                if (details == null || details.Count <= 0)
                {
                    _noRecordsInPool = true;
                    break;
                }
            }
        }
    }

The app crashes because there seem to be connection issues to the database. Looking in my log files for the DAL I am seeing this:

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

When I run this in one thread it works fine. I am guessing when I runt his in multiple threads I am obviously making too many connections to the DB. Any thoughts on how I can keep this running in multiple threads and make sure the database doesn’t give me any errors.

Update: I am thinking my issues may be deadlocks in my database. Here is the code in SQL that is running whe I get a deadlock error:

WITH cte AS ( 
  SELECT TOP (@topCount) *
  FROM
  dbo.UserDetails WITH (READPAST) 
WHERE
  dbo.UserDetails where IsLocked = 0)

UPDATE cte 
  SET 
  IsLocked = 1

  OUTPUT INSERTED.*;

I have never had issues with this code before (in other applications). I reorganzied my Indexes as they were 99% fragmented. That didn't help. I am at a loss here.

Answer 1

I'm confused as to where in your code connections get opened, but you probably want your data adapters to implement IDispose (making sure to close the pool connection as you leave using scope) and wrap your code in using blocks:

using (adapter = new UserDetailsAdapter())
{
    for (var i = 1; i <= _threadCount; i++)
    {
        [..]
    }
} // adapter leaves scope here; connection is implicitly marked as no longer necessary

ADO.NET uses connection pooling, so there's no need to (and it can be counter-productive to) explicitly open and close connections.

Answer 2

Hello,

It is not clear to me how you actually connect to the database. The adapter must reference a connection.

How do you actually initialize that connection?

If you use a new adapter for each thread, you must use a new connection for each adapter.

I am not too familiar with your environment, but I am certain that you really need a lot of open connections before your DB starts complaining about it!

Answer 3

ADO.net has a connection pool so its not recommended to open and close connection every time you want use it. see this.

Answer 4

Well, after doing some research I found that there might be a bug in SQL server 2008 and running parallel queries. I’ll have to dig up the link where I found the discussion on this, but I ended up running this on my server:

sp_configure 'max degree of parallelism', 1; GO RECONFIGURE WITH OVERRIDE; GO

This can decrease your server performance, overall, so it may not be an option for some people, but it worked great for me.

For some queries I added the MAXDOP(n) (n being the number of processors to utilize) option so they can run more efficiently. It did help a bit. Secondly, I found out that my DAL’s Dispose method was using the GC.Suppressfinalize method. So, my finally sections were not firing in my DAL properly and not closing out my connections. Thanks to all who gave their input!