SQLSERVER遇到的死锁问题

在开发时遇到了sqlserver死锁的问题，问题情况和解决方法如下。

调用查询接口时报死锁错误，使用工具分析后如下，同一个进程竞争资源发生了死锁。

发生问题的存储过程如下

CREATE PROC [dbo].[spGetEmailTemplateList]
(
@statusStr NVARCHAR(max),
@pageIndex INT,
@pageSize INT,
@sortColumnName VARCHAR(MAX),
@keyword NVARCHAR(MAX),
@sortMode VARCHAR(MAX)
)
AS
BEGIN
DECLARE @sql1 NVARCHAR(max) = N'',
@sql2 NVARCHAR(max) = N'',
@Parm1 NVARCHAR(MAX) = N'',
@Parm2 NVARCHAR(MAX) = N''

CREATE TABLE #resultTable (
                            ID INT,
                            Name NVARCHAR(MAX),
                            Style NVARCHAR(MAX),
                            Remark NVARCHAR(MAX),
                            TitleName NVARCHAR(MAX),
                            DisplayName NVARCHAR(MAX),
                            Description NVARCHAR(MAX),
                            ItemColName1 NVARCHAR(MAX),
                            ItemColName2 NVARCHAR(MAX),
                            ItemColName3 NVARCHAR(MAX),
                            ItemColName4 NVARCHAR(MAX),
                            ItemColName5 NVARCHAR(MAX),
                            ItemColName6 NVARCHAR(MAX),
                            ItemColName7 NVARCHAR(MAX),
                            ItemColName8 NVARCHAR(MAX),
                            ItemColName9 NVARCHAR(MAX),
                            ItemColName10 NVARCHAR(MAX),
                            ItemColName11 NVARCHAR(MAX),
                            ItemColName12 NVARCHAR(MAX),
                            ItemColName13 NVARCHAR(MAX),
                            ItemColName14 NVARCHAR(MAX),
                            ItemColName15 NVARCHAR(MAX),
                            ItemColName16 NVARCHAR(MAX),
                            ItemColName17 NVARCHAR(MAX),
                            ItemColName18 NVARCHAR(MAX),
                            ItemColName19 NVARCHAR(MAX),
                            ItemColName20 NVARCHAR(MAX),
                            ItemColName21 NVARCHAR(MAX),
                            ItemColName22 NVARCHAR(MAX),
                            ItemColName23 NVARCHAR(MAX),
                            ItemColName24 NVARCHAR(MAX),
                            ItemColName25 NVARCHAR(MAX),
                            ItemColName26 NVARCHAR(MAX),
                            ItemColName27 NVARCHAR(MAX),
                            ItemColName28 NVARCHAR(MAX),
                            ItemColName29 NVARCHAR(MAX),
                            ItemColName30 NVARCHAR(MAX),
                            ItemColName31 NVARCHAR(MAX),
                            ItemColName32 NVARCHAR(MAX),
                            ItemColName33 NVARCHAR(MAX),
                            ItemColName34 NVARCHAR(MAX),
                            ItemColName35 NVARCHAR(MAX),
                            ItemColName36 NVARCHAR(MAX),
                            ItemColName37 NVARCHAR(MAX),
                            ItemColName38 NVARCHAR(MAX),
                            ItemColName39 NVARCHAR(MAX),
                            ItemColName40 NVARCHAR(MAX),
                            WorkflowTypeID INT,
                            Created DATETIME,
                            CreatedBy INT,
                            Modified DATETIME,
                            ModifiedBy INT,
                            Status INT,
                            Total INT)

  SET @sql1 = ' INSERT INTO #resultTable (ID,
                                            Name,
                                            Style,
                                            Remark,
                                            TitleName,
                                            DisplayName,
                                            Description,
                                            ItemColName1,
                                            ItemColName2,
                                            ItemColName3,
                                            ItemColName4,
                                            ItemColName5,
                                            ItemColName6,
                                            ItemColName7,
                                            ItemColName8,
                                            ItemColName9,
                                            ItemColName10,
                                            ItemColName11,
                                            ItemColName12,
                                            ItemColName13,
                                            ItemColName14,
                                            ItemColName15,
                                            ItemColName16,
                                            ItemColName17,
                                            ItemColName18,
                                            ItemColName19,
                                             ItemColName20,
                                        ItemColName21,
                                            ItemColName22,
                                            ItemColName23,
                                            ItemColName24,
                                            ItemColName25,
                                            ItemColName26,
                                            ItemColName27,
                                            ItemColName28,
                                            ItemColName29,
                                            ItemColName30,
                                            ItemColName31,
                                            ItemColName32,
                                            ItemColName33,
                                            ItemColName34,
                                            ItemColName35,
                                            ItemColName36,
                                            ItemColName37,
                                            ItemColName38,
                                            ItemColName39,
                                             ItemColName40,
                                            WorkflowTypeID,
                                            Created,
                                            CreatedBy,
                                            Modified,
                                            ModifiedBy,
                                            Status,
                                            Total
                                            )
