更新 + WITH (ROWLOCK) + CTE

本文介绍了更新 + 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.