了解SELECT查询上的SQL Server LOCKS [英] Understanding SQL Server LOCKS on SELECT queries

问题描述

我想知道如果只影响表的其他查询是SELECT查询，那么在表上使用SELECT WITH (NOLOCK)有什么好处.

I'm wondering what is the benefit to use SELECT WITH (NOLOCK) on a table if the only other queries affecting that table are SELECT queries.

SQL Server如何处理? SELECT查询会阻止另一个SELECT查询吗?

How is that handled by SQL Server? Would a SELECT query block another SELECT query?

我正在使用SQL Server 2012和Linq-to-SQL DataContext.

I'm using SQL Server 2012 and a Linq-to-SQL DataContext.

(编辑)

关于效果:

• 如果使用锁定的SELECT，第二个SELECT是否必须等待第一个SELECT完成?
• 与SELECT WITH (NOLOCK)相对吗?

• Would a 2nd SELECT have to wait for a 1st SELECT to finish if using a locked SELECT?
• Versus a SELECT WITH (NOLOCK)?

推荐答案

SQL Server中的SELECT将在表行上放置共享锁-第二个SELECT也需要共享锁，并且它们彼此兼容.

A SELECT in SQL Server will place a shared lock on a table row - and a second SELECT would also require a shared lock, and those are compatible with one another.

所以没有-一个SELECT不能阻止另一个SELECT.

So no - one SELECT cannot block another SELECT.

WITH (NOLOCK)查询提示的作用是能够读取正在插入(通过另一个连接)并且尚未提交的数据.

What the WITH (NOLOCK) query hint is used for is to be able to read data that's in the process of being inserted (by another connection) and that hasn't been committed yet.

如果没有该查询提示，则可能会通过正在进行的INSERT(或UPDATE)语句在行(或整个行)上放置排他锁来阻止SELECT读取表表)，直到该操作的交易已提交(或回滚).

Without that query hint, a SELECT might be blocked reading a table by an ongoing INSERT (or UPDATE) statement that places an exclusive lock on rows (or possibly a whole table), until that operation's transaction has been committed (or rolled back).

提示WITH (NOLOCK)的问题是:您可能最终正在读取根本不会插入的数据行(如果INSERT事务已回滚)-因此您报告中可能会显示从未真正提交到数据库的数据.

Problem of the WITH (NOLOCK) hint is: you might be reading data rows that aren't going to be inserted at all, in the end (if the INSERT transaction is rolled back) - so your e.g. report might show data that's never really been committed to the database.

还有另一个可能有用的查询提示-WITH (READPAST).这指示SELECT命令仅跳过它尝试读取且被独占锁定的任何行. SELECT不会阻塞，也不会读取任何脏的"未提交的数据-但它可能会跳过一些行，例如不在表格中显示所有行.

There's another query hint that might be useful - WITH (READPAST). This instructs the SELECT command to just skip any rows that it attempts to read and that are locked exclusively. The SELECT will not block, and it will not read any "dirty" un-committed data - but it might skip some rows, e.g. not show all your rows in the table.

这篇关于了解SELECT查询上的SQL Server LOCKS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！