SQL 使用 Nolock 提示更新查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21856563/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update Query with Nolock hint
提问by Ram Mehta
I am trying to put an with(NOLOCK) on an update query:
我正在尝试在更新查询中添加 with(NOLOCK)：
UPDATE pth_patchLookup with(nolock) SET ScanDateTime = Getdate() WHERE RegID = 312
but I get the following message :
但我收到以下消息：
NoLock hint is supported only with Select statement and not with update, insert and delete.
Is there any manner in which I can apply a 'NOLOCK" on this update query ?
有什么方法可以在此更新查询中应用“NOLOCK”？
Thanks for any help
谢谢你的帮助
回答by Amir Keshavarz
(NOLOCK)disables shared locks and not exclusive locks.
you can use Read Committed isolation level in order to place an exclusive lock on select statements.
(NOLOCK)禁用共享锁而不是排他锁。您可以使用 Read Committed 隔离级别来在 select 语句上放置排他锁。
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
UPDATE pth_patchLookup SET ScanDateTime = Getdate() WHERE RegID = 312
回答by jean
NOLOCK is a select (only) hint and it's much a bad habit form older programmers since it was almost mandatory in SQL Server 7 but since SQL Server 2000 it's most unnecessary. That hint in particular tell the engine the select can read rows even if it is in the middle of a uncommited transaction. Due to this you can experience dirty or ghost reads.
NOLOCK 是一个选择（仅）提示，它是老程序员的一个坏习惯，因为它在 SQL Server 7 中几乎是强制性的，但自 SQL Server 2000 以来它是最不必要的。该提示特别告诉引擎，即使 select 处于未提交事务的中间，它也可以读取行。因此，您可能会遇到脏读或鬼读。
I strongly suggest you to read about isolation levels to know how to meet your particular system requirements.
我强烈建议您阅读有关隔离级别的内容，以了解如何满足您的特定系统要求。
回答by Helder Gurgel
And this?
和这个？
UPDATE TOP (1000) v
SET idSupervisor = a.supervisor
FROM dbo.Venda_2014 v WITH ( NOLOCK )
INNER JOIN #supervidores_presentes a WITH (NOLOCK) ON v.IdVendedor = a.vendedor AND v.idEmpresaOriginal = a.empresa
WHERE IdDistribuidor IN ( 40 )
AND ISNULL(v.idSupervisor,0) = 0
AND datAnoMesRef >= '201501'
Go
Working fine for me.
对我来说工作得很好。
