概念介紹

開(kāi)發(fā)人員喜歡在SQL腳本中使用WITH(NOLOCK), WITH(NOLOCK)其實(shí)是表提示（table_hint）中的一種。它等同于 READUNCOMMITTED 。 具體的功能作用如下所示（摘自MSDN）：

   1： 指定允許臟讀。不發(fā)布共享鎖來(lái)阻止其他事務(wù)修改當(dāng)前事務(wù)讀取的數(shù)據(jù)，其他事務(wù)設(shè)置的排他鎖不會(huì)阻礙當(dāng)前事務(wù)讀取鎖定數(shù)據(jù)。允許臟讀可能產(chǎn)生較多的并發(fā)操作，但其代價(jià)是讀取以后會(huì)被其他事務(wù)回滾的數(shù)據(jù)修改。這可能會(huì)使您的事務(wù)出錯(cuò)，向用戶顯示從未提交過(guò)的數(shù)據(jù)，或者導(dǎo)致用戶兩次看到記錄（或根本看不到記錄）。有關(guān)臟讀、不可重復(fù)讀和幻讀的詳細(xì)信息，請(qǐng)參閱并發(fā)影響。

   2： READUNCOMMITTED 和 NOLOCK 提示僅適用于數(shù)據(jù)鎖。所有查詢（包括那些帶有 READUNCOMMITTED 和 NOLOCK 提示的查詢）都會(huì)在編譯和執(zhí)行過(guò)程中獲取 Sch-S（架構(gòu)穩(wěn)定性）鎖。因此，當(dāng)并發(fā)事務(wù)持有表的 Sch-M（架構(gòu)修改）鎖時(shí)，將阻塞查詢。例如，數(shù)據(jù)定義語(yǔ)言 (DDL) 操作在修改表的架構(gòu)信息之前獲取 Sch-M 鎖。所有并發(fā)查詢（包括那些使用 READUNCOMMITTED 或 NOLOCK 提示運(yùn)行的查詢）都會(huì)在嘗試獲取 Sch-S 鎖時(shí)被阻塞。相反，持有 Sch-S 鎖的查詢將阻塞嘗試獲取 Sch-M 鎖的并發(fā)事務(wù)。有關(guān)鎖行為的詳細(xì)信息，請(qǐng)參閱鎖兼容性（數(shù)據(jù)庫(kù)引擎）。

   3:  不能為通過(guò)插入、更新或刪除操作修改過(guò)的表指定 READUNCOMMITTED 和 NOLOCK。SQL Server 查詢優(yōu)化器忽略 FROM 子句中應(yīng)用于 UPDATE 或 DELETE 語(yǔ)句的目標(biāo)表的 READUNCOMMITTED 和 NOLOCK 提示。

功能與缺陷

    使用WIHT(NOLOCK)有利也有弊，所以在決定使用之前，你一定需要了解清楚WITH(NOLOCK)的功能和缺陷，看其是否適合你的業(yè)務(wù)需求,不要覺(jué)得它能提升性能，稀里糊涂的就使用它。

    1：使用WITH(NOLOCK)時(shí)查詢不受其它排他鎖阻塞

    打開(kāi)會(huì)話窗口1，執(zhí)行下面腳本，不提交也不回滾事務(wù)，模擬事務(wù)真在執(zhí)行過(guò)程當(dāng)中

BEGIN TRAN

       UPDATE TEST SET NAME='Timmy' WHERE OBJECT_ID =1;

       --ROLLBACK

   打開(kāi)會(huì)話窗口2，執(zhí)行下面腳本，你會(huì)發(fā)現(xiàn)執(zhí)行結(jié)果一直查詢不出來(lái)（其實(shí)才兩條記錄）。當(dāng)前會(huì)話被阻塞了

SELECT * FROM TEST;

    打開(kāi)會(huì)話窗口3，執(zhí)行下面腳本，查看阻塞情況，你會(huì)發(fā)現(xiàn)在會(huì)話2被會(huì)話1給阻塞了，會(huì)話2的等待類(lèi)型為L(zhǎng)CK_M_S：“當(dāng)某任務(wù)正在等待獲取共享鎖時(shí)出現(xiàn)”

  SELECT wt.blocking_session_id                    AS BlockingSessesionId

        ,sp.program_name                           AS ProgramName

        ,COALESCE(sp.LOGINAME, sp.nt_username)     AS HostName    

        ,ec1.client_net_address                    AS ClientIpAddress

        ,db.name                                   AS DatabaseName        

        ,wt.wait_type                              AS WaitType                    

        ,ec1.connect_time                          AS BlockingStartTime

        ,wt.WAIT_DURATION_MS/1000                  AS WaitDuration

        ,ec1.session_id                            AS BlockedSessionId

        ,h1.TEXT                                   AS BlockedSQLText

        ,h2.TEXT                                   AS BlockingSQLText

  FROM sys.dm_tran_locks AS tl

  INNER JOIN sys.databases db

    ON db.database_id = tl.resource_database_id

  INNER JOIN sys.dm_os_waiting_tasks AS wt

    ON tl.lock_owner_address = wt.resource_address

  INNER JOIN sys.dm_exec_connections ec1

    ON ec1.session_id = tl.request_session_id

  INNER JOIN sys.dm_exec_connections ec2

    ON ec2.session_id = wt.blocking_session_id

  LEFT OUTER JOIN master.dbo.sysprocesses sp

    ON SP.spid = wt.blocking_session_id

  CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1

  CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

