Tag Archives: Parallelism

Intra-Query Parallel Deadlock

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)

victim-list

victimProcess id=”process5554c8″/>

victim-list

process-list

process id=”process5554c8″

process id=”process4c434c8″

process id=”process55fdc8″

inputbuf

Proc [Database Id = 7 Object Id = 532912970]

resource-list

pagelock pageid=”5259244″ objectname=”” mode=”U”

owner-list

owner id=”process55fdc8″ mode=”U”

waiter-list

waiter id=”process5554c8″ mode=”U” requestType=”wait”

pagelock pageid=”5258622″ objectname=”” mode=”U”                     owner-list

owner id=”process5554c8″ mode=”U”

waiter-list

waiter id=”process4c434c8″ mode=”U” requestType=”wait”

pagelock

exchangeEvent id=”Pipe1b6aa3a50″ WaitType=”e_waitPipeGetRow” nodeId=”5″

waiter-list

waiter id=”process55fdc8″

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.

Parallel Plan

 

(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

  1. Two merging exchanges separated by order preserving operators
  2. A merge join with merging exchanges on both inputs
  3. 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!

Today’s Annoyingly-Unwieldy Term: “Intra-Query Parallel Thread Deadlocks”

Degrees of Parallelism and a Degree of Uncertainty

Handling Deadlocks in SQL Server

Understanding and Using Parallelism in SQL Server

 

Leave a comment

Filed under Administration, Internals, Performance