I'm trying to write some queries to fetch data from database. I used to use With(NoLock) to prevent SQL Server from placing shared locks on database. And recently a friend of mine told me that the default behavior now is that it doesn't put any lock on reading data.
我正在尝试编写一些查询来从数据库中获取数据。我曾经使用(NoLock)来阻止SQL Server在数据库中放置共享锁。最近我的一个朋友告诉我，现在默认的行为是它不会对读取数据进行任何锁定。
However, I can't find a clean explanation for this. Should I still use With(NoLock) in SQL Server?
然而，我找不到一个清楚的解释。我还应该在SQL Server中使用(NoLock)吗?
3 个解决方案
#1
3
WITH (NOLOCK) behaves the same as setting the transaction isolation level to READ UNCOMMITTED, just it's scope is different.
(NOLOCK)的行为与设置事务隔离级别以读取未提交的事务一样，只是范围不同。
However keep in mind, that SQL Server is allowed to use a different isolation level, depending on the type of query you are running. (INSERT, UPDATE, DELETE and other write operations have to run under at least READ COMMITTED).
但是请记住，SQL Server允许使用不同的隔离级别，这取决于您正在运行的查询的类型。(插入、更新、删除和其他写入操作必须至少在已提交的读下运行)。
Your friend is not right, SQL Server will acquire at least a schema lock during read operations on the table to prevent changes in the table's structure. This lock will be applied to the table even if you use READ UNCOMMITTED isolation level or the WITH (NOLOCK) hint.
您的朋友不正确，SQL Server将在表上的读操作期间获得至少一个模式锁，以防止表结构的更改。即使您使用了READ UNCOMMITTED隔离级别或WITH (NOLOCK)提示，这个锁也将应用到表中。
In general, I would avoid using the WITH (NOLOCK) hint. You have less control when using the table hint. Use READ UNCOMMITTED isolation level for the connection if dirty reads are acceptable. You can always change the isolation level of the connection, but you cannot dynamically remove the WITH (NOLOCK) hint. This is especially true, when you use WITH (NOLOCK) on each and every table.
一般来说，我会避免使用WITH (NOLOCK)提示。在使用表提示时，您的控制能力更弱。如果可以接受脏读，则对连接使用读未提交隔离级别。您总是可以更改连接的隔离级别，但是不能动态地删除WITH (NOLOCK)提示。当你在每张桌子上使用(NOLOCK)时，这一点尤其正确。
#2
0
You can go through link or u will find more such kinda of links to clear your doubts
你可以通过链接或者你会找到更多这样的链接来消除你的疑虑
https://www.sqlshack.com/understanding-the-impact-of-nolock-and-with-nolock-table-hints-in-sql-server/
#3
0
I found in Microsoft documentation the following statement which warns to use NOLOCK and READUNCOMMITTED hints in query
我在Microsoft文档中找到了下面的语句，它警告在查询中使用NOLOCK和READUNCOMMITTED提示
Note: Support for use of the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement will be removed in a future version of SQL Server. Avoid using these hints in this context in new development work, and plan to modify applications that currently use them.
注意:在SQL Server的未来版本中，将删除适用于UPDATE或DELETE语句的目标表的FROM子句中的READUNCOMMITTED和NOLOCK提示。在新的开发工作中避免在此上下文中使用这些提示，并计划修改当前使用它们的应用程序。
You can check the documentation from the link https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table for more information. There is no mention of being it the default behaviour.
您可以查看链接https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table中的文档以获得更多信息。没有人提到它是默认行为。
#1
3
WITH (NOLOCK) behaves the same as setting the transaction isolation level to READ UNCOMMITTED, just it's scope is different.
(NOLOCK)的行为与设置事务隔离级别以读取未提交的事务一样，只是范围不同。
However keep in mind, that SQL Server is allowed to use a different isolation level, depending on the type of query you are running. (INSERT, UPDATE, DELETE and other write operations have to run under at least READ COMMITTED).
但是请记住，SQL Server允许使用不同的隔离级别，这取决于您正在运行的查询的类型。(插入、更新、删除和其他写入操作必须至少在已提交的读下运行)。
Your friend is not right, SQL Server will acquire at least a schema lock during read operations on the table to prevent changes in the table's structure. This lock will be applied to the table even if you use READ UNCOMMITTED isolation level or the WITH (NOLOCK) hint.
您的朋友不正确，SQL Server将在表上的读操作期间获得至少一个模式锁，以防止表结构的更改。即使您使用了READ UNCOMMITTED隔离级别或WITH (NOLOCK)提示，这个锁也将应用到表中。
In general, I would avoid using the WITH (NOLOCK) hint. You have less control when using the table hint. Use READ UNCOMMITTED isolation level for the connection if dirty reads are acceptable. You can always change the isolation level of the connection, but you cannot dynamically remove the WITH (NOLOCK) hint. This is especially true, when you use WITH (NOLOCK) on each and every table.
一般来说，我会避免使用WITH (NOLOCK)提示。在使用表提示时，您的控制能力更弱。如果可以接受脏读，则对连接使用读未提交隔离级别。您总是可以更改连接的隔离级别，但是不能动态地删除WITH (NOLOCK)提示。当你在每张桌子上使用(NOLOCK)时，这一点尤其正确。
#2
0
You can go through link or u will find more such kinda of links to clear your doubts
你可以通过链接或者你会找到更多这样的链接来消除你的疑虑
https://www.sqlshack.com/understanding-the-impact-of-nolock-and-with-nolock-table-hints-in-sql-server/
#3
0
I found in Microsoft documentation the following statement which warns to use NOLOCK and READUNCOMMITTED hints in query
我在Microsoft文档中找到了下面的语句，它警告在查询中使用NOLOCK和READUNCOMMITTED提示
Note: Support for use of the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement will be removed in a future version of SQL Server. Avoid using these hints in this context in new development work, and plan to modify applications that currently use them.
注意:在SQL Server的未来版本中，将删除适用于UPDATE或DELETE语句的目标表的FROM子句中的READUNCOMMITTED和NOLOCK提示。在新的开发工作中避免在此上下文中使用这些提示，并计划修改当前使用它们的应用程序。
You can check the documentation from the link https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table for more information. There is no mention of being it the default behaviour.
您可以查看链接https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table中的文档以获得更多信息。没有人提到它是默认行为。