SQL SELECT 语句 - NOLOCK 与 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3857047/
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
SELECT Statement - NOLOCK with SET TRANSACTION ISOLATION LEVEL READ COMMITTED
提问by Dave
My understanding is that when NOLOCK is used in SELECT statement, it could read uncommitted / dirty rows as well. But I wanted to take advantage of NOLOCK hint on table so that my SELECT query will run fast.
我的理解是，当在 SELECT 语句中使用 NOLOCK 时，它也可以读取未提交/脏行。但我想利用表上的 NOLOCK 提示，以便我的 SELECT 查询能够快速运行。
Now, does NOLOCK on table but along with "SET TRANSACTION ISOLATION LEVEL READ COMMITTED" give me NOLOCK advantage and faster SELECT query (because of NOLOCK) with only committed rows (because of SET)?
现在，表上的 NOLOCK 但与“SET TRANSACTION ISOLATION LEVEL READ COMMITTED”一起给我 NOLOCK 优势和更快的 SELECT 查询（因为 NOLOCK），只有提交的行（因为 SET）？
回答by SQLMenace
yes a table hint overrides the isolation level setting, so you will still get dirty reads
是的，表提示会覆盖隔离级别设置，因此您仍然会得到脏读
easy to test
易于测试
first run this
首先运行这个
create table bla(id int)
insert bla values(1)
begin tran
select * from bla with (updlock, holdlock)
make sure not commit the tran!! open another window and run this
确保不提交 tran ！打开另一个窗口并运行它
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
select * from bla -- with (nolock)
you don't get anything back.
你什么也得不到。
open another window and run this now
打开另一个窗口并立即运行
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
select * from bla with (nolock)
as you can see you will get back the row
如您所见，您将返回该行
BTW, READ COMMITTED is the default isolation level, no need to set it
BTW，READ COMMITTED 是默认的隔离级别，不需要设置
Take a look at Snapshot Isolationwhich won't give you back dirty data but still won't lock
看看快照隔离，它不会给你回脏数据但仍然不会锁定
