A deadlock happens when two tasks permanently block each other when one task is trying to get a resource to lock but the resource is already locked by the other task. SQL Server will chose one of the task (session) that has lower rollback cost, as a deadlock victim and rollback that transaction. The error message may look like this.
Msg 1205, Level 13, State 45, Line 5
Transaction (Process ID 52) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim.
Rerun the transaction.
The deadlock may usually happen when queries in two sessions fight for resources each other in a cyclic manner. In this post I’m not going in deep on deadlock analysis or fixing. The post describes a special case of deadlock called ‘Intra-Query Parallel Deadlock’ that I encountered very recently.
Intra-Query Parallel deadlock happens on parallel query plan executions. One of our clients reported a frequent deadlock occurrence on a procedure. I ran scripts to query deadlock graph and the graph below looks different from usual deadlock graph.
(For clarity I’ve omitted some of the nodes and reformatted)
Proc [Database Id = 7 Object Id = 532912970]
pagelock pageid=”5259244″ objectname=”” mode=”U”
owner id=”process55fdc8″ mode=”U”
waiter id=”process5554c8″ mode=”U” requestType=”wait”
pagelock pageid=”5258622″ objectname=”” mode=”U” owner-list
owner id=”process5554c8″ mode=”U”
waiter id=”process4c434c8″ mode=”U” requestType=”wait”
exchangeEvent id=”Pipe1b6aa3a50″ WaitType=”e_waitPipeGetRow” nodeId=”5″
The exchangeEvent node (marked in red above) is the key discussion point here. When a parallel query operator scans the data using multiple concurrent threads, an exchange operator is used to “glue” the rows at the end of execution of threads. The parallelism operator in the below plan is an exchange operator.
(You can read the a detailed discussion on Parallelism operator or exchange operator here)
In some rare scenarios the Parallelism operator cause deadlock to the input threads. From Craig Freedman’s presentation here(http://blogs.msdn.com/b/craigfr/archive/2007/04/17/parallel-query-execution-presentation.aspx), the scenarios are
- Two merging exchanges separated by order preserving operators
- A merge join with merging exchanges on both inputs
- Merging exchange above index seek with multiple ranges
Ultimately the query will deadlock due to the execution of the query itself!
The e_waitPipeGetRow is the wait type inside the exchange event. You can read more on this here. Apart from exchangeEvent sometimes you can see threadpool also.
SQL Server will raise an error saying;
Server: Msg 8650, Level 13, State 1, Line 1 Intra-query parallelism caused your server command (process ID #51) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1)
As the message suggests, setting MAXDOP 1 (maximum degree-of-parallelism to 1), may solve the issue by using a single execution thread (a serial plan). However setting MAXDOP is not a good solution in all scenarios. Rewriting the query or defining proper indexes may avoid the parallel plan. As suggested by Microsoft, sometimes installing service packs may fix this issue.
The problem database server was SQL Server 2008 R2. I created an index and this lead to a serial plan and prevent the intra-query parallel deadlock!
Ultimately the problem sorted out and everyone was happy. This was a good learning experience for me.
Here are some resources you can read and enjoy!