SQL Server with(nolock)詳解

標籤：color   使用   strong   檔案   資料   on   問題   ad   工作   

大家在寫查詢時,為了效能,往往會在表後面加一個nolock,或者是with(nolock),其目的就是查詢是不鎖定表,從而達到提高查詢速度的目的。

什麼是並發訪問：同一時間有多個使用者訪問同一資源,並發使用者中如果有使用者對資源做了修改，此時就會對其它使用者產生某些不利的影響，例如：

1：髒讀,一個使用者對一個資源做了修改，此時另外一個使用者正好讀取了這條被修改的記錄，然後，第一個使用者放棄修改，資料回到修改之前，這兩個不同的結果就是髒讀。

2：不可重複讀取,一個使用者的一個操作是一個事務，這個事務分兩次讀取同一條記錄，如果第一次讀取後，有另外使用者修改了這個資料，然後第二次讀取的資料正好是其它使用者修改的資料，這樣造成兩次讀取的記錄不同，如果事務中鎖定這條記錄就可以避免。

3：幻讀,指使用者讀取一批記錄的情況，使用者兩次查詢同一條件的一批記錄，第一次查詢後，有其它使用者對這批資料做了修改，方法可能是修改，刪除，新增，第二次查詢時，會發現第一次查詢的記錄條目有的不在第二次查詢結果中，或者是第二次查詢的條目不在第一次查詢的內容中。

為什麼會在查詢的表後面加nolock標識？為了避免並發訪問產生的不利影響，SQL Server有兩種並發訪問的控制機制：鎖、資料列版本設定,表後面加nolock是解決並發訪問的方案之一。

1> 鎖,每個事務對所依賴的資源會請求不同類型的鎖，它可以阻止其他事務以某種可能會導致事務請求鎖出錯的方式修改資源。當事務不再依賴鎖定資源時，鎖將被釋放。

鎖的類型：1:表類型：鎖定整個表；2:行類型：鎖定某個行；3:檔案類型：鎖定某個資料庫檔案；4:資料庫類型：鎖定整個資料庫；5:頁類型：鎖定8K為單位的資料庫頁。

鎖的分類還有一種分法，就是按使用者和資料庫物件來分：

1). 從資料庫系統的角度來看：分為獨佔鎖（即排它鎖），共用鎖定和更新鎖定

1:共用 (S) ：用於不更改或不更新資料的操作（唯讀操作），一般常見的例如select語句。

2:更新 (U) ：用於可更新的資源中。防止當多個會話在讀取、鎖定以及隨後可能進行的資源更新時發生常見形式的死結。

3:排它 (X) ：用於資料修改操作，例如 INSERT、UPDATE 或 DELETE。確保不會同時同一資源進行多重更新。

2). 從程式員的角度看：分為樂觀鎖和悲觀鎖。

1:樂觀鎖：完全依靠資料庫來管理鎖的工作。

2:悲觀鎖：程式員自己管理資料或對象上的鎖處理。

一般程式員一看到什麼鎖之類，覺的特別複雜，對專業的DBA當然是入門級知識了。可喜的是程式員不用去設定,控制這些鎖，SQLServer通過設 置事務的隔離等級自動管理鎖的設定和控制。鎖管理器通過查詢分析器分析待執行的sql語句，來判斷語句將會訪問哪些資源，進行什麼操作，然後結合設定的隔 離層級自動分配管理需要用到的鎖。

2>:資料列版本設定:當啟用了基於資料列版本設定的隔離等級時，資料庫引擎 將維護修改的每一行的版本。應用程式可以指定事務使用行版本查看事務或查詢開始時存在的資料，而不是使用鎖保護所有讀取。通過使用資料列版本設定，讀取操作阻 止其他事務的可能性將大大降低。也就是相當於針對所有的表在查詢時都會加上nolock，同樣會產生髒讀的現象，但差別在於在一個統一管理的地方。說到了基於資料列版本設定的隔離等級，這裡有必要說下隔離等級的概念。

隔離等級的用處：控制鎖的應用，即什麼情境應用什麼樣的鎖機制。

最終目的：解決並發處理帶來的種種問題。

隔離等級的分類：

1：未提交讀,隔離事務的最低層級，只能保證不讀取物理上損壞的資料;

2：已提交讀,資料庫引擎的預設級；

3：可重複讀；

4：可序列化；隔離事務的最進階別，事務之間完全隔離。

小結：NOLOCK 語句執行時不發出共用鎖定，允許髒讀 ，等於 READ UNCOMMITTED交易隔離等級 。nolock確實在查詢時能提高速度，但它並不是沒有缺點的，起碼它會引起髒讀。

nolock的使用情境(個人觀點)：

1:資料量特別大的表，犧牲資料安全性來提升效能是可以考慮的；

2:允許出現髒讀現象的商務邏輯，反之一些資料完整性要求比較嚴格的情境就不合適了，像金融方面等。

3:資料不經常修改的表，這樣會省於鎖定表的時間來大大加快查詢速度。

綜上所述，如果在項目中的每個查詢的表後面都加nolock,這種做法並不科學，起碼特別費時間，不如資料列版本設定來的直接有效。

而且會存在不可預期的技術問題。應該有選擇性的挑選最適合的表來放棄共用鎖定的使用。

最後說下nolock和with(nolock)的幾個小區別：

1:SQL05中的同義字，只支援with(nolock);

2:with(nolock)的寫法非常容易再指定索引。

跨伺服器查詢語句時 不能用with (nolock) 只能用nolock
同一個伺服器查詢時 則with (nolock)和nolock都可以用 
比如

SQL code

select * from [IP].a.dbo.table1 with (nolock) 這樣會提示用錯誤

select * from a.dbo.table1 with (nolock) 這樣就可以

SQL Server with(nolock)詳解