并行查询工作程序线程陷入死锁 [英] Parallel query worker thread was involved in a deadlock

问题描述

我运行SQL Server跟踪来跟踪一些死锁问题，并以注释Parallel query worker thread was involved in a deadlock作为死锁的原因使我震惊.

I run an SQL Server Trace to track some deadlocks issues and I was hit on the head with this comment Parallel query worker thread was involved in a deadlock as a reason for the deadlock.

问题1:这是否意味着同一查询自身就陷入僵局?查询执行计划显示了一些并行情况.

Q1: Does this means that the same query is deadlocking it self? The query execution plan shows some parallelism cases.

第二季度:有哪些可能的方法强制" SQL Server不使用并行性，或者至少避免尽可能多地使用并行性?

Q2: What are the possible ways to "force" SQL Server not to use parallelism or at least to avoid using it as much as possible?

推荐答案

Q1:否.这仅表示死锁涉及Exchange操作员.在客户端，您会收到错误消息:事务(进程ID n)与另一个进程在{thread | 通信缓冲区}资源上已死锁，并且已被选择为死锁受害者."

Q1: No. This just means that the deadlock involves an Exchange operator. On the client side you'll get the error "Transaction (Process ID n) was deadlocked on {thread | communication buffer} resources with another process and has been chosen as the deadlock victim."

这类死锁将始终包含两个或多个进程，并且始终包含锁定资源.

These kind of deadlock will always include two or more processes and will always include a lock resource.

此处是此场景的副本.在大多数情况下，拥有正确的索引将解决此问题.

Here is a repro for this scenario. In most cases, having the correct index will resolve this issue.

当进程自身发生死锁(在最新版本中非常罕见)时，它称为查询内并行"死锁，并且您会收到类似"Msg 8650，Level 13，State 1，Line 1 Intra-query Intra-query引起您的服务器命令"的错误. (进程ID n)陷入僵局.通过使用查询提示选项(maxdop 1)，重新运行查询，而无需查询内并行.参见此链接以获取详细信息.

When a process deadlock with itself (very rare with latest builds) it's called Intra-Query Parallelism deadlock and you will get an error like "Msg 8650, Level 13, State 1, Line 1 Intra-query parallelism caused your server command (process ID n) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1)." See this link for details.

第二季度:请参阅Denis提供的链接.

Q2: Refer to the links Denis provided.

这篇关于并行查询工作程序线程陷入死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！