Do I need to synchronize SQL Server calls?

Posted on 2011-10-26
Last Modified: 2012-05-12
I have a Windows Service that is running a Parallel.ForEach series which performs an array of tasks that update information in a SQL Server database.

Within the task itself, I have a method that retrieves data specific to that task via a DataTable.  While the SqlConnection, SqlDataAdapter, etc... are being constructed within the scope of the task, there are still issues.  The DataTable itself is not a global variable and is created only within the scope of the task as well.

All of that stated, the entire application will crash with a large number of timeouts coming from the database server - like it locked up and could not process the requests.  I am specifically using WITH (NOLOCK) commands in my stored procedures but they have had no effect.

I think, given what I know, that the thread pool managing the connections to the database is being overwhelmed.  To that end, I actually tried filtering down the number of concurrent tasks to 50 running at once, and even 20, but I still get these errors when the database stops responding.

Do I somehow need to lock the database connection?  Doesn't that sound contrary to database pooling in the first place?
Question by:ktola
    LVL 5

    Expert Comment

    What kind of statements are you execution in your stored procedure and what is their sequence (e.g. select -> insert -> update -> select -> ...)?

    BR, Marcus

    Author Comment

    The SP that is currently crashing consists of series of Select statements that pulls data in from various tables based on the incoming variables.  There is a function call in there but it is just performing some math operations and does not pull any data.

    There are some Delete statements as well - but I do not see any means of running those unlocked.

    Author Comment

    I should also say that I am using WITH (ROWLOCK) in the Delete statements in an attempt to minimize their impact.
    LVL 5

    Accepted Solution


    If you're selecting and deleting from various table(s) in a special sequence your parallel threads will cause the database to deadlock.

    Search for deadlock provoking sequence of statements. In most cases threads won't release their current lock until they are able to acquire a new lock. Therefore they deadlock each other.

    You can find a load of examples if you google for "deadlock scenarios".

    Further reading on SQL Server 2008 lock modes:

    BR, Marcus

    Author Closing Comment

    Thanks - the resources led me to the 1205 error code so I can handle deadlocks.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now