使用 WITH(NOLOCK) 提高性能 [英] Using WITH(NOLOCK) to increase performance

问题描述

我看到开发人员在查询中使用 WITH(nolock)，它有什么缺点吗?另外，查询的默认执行模式是什么?我的数据库没有任何索引.

I have seen developers using WITH(nolock) in the query, is there any disadvantage of it? Also, what is the default mode of execution of query? My database do not have any index.

有没有其他方法可以提高数据库 select 语句的性能?

Is there any other way to increase database select statement performance?

推荐答案

对 nolock 的常见误解是它在执行时没有在数据库上放置锁.从技术上讲，它确实会发出模式稳定性 (sch-s) 锁，因此锁的否"部分与查询的数据端相关.

The common misconception with nolock is that that it places no locks on the database whilst executing. Technically it does issues a schema-stability (sch-s) lock, so the 'no' part of the lock relates to the data side of the query.

大多数时候我看到这个，这是开发人员过早的优化，因为他们听说它使查询更快.

Most of the time that I see this, it is a premature optimization by a developer because they have heard it makes the query faster.

除非您在接受脏读(并可能两次读取同一行)时检测了证据和有效性，否则不应使用它 - 它绝对不应该是查询的默认方法，而是规则的例外可以显示它是必需的.

Unless you have instrumented proof and validity in accepting a dirty read (and potentially reading the same row twice) then it should not be used - it definately should not be the default approach to queries, but an exception to the rule when it can be shown that it is required.

这篇关于使用 WITH(NOLOCK) 提高性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！