sql-server - 当WITH (NOLOCK) 使用内部游标时，存储过程挂起

Question

我在 SQL Server 2008 R2 中有一个存储过程，如下；

DECLARE @uniqueId BIGINT

DECLARE TestCursor CURSOR FOR 
    SELECT em.uniqueId 
    FROM EMPLYEE_MASTER em WITH (NOLOCK)
    INNER JOIN EMP_DETAILS edt WITH (NOLOCK) ON em.uniqueId = edt.uniqueId
    INNER JOIN EMP_ATTENDANCE ea WITH (NOLOCK) ON em.uniqueId = ea.uniqueId

OPEN TestCursor

FETCH NEXT FROM TestCursor INTO @uniqueId

WHILE @@fetch_status = 0
BEGIN
    BEGIN TRANSACTION  purge        

    DELETE FROM EMPLYEE_MASTER where uniqueId = @uniqueId
    DELETE FROM EMP_DETAILS where uniqueId = @uniqueId
    DELETE FROM EMP_ATTENDANCE where uniqueId = @uniqueId

    DELETE FROM EMP_ADDRESS where uniqueId = @uniqueId
    DELETE FROM EMP_APPRAISALS where uniqueId = @uniqueId

    COMMIT TRANSACTION purge

    FETCH NEXT FROM TestCursor INTO @uniqueId
END

CLOSE TestCursor
DEALLOCATE TestCursor

此存储过程在开始运行时挂起，并且也会阻止运行其他查询(不存在引用完整性问题)。我怀疑问题的原因是(如您所见)游标内的 SELECT 语句使用 WITH (NOLOCK) 提示进行脏获取。然后用游标内的WITH (NOLOCK)语句删除表中已经在select语句中使用的数据。因为一旦我注释了游标中 select 语句中与 WITH (NOLOCK) 一起使用的删除语句，存储过程就可以正常运行。

谁能解释一下:

1. 我怀疑的是正确的吗？
2. 问题的原因是在游标内的语句中使用了 WITH (NOLOCK) 并锁定了 DELETE 的记录(在事务内运行)
3. 如何防止此问题？从 select 语句中删除 WITH (NOLOCK)？

Answer 1

删除绝对不必要的光标，删除无意义的NOLOCK。

DECLARE @del TABLE (uniqueId BIGINT NOT NULL PRIMARY KEY)

DELETE em
      OUTPUT DELETED.uniqueId
      INTO @del(uniqueId)
FROM EMPLYEE_MASTER em
WHERE EXISTS(SELECT 1 FROM EMP_DETAILS edt WHERE em.uniqueId = edt.uniqueId)
   AND EXISTS(SELECT 1 FROM EMP_ATTENDANCE ea WHERE em.uniqueId = ea.uniqueId)

DELETE t
FROM EMP_DETAILS t
WHERE EXISTS(SELECT 1 FROM @del d WHERE d.uniqueId = t.uniqueId)

等等

关于光标的旁注

DECLARE TestCursor CURSOR STATIC FOR 
SELECT DISTINCT em.uniqueId 
FROM EMPLYEE_MASTER em
INNER JOIN EMP_DETAILS edt ON em.uniqueId = edt.uniqueId
INNER JOIN EMP_ATTENDANCE ea ON em.uniqueId = ea.uniqueId

DISTINCT - 您正在加入 DETAILS，因此行数将乘以详细信息数，这意味着您将尝试多次删除相同的 uniqueId

STATIC - 您可以这样做，而不是无锁定:使用此选项，服务器将创建临时表并在其中存储选定的值。