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?
wasabi3689Asked:
Who is Participating?
 
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
 
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.