Posted on 2009-02-09
Medium Priority
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
  • 4
  • 3
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23590511
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

ID: 23590727
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
ID: 23592026
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.
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.


Author Comment

ID: 23592142
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
ID: 23596736
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

ID: 23598472
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 http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx.

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

Accepted Solution

Raja Jegan R earned 2000 total points
ID: 23598528
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Loops Section Overview

750 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