SQL NOLOCK 和 UNCOMMITTED 之间的区别是什么
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27198020/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is (are) difference between NOLOCK and UNCOMMITTED
提问by Ardalan Shahgholi
I use SQL Server 2012.
我使用 SQL Server 2012。
I write two queries but what is a different between NOLOCKand UnCommitted?
我写了两个查询，但是NOLOCK和之间有什么不同UnCommitted？
SELECT lastname, firstname
FROM HR.Employees with (READUNCOMMITTED)
SELECT lastname, firstname
FROM HR.Employees with (NoLock)
回答by P?????
NOLOCK :Is equivalent to READUNCOMMITTED(source : MSDN)
NOLOCK ：相当于READUNCOMMITTED（来源：MSDN）
NOLOCKor READUNCOMMITTEDSpecifies 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
NOLOCK或READUNCOMMITTED指定允许脏读。不发布共享锁以防止其他事务修改当前事务读取的数据，其他事务设置的排他锁不阻止当前事务读取锁定的数据。允许脏读会导致更高的并发性，但代价是读取数据修改然后被其他事务回滚
READUNCOMMITTEDand NOLOCKhints 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
READUNCOMMITTED和NOLOCK提示仅适用于数据锁。所有查询，包括那些with READUNCOMMITTED and NOLOCK hints，在编译和执行期间获取 Sch-S（模式稳定性）锁。因此，当并发事务在表上持有 Sch-M（模式修改）锁时，查询会被阻塞
回答by Prabu
Under the hood they are the performing the same action.
在引擎盖下，他们正在执行相同的操作。
The read-uncommittedisolation level is the least restrictive isolation level within SQL Server, which is also what makes it popular for developers when looking to reduce blocking.
该read-uncommitted隔离级别是SQL Server，它也希望减少阻塞时，是什么使得它受欢迎的开发商中最低限制的隔离级别。
The nolocktable hint behind the scenes performs the exact same action as running under the read-uncommitted isolation level.
nolock幕后的表提示执行与在未提交读隔离级别下运行完全相同的操作。
Theonly differencebetween the two is that the read-uncommittedisolation level determines the locking mechanism for the entire connection and the nolocktable hint determines the locking mechanism for the table that you give the hint to.
该唯一的区别两者之间是read-uncommitted隔离级别决定了整个连接的锁紧机构和nolock表提示确定表的锁定机制，你给的提示。
回答by Code Different
No difference in terms of their functions, like other have mentioned.
就像其他人提到的那样，它们的功能没有区别。
The single difference is that you can apply WITH(NOLOCK)selectively, on some tables but not others. READ UNCOMMITTEDapplies NOLOCKto all tables in a session.
唯一的区别是您可以WITH(NOLOCK)有选择地在某些表上而不是其他表上应用。READ UNCOMMITTED适用NOLOCK于会话中的所有表。
If you do this:
如果你这样做：
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT *
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.ID = T2.id
It is functionally equivalent to:
它在功能上等同于：
SELECT *
FROM Table1 T1 WITH(NOLOCK)
INNER JOIN Table2 T2 WITH(NOLOCK) ON T1.ID = T2.ID
But you can also apply WITH(NOLOCK)selectively:
但您也可以WITH(NOLOCK)有选择地申请：
SELECT *
FROM Table1 T1 WITH(TABLOCK)
INNER JOIN Table2 WITH(NOLOCK) ON T1.ID = T2.ID
回答by CPMunich
There is no difference at the statement level.
语句级别没有区别。
You can set READUNCOMMITED at the session level and here you have to write SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
您可以在会话级别设置 READUNCOMMITED，在这里您必须编写 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
回答by M Prabhu
For NOLOCK , we need to put this hint on table level, so it is require to put for every tables level which are used in update transaction. So it is very lengthy and time consuming to put it everywhere tables refers in query. For READ UNCOMMITTED, We do not need to put it every tables level, just put at session level or query level and can be written at top of the query or stored procedure. Let us look on small demo to elaborate it. First checking here database default isolation level
对于 NOLOCK ，我们需要将此提示放在表级别，因此需要为更新事务中使用的每个表级别放置。所以把它放在查询中表引用的任何地方是非常冗长和耗时的。对于READ UNCOMMITTED，我们不需要把它放在每个表级别，只需放在会话级别或查询级别，并且可以写在查询或存储过程的顶部。让我们看一个小演示来详细说明它。首先在这里检查数据库默认隔离级别
CREATE TABLE SAMPLETABLE
(
Col1 INT ,
Col2 VARCHAR(100)
)
INSERT INTO SAMPLETABLE(Col1,Col2)
SELECT 1,'Col1'
Union all
SELECT 2,'Col1'
BEGIN TRANSACTION
Update SAMPLETABLE Set Col2 = 'Value changed' Where col1 =1
Select * from SAMPLETABLE with (nolock)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Select * from SAMPLETABLE
Output is 1, Col1 for both query
两个查询的输出都是 1，Col1
