SQL Server SELECT语句导致阻塞 [英] SQL Server SELECT statements causing blocking

问题描述

我们正在使用带有巨大选择语句的SQL Server 2005数据库(无行版本控制)，并且正在看到它阻止其他语句运行(使用sp_who2看到).我没有意识到SELECT语句可能导致阻塞-有什么我可以减轻的方法吗?

We're using a SQL Server 2005 database (no row versioning) with a huge select statement, and we're seeing it block other statements from running (seen using sp_who2). I didn't realise SELECT statements could cause blocking - is there anything I can do to mitigate this?

推荐答案

SELECT可以阻止更新.正确设计的数据模型和查询只会导致最小的阻塞，而不会成为问题. 常规"的WITH NOLOCK提示几乎总是错误的答案.正确的答案是调整您的查询，使其不扫描大型表.

SELECT can block updates. A properly designed data model and query will only cause minimal blocking and not be an issue. The 'usual' WITH NOLOCK hint is almost always the wrong answer. The proper answer is to tune your query so it does not scan huge tables.

如果查询不可调整，则应首先考虑快照隔离级别，第二，您应该考虑使用数据库快照，最后一个选项应该是DIRTY阅读(最好更改隔离级别而不是使用NOLOCK HINT).请注意，正如名称所明确指出的那样，脏读将返回不一致的数据(例如，您的工作表可能不平衡).

If the query is untunable then you should first consider SNAPSHOT ISOLATION level, second you should consider using DATABASE SNAPSHOTS and last option should be DIRTY READS (and is better to change the isolation level rather than using the NOLOCK HINT). Note that dirty reads, as the name clearly states, will return inconsistent data (eg. your total sheet may be unbalanced).

这篇关于SQL Server SELECT语句导致阻塞的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！