SELECT  ID,
        Name,
        Style,
        Remark,
        TitleName,
        DisplayName,
        Description,
        ItemColName1,
        ItemColName2,
        ItemColName3,
        ItemColName4,
        ItemColName5,
        ItemColName6,
        ItemColName7,
        ItemColName8,
        ItemColName9,
        ItemColName10,
        ItemColName11,
        ItemColName12,
        ItemColName13,
        ItemColName14,
        ItemColName15,
        ItemColName16,
        ItemColName17,
        ItemColName18,
        ItemColName19,
        ItemColName20,
                                        ItemColName21,
                                            ItemColName22,
                                            ItemColName23,
                                            ItemColName24,
                                            ItemColName25,
                                            ItemColName26,
                                            ItemColName27,
                                            ItemColName28,
                                            ItemColName29,
                                            ItemColName30,
                                            ItemColName31,
                                            ItemColName32,
                                            ItemColName33,
                                            ItemColName34,
                                            ItemColName35,
                                            ItemColName36,
                                            ItemColName37,
                                            ItemColName38,
                                            ItemColName39,
                                             ItemColName40,
        WorkflowTypeID,
        Created,
        CreatedBy,
        Modified,
        ModifiedBy,
        Status,
        COUNT(1) OVER() AS Total
        FROM sysEmailTemplate WITH (NOLOCK)
        WHERE 1=1'
  IF(@statusStr IS NOT NULL AND @statusStr!='')
  SET @sql1=CONCAT(@sql1,' AND Status in (select col from SplitIn(@statusStr,'',''))')

  IF(@keyword IS NOT NULL AND @keyword!='')
    SET @sql1=CONCAT(@sql1,' AND (Name LIKE CONCAT(''%'',@keyword,''%'')
                                    OR TitleName LIKE CONCAT(''%'',@keyword,''%'')
                                    OR DisplayName LIKE CONCAT(''%'',@keyword,''%'')
                                    OR Description LIKE CONCAT(''%'',@keyword,''%''))')

  SET @Parm1= '
    @statusStr NVARCHAR(max),
    @keyword NVARCHAR(max)'

  IF @sortColumnName IS NULL OR @sortColumnName = ''
  SET @sortColumnName = 'Name'
  IF @sortMode IS NULL OR @sortMode = ''
  SET @sortMode = 'DESC'

  SET @sql2 = 'SELECT TOP(@pageSize)      ID,
                                            Name,
                                            Style,
                                            Remark,
                                            TitleName,
                                            DisplayName,
                                            Description,
                                            ItemColName1,
                                            ItemColName2,
                                            ItemColName3,
                                            ItemColName4,
                                            ItemColName5,
                                            ItemColName6,
                                            ItemColName7,
                                            ItemColName8,
                                            ItemColName9,
                                            ItemColName10,
                                            ItemColName11,
                                            ItemColName12,
                                            ItemColName13,
                                            ItemColName14,
                                            ItemColName15,
                                            ItemColName16,
                                            ItemColName17,
                                            ItemColName18,
                                            ItemColName19,
                                            ItemColName20,
                                        ItemColName21,
                                            ItemColName22,
                                            ItemColName23,
                                            ItemColName24,
                                            ItemColName25,
                                            ItemColName26,
                                            ItemColName27,
                                            ItemColName28,
                                            ItemColName29,
                                            ItemColName30,
                                            ItemColName31,
                                            ItemColName32,
                                            ItemColName33,
                                            ItemColName34,
                                            ItemColName35,
                                            ItemColName36,
                                            ItemColName37,
                                            ItemColName38,
                                            ItemColName39,
                                             ItemColName40,
                                            WorkflowTypeID,
                                            Created,
                                            CreatedBy,
                                            Modified,
                                            ModifiedBy，
                                            Status,
                                            Total
    from (
    SELECT    ROW_NUMBER() over(order by ' + @sortColumnName + ' ' + @sortMode + ') AS rownumber,
                                            ID,
                                            Name,
                                            Style,
                                            Remark,
                                            TitleName,
                                            DisplayName,
                                            Description,
                                            ItemColName1,
                                            ItemColName2,
                                            ItemColName3,
                                            ItemColName4,
                                            ItemColName5,
                                            ItemColName6,
                                            ItemColName7,
                                            ItemColName8,
                                            ItemColName9,
                                            ItemColName10,
                                            ItemColName11,
                                            ItemColName12,
                                            ItemColName13,
                                            ItemColName14,
                                            ItemColName15,
                                            ItemColName16,
                                            ItemColName17,
                                            ItemColName18,
                                            ItemColName19,
                                            ItemColName20,
                                        ItemColName21,
                                            ItemColName22,
                                            ItemColName23,
                                            ItemColName24,
                                            ItemColName25,
                                            ItemColName26,
                                            ItemColName27,
                                            ItemColName28,
                                            ItemColName29,
                                            ItemColName30,
                                            ItemColName31,
                                            ItemColName32,
                                            ItemColName33,
                                            ItemColName34,
                                            ItemColName35,
                                            ItemColName36,
                                            ItemColName37,
                                            ItemColName38,
                                            ItemColName39,
                                             ItemColName40,
                                            WorkflowTypeID,
                                            Created,
                                            CreatedBy,
                                            Modified,
                                            ModifiedBy,
                                            Status,
                                            Total
                                            FROM #resultTable
) temp_row
WHERE rownumber > (@pageIndex - 1) * @pageSize'

  SET @Parm2= '@pageIndex INT,
            @pageSize INT'

  PRINT @sql1
  PRINT @sql2

  EXEC Sp_executesql @sql1, @Parm1,
  @statusStr = @statusStr,
  @keyword = @keyword

  EXEC Sp_executesql @sql2, @Parm2,
  @pageIndex = @pageIndex,
  @pageSize = @pageSize

DROP TABLE #resultTable
END

查找资料后发现是查询内并行度导致的死锁（和语句中有插入临时表的语句有关），在select 的where语句后加入OPTION (MAXDOP 1)语句解决。