[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 840
  • Last Modified:

this deadlock issue

I run SQL profiling to capture  several  messages like below

Parallel query worker thread was involved in a deadlock

Once this error popped in profiling, it follows this patten

Parallel query worker thread was involved in a deadlock
insert into ......(different)
update.... from....(same1)
update.... from....(same1)
insert into ...... ( different )
select ....(different)
update.... from....(same2)
update.... from....(same2)

I don't understand this patten and error message. It seems the same update query run twice causing the deadlock, is it correct?

Can someone give me more inside for this happening?
0
wasabi3689
Asked:
wasabi3689
  • 5
  • 5
5 Solutions
 
wasabi3689Author Commented:
here is the graph
Capture-DeadLock-Chain2.JPG
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this can happen when your query/script is run in parallel mode (split among several threads).

try to do you script in non-parallel mode (OPTION (MAXDOP 1) to see if it's solving the issue
0
 
wasabi3689Author Commented:
Also, from the graph, I see a lot of page locks? how to connect page lock into deadlock issue? what is the possible problem existed in?
0
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.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
"many pagelocks" only mean that your sql does cover data from many pages.
if your query should only update a couple of rows, it's likely not having the good indexes in the tables, which could help performance, reducing page locks, and last but not least, the deadlock.
however, the parallel query execution is more likely "the cause", but I would indeed try to solve both problems.
0
 
wasabi3689Author Commented:
Hi angelIII

can you provide me example for (OPTION (MAXDOP 1) if I have several update statement?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it goes to the end:
http://msdn.microsoft.com/en-us/library/ms181714.aspx
UPDATE ...
    SET 
  FROM ...
WHERE ...
  OPTION (MAXDOP 1) 

Open in new window

0
 
wasabi3689Author Commented:
If I have several update or delete statement,

should I put ?

OPTION (MAXDOP 1)
....
...
OPTION (MAXDOP 16)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
1   => to disable the parallel query execution
0
 
wasabi3689Author Commented:
Do the same to the delete statement too?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no need, normally, as delete will usually not go into parallel query execution.
unless, of course, you are doing a large/massive delete, in which case the parallel query execution may be good.
do all the tables have their clustered index (it may be the primary key constraint that is configured to be the clustered index)?
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now