更新+使用(行锁)+ CTE

Question

我找不到任何关于result语句语法的文档:我需要对CTE结果进行WITH (ROWLOCK)更新。

类似于：(更新后的top1000表1.col2)。语句WITH (ROWLOCK)在table1行的更新过程中至关重要)

    ;WITH CTE AS 
    ( 
        SELECT TOP(1000) table1.col2
        FROM  table1 INNER JOIN table2 ON table1.id = table2.id    
    ) 
    UPDATE CTE WITH (ROWLOCK)
    SET col2 = 1

以上的说法可能在语法上是正确的，但是如果有人会找到这样的例子，请给我一个链接。

但是:我的完整SQL如下所示。在执行过程中，我得到错误：

表"table1“指定了冲突的锁定提示。这可能是由为视图指定的冲突提示造成的。

为什么我不能在更新时使用WITH (NOLOCK)进行选择和使用WITH (ROWLOCK)？

;WITH CTE AS 
( 
    SELECT TOP(5) table1.col2
    FROM table1 WITH (NOLOCK) INNER JOIN table2 WITH (NOLOCK) ON table1.id = table2.id 
    WHERE table1.col3 = 2
    ORDER BY table1.id    
) 
UPDATE CTE WITH (ROWLOCK)
SET col2 = 1

Answer 1

NOLOCK不适用于查询中引用要修改的表的部分。在Server语句中，在测试每一行时，可以简单地锁定每一行。这是一个避免死锁的机制。它防止对每个S-锁一行进行多次更新，然后尝试将其锁定。

你不能让U锁消失AFAIK。但是，您可以通过自连接将U锁定的行数减少到最小：

update t1
set ...
from T t1 with (rowlock)
where t1.ID in (select TOP 5 ID from T t2 with (nolock) where ... order by ...)

这增加了一些开销，但允许您使用NOLOCK进行阅读。

考虑在读取时使用快照隔离。NOLOCK存在某些问题，如查询随机中止。