We help IT Professionals succeed at work.

Deadlock

Medium Priority
469 Views
Last Modified: 2012-06-27
Hi,

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.

deadlock.txt
Comment
Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
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.

http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/

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

Commented:
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?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
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

Commented:
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.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
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

Commented:
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.
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.