SQL Server并行死锁案例解析

并行执行作为提升查询响应时间,提高用户体验的一种有效手段被大家所熟知,感兴趣的朋友可以看我以前的博客SQL Server优化技巧之SQL Server中的"MapReduce", SQL Server优化器特性-位图过滤(Bitmap),然而正如我一直强调的,任何事物均有利弊,重点在于抉择.近日有朋友问我关于在今年7月份SQL Saturday中分享的并行执行中关于并行死锁的内容,这里我就详细解释下我举的实例中的并行死锁.

     并行死锁我们可以理解为SQL Server的”BUG”,即按照并行方式执行时遭遇特定异常使得执行无法继续.官方给的解决方式大多很简单,改为串行就可以了.但其中还是有值得我们深究的地方以便我们更好的利用并行.

     注:由于案例涉及的并行知识点较多,缺乏相关知识的同学可以先预览一下我当时分享的PPT,如果有不清楚的地方也可以私信我. Inside Parallel Query

     闲篇到此,上实例

生成测试数据

CREATE TABLE Numbers
(
    Number INT NOT NULL,
    CONSTRAINT PK_Numbers 
        PRIMARY KEY CLUSTERED (Number)
        WITH FILLFACTOR = 100
)

INSERT INTO Numbers
SELECT
    (a.Number * 256) + b.Number AS Number
FROM 
    (
        SELECT number
        FROM master..spt_values
        WHERE 
            type = 'P'
            AND number <= 255
    ) a (Number),
    (
        SELECT number
        FROM master..spt_values
        WHERE 
            type = 'P'
            AND number <= 255
    ) b (Number)

View Code

接下来我们执行如下语句,取30000下最大偶数,此时我将执行并行数maxdop随意调整为奇数,3,5,7我的执行都可以迅速返回结果.

执行代码

set statistics time on
select 
      maxN=max(num.number)
from dbo.numbers as num
where 
      num.number<30000
      and convert(integer,convert(varchar(max),num.number)) % 2=0
option
(
Maxdop 3,-----5,7
querytraceon 8649
);

View Code

但当我将并行数调整为偶数时,执行时间居然长达数秒…打开trace profiler跟踪dead lock chain我们发现,当并行数为偶数时出现了死锁.

如图1-1,1-2,1-3

select 
      maxN=max(num.number)
from dbo.numbers as num
where 
      num.number<30000
      and convert(integer,convert(varchar(max),num.number)) % 2=0
option
(
Maxdop 4,-----2,6
querytraceon 8649
);

View Code

                                    图1-1

                                            图1-2

                                            图1-3

咋一看有的同学可能觉得蹊跷,Bug可能是很多人的第一反应.而到底发生了什么由我们具体分析下并行死锁的相应执行计划

分析:

1访问基表数据时用的是聚集索引扫描,但扫描方式是backward,而SQL server中只有forward scan可以并行扫描,backward只能串行扫描

2 因此在做exchange向各个threads分发数据时（distribute streams）采用roundrobin轮询分发数据,这势必造成奇偶数据按threads分开流向下一个过滤操作符

3 在Filter时将奇数的数据过滤,而相应的threads也就没有了数据

4 所以在最后exchange汇总数据时(gather streams)有的threads没有数据,因而造成死锁.

注:thread 0为主线程,不参与并行分支工作

分析如图2-1

                                                                             图2-1

而反观并行采用奇数并行数,这时当分发数据时就不会造成某个thread所持有的数据只是奇数或是偶数,也就不会造成后来的情形,死锁也就不会出现.如图2-2感兴趣的同学可以做实验调整并行数并阅读相应的执行计划.

                                                                       图2-2

小结:要读懂并行执行计划需要一定的知识储备,不了解的同学可先看我分享的PPT.并行执行做为提升复杂查询响应时间的杀手锏被很多数据库厂商广泛应用,而其中的部分思想也应用的分布式系统当中,可以说当下硬件架构下并行是执行引擎健壮性的重要体现.

结语:万事皆有因果,一个简单的BUG可以做为回应,但深究可能窥其本质,并且很有意思.技术人需有这种精神.

认为有收获的同学请点赞.