单表死锁

Question

我们偶尔会遇到以下死锁：

流程 1：在事务内对 DeadlockedTable 进行多次 INSERTS

流程 2：在 DeadlockedTable 上调用具有多个 SELECT 的表值函数。一些 SELECT 位于 CTE 内，一些包含 DeadlockedTable 上的子选择。

死锁：

Process 2 --> Request Mode S --> PageLock (DeadlockedTable) --> Owner Mode IX   --> Process 1
Process 2 <-- Owner Mode S   <-- PageLock (DeadlockedTable) <-- Request Mode IX <-- Process 1

查询（仅使用 DeadlockedTable 的查询）：

WITH T
AS (
SELECT ID1 = ROW_NUMBER() OVER (
        ORDER BY ENTRY_DATE DESC
        )
    ,*
FROM DeadlockedTable
WHERE ENTRY_ID = @ENTRY_ID
    AND ENTRY_TYPE = @ENTRY_TYPE
    AND ENTRY_DATE BETWEEN @FROM_DATE
        AND @TO_DATE
    AND SOURCE_ID IN (
        SELECT max(SOURCE_ID)
        FROM DeadlockedTable
        WHERE ENTRY_ID = @ENTRY_ID
            AND ENTRY_TYPE = @ENTRY_TYPE
            AND ENTRY_DATE BETWEEN @FROM_DATE
                    AND @TO_DATE
        GROUP BY ENTRY_DATE
        )
)
 INSERT INTO @dtTmpTable
 SELECT *
 FROM (
 SELECT min(ENTRY_DATE) AS ENTRY_DATE
    ,SRC_VALUE AS REF_DATE
FROM DeadlockedTable P
  WHERE P.ENTRY_ID = @ENTRY_ID
    AND ENTRY_TYPE = @ENTRY_TYPE
    AND ENTRY_DATE BETWEEN @FROM_DATE
        AND @TO_DATE
    AND SOURCE_ID IN (
        SELECT max(SOURCE_ID)
        FROM DeadlockedTable
        WHERE ENTRY_ID = @ENTRY_ID
            AND ENTRY_TYPE = @ENTRY_TYPE
            AND ENTRY_DATE BETWEEN @FROM_DATE
                AND @TO_DATE
        GROUP BY ENTRY_DATE
        )
GROUP BY SRC_VALUE
) #
 WHERE ENTRY_DATE NOT IN (
    SELECT rd.REFDATE
    FROM @dtTmpTable RD
    )

 UPDATE @dtTmpTable
 SET TotalValue = SRC_VALUE
 FROM @dtTmpTable
 INNER JOIN DeadlockedTable P ON P.ENTRY_ID = @ENTRY_ID
    AND ENTRY_TYPE = @ENTRY_TYPE
    AND ENTRY_DATE = refDate
 WHERE SOURCE_ID = (
    SELECT max(SOURCE_ID)
    FROM DeadlockedTable
    WHERE ENTRY_ID = @ENTRY_ID
        AND ENTRY_TYPE = @ENTRY_TYPE
        AND ENTRY_DATE = refDate
        
        

索引：

Index 1:  Non-Unique, Non-Clustered (SOURCE_ID)
Index 2:  Non-Unique, Non-Clustered (SOURCE_ID, ENTRY_ID, ENTRY_DATE, LOT)

我不明白为什么进程 2 会在 DeadlockedTable 上请求多个共享锁。我假设锁仅在一个 SELECT 期间保持，然后释放，这是错误的吗？解决这个问题的正确方法是什么？

Answer 1

我已通过以下步骤解决了此问题：

1. 在插入记录时使用锁定。sp_getapplock (Transact-SQL)

   EXEC sp_getapplock @Resource = 'DeadlockedTable', @LockMode = '独占';
    --在这里执行插入工作 
    EXEC sp_releaseapplock @Resource = 'DeadlockedTable'; 
   

2. 从 DeadlockedTable 中选择数据时使用 with(nolock)