NOLOCK和UNCOMMITTED有什么区别 [英] What is (are) difference between NOLOCK and 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:等同于READUNCOMMITTED(来源:

NOLOCK : Is equivalent to READUNCOMMITTED (source : MSDN)

NOLOCK或READUNCOMMITTED指定允许脏读取.没有发出共享锁来防止其他事务修改当前事务读取的数据，并且其他事务设置的互斥锁不会阻止当前事务读取锁定的数据.允许脏读取可能会导致更高的并发性，但是以读取数据修改为代价的，而这些修改随后会被其他事务回滚

NOLOCK or READUNCOMMITTED 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

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

READUNCOMMITTED and NOLOCK hints apply only to data locks. All queries, including those with READUNCOMMITTED and NOLOCK hints, acquire Sch-S (schema stability) locks during compilation and execution. Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table

这篇关于NOLOCK和UNCOMMITTED有什么区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！