了解 SELECT 查询上的 SQL Server LOCKS

2021-12-10 00:00:00 tsql sql-server linq-to-sql

我想知道如果唯一影响该表的其他查询是 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.

如果没有该查询提示，SELECT 可能会被正在进行的 INSERT(或 UPDATE)语句阻止读取表，该语句放置一个 排他锁定行(或可能是整个表)，直到该操作的事务已提交(或回滚).

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> 事务回滚) - 所以你的eg报告可能会显示从未真正提交到数据库的数据.

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.

相关文章