sql - 如何将WITH (NOLOCK) 应用于整个查询

Question

我知道您通常会在表级别应用WITH (NOLOCK)，但假设您希望将 15 个表连接在一起。有没有一种更简单的方法可以将 WITH (NOLOCK) 应用于所有表，而不必在每个表名后面写入它？

Answer 1

您可以设置 transaction isolation level 读取未提交:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT ...

这有效地将整个事务视为WITH (NOLOCK)

来自MSDN :

READ UNCOMMITTED

Specifies that statements can read rows that have been modified by other transactions but not yet committed.

Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.