转载，关于WITH (NOLOCK)的使用

这里明确表示是转载的，如有侵权，我将立即删除

转载两篇关于WITH (NOLOCK)的使用

说实话之前确实没有去认真考虑WITH (NOLOCK)的使用

两篇文章讲的比较好，拿过来分享一下

第一篇来自博客园

http://www.cnblogs.com/henw/archive/2011/07/22/2113580.html

WITH (NOLOCK)

缺点:

　　1.会产生脏读

　　2.只适用与select查询语句

优点:

　　1.有些文件说，加了WITH (NOLOCK)的SQL查询效率可以增加33%。

　　2.可以用于inner join 语句

脏读: 一个用户对一个资源做了修改，此时另外一个用户正好读取了这条被修改的记录，然后，第一个用户放弃修改，数据回到修改之前，这两个不同的结果就是脏读。

详细内容:

　　要提升SQL的查询效能，一般来说大家会以建立索引(index)为第一考虑。其实除了index的建立之外，当我们在下SQL Command时，在语法中加一段WITH (NOLOCK)可以改善在线大量查询的环境中数据集被LOCK的现象藉此改善查询的效能。

不过有一点千万要注意的就是，WITH (NOLOCK)的SQL SELECT有可能会造成Dirty Read(脏读)。

例如：

SELECT   COUNT (UserID) 
FROM  EMPLOYEE  WITH  (NOLOCK) 
JOIN  WORKING_GROUP  WITH  (NOLOCK) 
ON  EMPLOYEE.UserID  =  WORKING_GROUP.UserID

除了简单的SELECT之外，有JOIN的SELECT语法也是可以使用的。但是DELETE、INSERT、UPDATE这些需要transaction的指令就不行了…

有些文件说，加了WITH (NOLOCK)的SQL查询效率可以增加33%。
加了WITH (NOLOCK)即告诉SQL Server，我们的这段SELECT指令无需去考虑目前table的transaction lock状态，因此效能上会有明显的提升，而且数据库系统的Lock现象会有明显的减少(包含Dead Lock)。

有 一点要特别注意，因为WITH (NOLOCK)不考虑目前table的transaction lock，因此当有某些资料正处于多个phase交易(例如跨多个table的transaction交易-->如提款系统)，WITH (NOLOCK)会让目前处理交易process的数据被忽略…

讲白话一点，也就是说当使用NoLock时，它允许阅读那些已经修改但是还没有交易完成的数据。因此如果有需要考虑transaction事务数据的实时完整性时，使用WITH (NOLOCK)就要好好考虑一下。

如果不需考虑transaction，WITH (NOLOCK)或许是个好用的参考。

注1：WITH ( < table_hint > )
指定由查询优化器使用的表扫描、一或多个索引，
或由查询优化器利用此数据表以及为此语句使用锁定模式。

注2：WITH (NOLOCK)相当于READ UNCOMMITTED

另一篇出自

http://www.dotblogs.com.tw/ricochen/archive/2011/04/15/22758.aspx

应该是台湾朋友的，图文并茂，非常好，这里对作者表示感谢

在論壇上看到一則關於With NoLock發問，我不確定發問者是否常態使用NoLock，

但以前經驗告訴我要謹慎使用NoLock，以下就讓我娓娓道來..

2003年微軟有篇關於NoLock的記載，標題如下

NOLOCK 最佳化工具提示可能會造成暫時性的損毀錯誤在 SQL Server 錯誤記錄檔中

當時我相當在意圈選中的文字。

當你使用NoLock時，你等於是告訴SQL Server 使用者不在意資料正確性和一致性，

假設某位使用者正在更新資料表，就會影響其他使用者查詢(with nolock)該資料的正確性和一致性，

那些查詢的使用者可能會遇到重複讀取相同資料、遺漏讀取或中途讀取..等狀況，

而我更在意頁面分割的動作

(頁面分割這裡不多敘述，但你可以參考2011年4月 RUN!PC我所發表的索引概念和設計)

我只和你說頁面分割是相當耗費系統資源的動作，

你絕對不希望使用者再查詢資料時，還得同時處理頁面分割動作，

所以無論如何請一定要減少頁面分割發生的頻率。

Note:

NoLock和ReadUnCommitted效果一樣。

 

create table mytest

(id int identity not null,

data uniqueidentifier default(newid()) not null 

)

--新增資料

declare @i int;set @i=1;

while @i<=30000

begin

insert mytest default values  

set @i=@i+1

end

--建立唯一叢集索引

create unique clustered index cidx on mytest( data )

--確認使用空間

exec sp_spaceused mytest

go

 

現在我來模擬一下並行效果

Connection1執行以下Script1

declare @totalrows int

,@currentnow int

,@errorcount tinyint

set @errorcount = 0

select @totalrows= count(1) from mytest 

while 1 = 1

begin

    --waitfor delay '00:00:00.200'

    select @currentnow= count(1) from mytest WITH(NOLOCK,INDEX = cidx )  

    if @totalrows <> @currentnow

    begin

        print '現在查詢總筆數= ' + cast( @currentnow as varchar(10) ) + 

        ' 差異筆數= ' + cast( @currentnow - @totalrows as varchar(10) )

        set @errorcount = @errorcount + 1

        if @errorcount >= 8

            break

    end

end

Connection2執行以下Script2

begin tran mytran

update mytest

    set data = newid()

結果:

 

Script1中沒有任何的新增或刪除，但查詢總筆數竟然會得到不同的值，

這些情形就是我前面提到的重複讀取或遺漏讀取..等，

雖然Nolock大部分可以避開Blocking(封鎖)問題，

但Nolock卻也帶來另外一個更麻煩的問題，還請小心服用。

参考http://msdn.microsoft.com/zh-tw/library/ms187373.aspx