单人桌陷入僵局

Question

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

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

过程2：在DeadlockedTable上调用具有多个SELECT的表值函数。一些SELECTS在CTE中，一些包含DeadlockedTable上的子SELECTS。

死锁：

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='Exclusive'；--在此处执行插入操作EXEC sp_releaseaplock@Resource="死锁表">

2. 在从死锁表中选择数据时与(nolock(一起使用