此時(shí)查看會(huì)話1（會(huì)話1的會(huì)話ID為53，執(zhí)行腳本1前，可以用SELECT  @@spid查看會(huì)話ID）的鎖信息情況，你會(huì)發(fā)現(xiàn)表TEST(ObjId=1893581784)持有的鎖信息如下所示

打開(kāi)會(huì)話窗口4，執(zhí)行下面腳本.你會(huì)發(fā)現(xiàn)查詢結(jié)果很快就出來(lái)，會(huì)話4并不會(huì)被會(huì)話1阻塞。

    SELECT * FROM TEST WITH(NOLOCK)

從上面模擬的這個(gè)小例子可以看出，正是由于加上WITH(NOLOCK)提示后，會(huì)話1中事務(wù)設(shè)置的排他鎖不會(huì)阻礙當(dāng)前事務(wù)讀取鎖定數(shù)據(jù)，所以會(huì)話4不會(huì)被阻塞，從而提升并發(fā)時(shí)查詢性能。

2：WITH(NOLOCK) 不發(fā)布共享鎖來(lái)阻止其他事務(wù)修改當(dāng)前事務(wù)讀取的數(shù)據(jù)，這個(gè)就不舉例子了。

本質(zhì)上WITH(NOLOCK)是通過(guò)減少鎖和不受排它鎖影響來(lái)減少阻塞，從而提高并發(fā)時(shí)的性能。所謂凡事有利也有弊，WITH(NOLOCK)在提升性能的同時(shí)，也會(huì)產(chǎn)生臟讀現(xiàn)象。

如下所示，表TEST有兩條記錄，我準(zhǔn)備更新OBJECT_ID=1的記錄，此時(shí)事務(wù)既沒(méi)有提交也沒(méi)有回滾

BEGIN TRAN 

UPDATE TEST SET NAME='Timmy' WHERE OBJECT_ID =1; 

--ROLLBACK 

此時(shí)另外一個(gè)會(huì)話使用WITH(NOLOCK)查到的記錄為未提交的記錄值

假如由于某種原因，該事務(wù)回滾了，那么我們讀取到的OBJECT_ID=1的記錄就是一條臟數(shù)據(jù)。

臟讀又稱無(wú)效數(shù)據(jù)的讀出，是指在數(shù)據(jù)庫(kù)訪問(wèn)中，事務(wù)T1將某一值修改，然后事務(wù)T2讀取該值，此后T1因?yàn)槟撤N原因撤銷(xiāo)對(duì)該值的修改，這就導(dǎo)致了T2所讀取到的數(shù)據(jù)是無(wú)效的。

WITH(NOLOCK)使用場(chǎng)景

什么時(shí)候可以使用WITH(NOLOCK)? 什么時(shí)候不能使用WITH(NOLOCK),這個(gè)要視你系統(tǒng)業(yè)務(wù)情況，綜合考慮性能情況與業(yè)務(wù)要求來(lái)決定是否使用WITH(NOLOCK), 例如涉及到金融或會(huì)計(jì)成本之類(lèi)的系統(tǒng)，出現(xiàn)臟讀那是要產(chǎn)生嚴(yán)重問(wèn)題的。關(guān)鍵業(yè)務(wù)系統(tǒng)也要慎重考慮。大體來(lái)說(shuō)一般有下面一些場(chǎng)景可以使用WITH(NOLOCK)

   1: 基礎(chǔ)數(shù)據(jù)表，這些表的數(shù)據(jù)很少變更。

   2：歷史數(shù)據(jù)表，這些表的數(shù)據(jù)很少變更。

   3：業(yè)務(wù)允許臟讀情況出現(xiàn)涉及的表。

   4：數(shù)據(jù)量超大的表，出于性能考慮，而允許臟讀。

