NOLOCK 和 UNCOMMITTED 之间的区别是什么

What is (are) difference between NOLOCK and UNCOMMITTED(NOLOCK 和 UNCOMMITTED 之间的区别是什么)

问题描述

我使用 SQL Server 2012.

I use SQL Server 2012.

我写了两个查询，但是 NOLOCK 和 UnCommitted 之间有什么不同?

I write two queries but what is a different between NOLOCK and UnCommitted ?

SELECT lastname, firstname
FROM HR.Employees with (READUNCOMMITTED)

SELECT lastname, firstname 
FROM HR.Employees with (NoLock)

推荐答案

NOLOCK : 等价于 READ UNCOMMITTED (来源:MSDN)

NOLOCK : Is equivalent to READ UNCOMMITTED (source : MSDN)

NOLOCK 或 READ UNCOMMITTED 指定允许脏读.不发布共享锁以防止其他事务修改当前事务读取的数据，其他事务设置的排他锁不阻止当前事务读取锁定的数据.允许脏读会导致更高的并发性，但代价是读取数据修改然后被其他事务回滚

NOLOCK or READ UNCOMMITTED Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions

READ UNCOMMITTED 和 NOLOCK 提示仅适用于数据锁.所有查询，包括那些带有 READ UNCOMMITTED 和 NOLOCK 提示的 查询，都会在编译和执行期间获取 Sch-S(架构稳定性)锁.因此，当并发​​事务在表上持有 Sch-M(模式修改)锁时，查询会被阻塞