我找不到任何关于 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
发布评论