另外一點(diǎn)就是不要濫用WITH(NOLOCK),我發(fā)現(xiàn)有個(gè)奇怪現(xiàn)象，很多開(kāi)發(fā)知道WITH(NOLOCK),但是有不了解臟讀，習(xí)慣性的使用WITH(NOLOCK)。

WITH(NOLOCK)與 NOLOCK區(qū)別

為了搞清楚WITH(NOLOCK)與NOLOCK的區(qū)別，我查了大量的資料，我們先看看下面三個(gè)SQL語(yǔ)句有啥區(qū)別

    SELECT * FROM TEST NOLOCK

    SELECT * FROM TEST (NOLOCK);

    SELECT * FROM TEST WITH(NOLOCK);

上面的問(wèn)題概括起來(lái)也就是說(shuō)NOLOCK、(NOLOCK)、 WITH(NOLOCK)的區(qū)別：

1： NOLOCK這樣的寫(xiě)法，其實(shí)NOLOCK其實(shí)只是別名的作用，而沒(méi)有任何實(shí)質(zhì)作用。所以不要粗心將(NOLOCK)寫(xiě)成NOLOCK

2：（NOLOCK）與WITH(NOLOCK)其實(shí)功能上是一樣的。(NOLOCK)只是WITH(NOLOCK)的別名,但是在SQL Server 2008及以后版本中，(NOLOCK)不推薦使用了，"不借助 WITH 關(guān)鍵字指定表提示”的寫(xiě)法已經(jīng)過(guò)時(shí)了。 具體參見(jiàn)MSDN http://msdn.microsoft.com/zh-cn/library/ms143729%28SQL.100%29.aspx

    2.1  至于網(wǎng)上說(shuō)WITH(NOLOCK)在SQL SERVER 2000不生效，我驗(yàn)證后發(fā)現(xiàn)完全是個(gè)謬論。

    2.2  在使用鏈接服務(wù)器的SQL當(dāng)中，(NOLOCK)不會(huì)生效，WITH(NOLOCK)才會(huì)生效。如下所示

    消息 4122，級(jí)別 16，狀態(tài) 1，第 1 行

    Remote table-valued function calls are not allowed.

3.語(yǔ)法上有些許出入，如下所示

這種語(yǔ)法會(huì)報(bào)錯(cuò)

SELECT  * FROM   sys.indexes  WITH(NOLOCK) AS i

-Msg 156, Level 15, State 1, Line 1

-Incorrect syntax near the keyword 'AS'.

這種語(yǔ)法正常

SELECT  * FROM   sys.indexes  (NOLOCK) AS i

可以全部改寫(xiě)為下面語(yǔ)法

SELECT  * FROM   sys.indexes   i WITH(NOLOCK) 

SELECT  * FROM   sys.indexes   i (NOLOCK) 

WITH(NOLOCK)會(huì)不會(huì)產(chǎn)生鎖

    很多人誤以為使用了WITH(NOLOCK)后，數(shù)據(jù)庫(kù)庫(kù)不會(huì)產(chǎn)生任何鎖。實(shí)質(zhì)上，使用了WITH(NOLOCK)后，數(shù)據(jù)庫(kù)依然對(duì)該表對(duì)象生成Sch-S（架構(gòu)穩(wěn)定性）鎖以及DB類(lèi)型的共享鎖， 如下所示，可以在一個(gè)會(huì)話中查詢一個(gè)大表，然后在另外一個(gè)會(huì)話中查看鎖信息(也可以使用SQL Profile查看會(huì)話鎖信息)

    不使用WTIH(NOLOCK)

  使用WITH(NOLOCK)

  從上可以看出使用WITH(NOLOCK)后，數(shù)據(jù)庫(kù)并不是不生成相關(guān)鎖。  對(duì)比可以發(fā)現(xiàn)使用WITH(NOLOCK)后，數(shù)據(jù)庫(kù)只會(huì)生成DB類(lèi)型的共享鎖、以及TAB類(lèi)型的架構(gòu)穩(wěn)定性鎖.

另外，使用WITH(NOLOCK)并不是說(shuō)就不會(huì)被其它會(huì)話阻塞，依然可能會(huì)產(chǎn)生Schema Change Blocking

會(huì)話1：執(zhí)行下面SQL語(yǔ)句，暫時(shí)不提交，模擬事務(wù)正在執(zhí)行

BEGIN TRAN 

  ALTER TABLE TEST ADD Grade VARCHAR(10) ; 

會(huì)話2：執(zhí)行下面語(yǔ)句，你會(huì)發(fā)現(xiàn)會(huì)話被阻塞，截圖如下所示。

SELECT * FROM TEST WITH(NOLOCK)