更新 + WITH (ROWLOCK) + CTE [英] UPDATE + WITH (ROWLOCK) + CTE

问题描述

我找不到任何关于 T-SQL 语句语法的文档:我需要对 CTE 结果进行 WITH (ROWLOCK) UPDATE.

I can't find any documentation about syntax for T-SQL statement: I need to make an WITH (ROWLOCK) UPDATE on a CTE result.

类似于:(因此更新将是 top1000 table1.col2.在 table1 的行的 UPDATE 期间声明 WITH (ROWLOCK) 至关重要)

Something like: (so updated will be top1000 table1.col2. Statement WITH (ROWLOCK) during an UPDATE on rows of table1 is crucial)

    ;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

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

The above statement is probably syntactically correct, however if someone will find such example, please give me a link.

但是:我的完整 SQL 如下所示.在执行期间我得到错误:

BUT: my full SQL looks like below. During execute I get error:

为表table1"指定了冲突的锁定提示.这可能是由为视图指定的冲突提示引起的.

Conflicting locking hints are specified for table "table1". This may be caused by a conflicting hint specified for a view.

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

Why can't I use WITH (NOLOCK) for selecting and WITH (ROWLOCK) on updating?

;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

推荐答案

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

NOLOCK does not apply to the part of the query that references the table to be modified. In SQL Server update statements U-lock each row briefly while it is being tested. This is a deadlock avoidance mechanism. It prevents multiple updates to each S-lock a row for reading and then try to X-lock it.

你不能让 U 型锁消失 AFAIK.但是您可以通过自连接将 U 锁定的行数减少到绝对最小值:

You cannot make the U-locks go away AFAIK. But you can reduce the amount of rows U-locked to the abolute minimum by self joining:

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 进行读取.

This adds a little overhead but it allows you to use NOLOCK for reading.

考虑对读取使用快照隔离.NOLOCK 存在查询随机中止等问题.

Consider using snapshot isolation for the reads. NOLOCK has certain problems such as queries randomly aborting.

这篇关于更新 + WITH (ROWLOCK) + CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！