Posted on 2009-02-09
Last Modified: 2012-06-27

On our production system, we frequently face deadlock issues. We found that these deadlocks are not because of the locks on the objects but because of the parallel processing of the queries. Attached is the snippet from the Error log.

We have reduced the max degree of parallelism to reduce the occurrence of this deadlock. Currently, the value is 4. But still it is occurring. Can someone give me more light on this kind of deadlocks and let me know the preventive measures (apart from re-writing queries) for the same.

Question by:Srinivas_Vengala
    LVL 57

    Expert Comment

    by:Raja Jegan R
    Use SQL Server Profiler and try out which query is exactly causing out the problem and try fixing out that query so that you will be able to resolve the parrallel queue and other deadlocks.

    To find out the root cause for Deadlocks.

    Give me the query which takes more time. Either we can tune it out or add appropriate indexes to resolve deadlocks.

    Author Comment

    Re-writing the queries or introducing/dropping indexes are the only options to resolve this issue? Any other options?

    Unfortunately, this deadlock occurs during a overnight process and that too at different queries. As the issue occurs once in a while, we are not running the profiler continuously.

    Also, the link you have provided doesn't talk about this kind of deadlocks. Can you please explain me what are all the possible reasons for a single query deadlocking itself?
    LVL 57

    Expert Comment

    by:Raja Jegan R
    Sure.. Assume you have one SELECT query:

    update tablea
    set col2 = value
    from tablea, tableb
    where tablea.col1 = tableb.col1
    and tablea.col3 = value
    and tableb.col3 = value

    Assume this query is running in my machine and for that update, you will be locking those two set of tables, and hence your overall update time will be huge.

    Now if you execute the same query from your machine, then it will wait for those set of records to be released by me so that your update can be done.

    Hence If my query time decreases, then the time to wait for your query increases.
    this becomes a deadlock condition if more and more users start waiting for the locks to be resolved. This is what we call as deadlock.

    To reduce the deadlock, we have to put proper indexes in our queries or either rewrite our queries to an optimal extent.

    And hence I asked you to find out those set of queries using Profiler. There is no other way around it.

    Author Comment

    I believe you didn't check the Errorlog file that I have sent. In our scenario, there is only one query running. No other queries running parallelly which can cause lock incompatibilities and hence the deadlock. But this deadlock is occurring because of the parallelism. I know about the normal deadlock where multiple connections are involved. In our case it is only one connection executing one long query which is being processed parallelly.
    LVL 57

    Expert Comment

    by:Raja Jegan R
    I checked your Error Log file.

    process id=process3527f18
    process id=process3527d38
    process id=processdaa2f8

    taskpriority=0 logused=20133 waittime=15 schedulerid=15 isolationlevel=read committed

    So if you have many parallel processes, and the wait time is 15 seconds for the other transaction to fininsh. Parallel processes in database perspective refers to some other task at higher level or some other queries at the granular level.

    Hence as I said earlier we focus on those queries to finish it fast, then we can resolve this deadlock.

    Author Comment

    Did you check the SPID for all the processes? It is same. On our server, when this deadlock occurs, there only one query runs (of couse,  the query changes).
    My deadlock (parallel thread deadlock) is similar to the one explained by BARTD on April 23, 2007 12:23 PM at this link

    I wanted more info on parallel thread deadlocks not the usual deadlocks.
    LVL 57

    Accepted Solution

    Even I meant the same thing earlier as you mentioned me that you are executing a single query only as a parallel process.

    I am iterating this again, if we tune that particular query involved either using index or rewriting we can resolve this deadlock.

    Can you provide me the query so that I can look into it.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
    by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    729 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

    24 Experts available now in Live!

    Get 1:1 Help Now