在SQL Server中的每个SELECT上使用(nolock)会导致什么结果? [英] What can happen as a result of using (nolock) on every SELECT in SQL Server?

问题描述

我发现(nolock)优化程序提示允许脏读"，但是在什么非常特殊的情况下，这是一个坏主意?我从未在组织中如此广泛地使用过(nolock)，这让我感到紧张.我想对用户故事进行解释. 保罗做A，彼得做B，X代替Y".

I get that the (nolock) optimizer hint allows for "dirty reads", but under what very specific scenarios is this a bad idea? I've never seen such widespread use of (nolock) in an organization, and it makes me nervous. I'd like an explanation in terms of user stories. "Paul does A, Peter does B, X happens instead of Y".

推荐答案

重新发布

Reposting this answer:

NOLOCK表示根本不放置锁.

您的查询可能在单个查询中返回UPDATE之前的数据和UPDATE 之后的数据.

Your query may returns portions of data as of before UPDATE and portions as of after UPDATE in a single query.

就像，没有信用和类似东西的借记卡.

Like, a debit without a credit and these kinds of stuff.

例如，我只是在一个大表上运行了此查询:

For instance, I just ran this query on a large table:

SELECT  SUM(LEN(name))
FROM    master WITH (NOLOCK)
OPTION (MAXDOP 1)

---
18874367

所有name的长度为1.

然后我重新运行它，并在查询中间更新了表:

Then I reran it and in the middle of the query updated the table:

UPDATE  master
SET     name = 'tt'
WHERE   id <= 10000

SELECT  SUM(LEN(name))
FROM    master WITH (NOLOCK)
OPTION (MAXDOP 1)

---
18874944

如我们所见，该查询注意到577行已更新(长度2)，所有其他行未更新(长度1).

As we can see, this query noticed 577 rows as updated (length 2), all other rows as not updated (length 1).

SELECT  SUM(LEN(name))
FROM    master WITH (NOLOCK)
OPTION (MAXDOP 1)

---
18884367

此查询在上一个查询完成后立即运行，查看所有更新.

And this query, run right after the previous one finished, sees all updates.

这篇关于在SQL Server中的每个SELECT上使用(nolock)会导致什么结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！