瞭解SQL Server鎖爭用：NOLOCK 和 ROWLOCK 的秘密

標籤：

關係型資料庫，如SQL Server，使用鎖來避免多使用者修改資料時的並發衝突。當一組資料被某個使用者鎖定時，除非第一個使用者結束修改並釋放鎖，否則其他使用者就無法修改該組資料。

有些資料庫，包括SQL Server，用鎖來避免使用者檢索未遞交的修改記錄。在這些系統中，如果使用者A在修改一組記錄，則其他使用者只有等使用者A修改完畢了，才能檢索。

資料庫在每個物理層上設定鎖：記錄行(rows)，資料頁(pages, 上百萬記錄行)，擴充頁(extends, 多個資料頁)，整個表，甚至整個資料庫。有些資料庫(如Oracle等)只使用精細的行鎖機制，而別的資料庫，則使用在頁面，擴充頁，表和資料庫上的較大範圍的鎖機制。大多數資料庫，包括SQL Server，同樣支援行鎖機制，但是經常使用的還是大範圍鎖機制。 這主要是因為管理鎖需要付出高昂的代價。鎖十分複雜而且數量很多，所以如果全都是 行鎖的話，將是極為痛苦的：一百萬行的資料更新就會輕易消耗巨大的記憶體，從而根本無法進行管理。

鎖爭用的描述

那些不僅僅使用行級鎖的資料庫使用一種稱為混和鎖(lock escalation)的技術來擷取較高的效能。除非很明確知道是針對整個資料表，否則這些資料庫的做法是開始使用行級鎖， 然後隨著修改的資料增多，開始使用大範圍的鎖機制。

不幸的是，這種混和鎖的方法會產生和放大新的問題：死結。如果兩個使用者以相反的順序修改位於不同表的記錄，而這兩條記錄雖然邏輯上不相關， 但是物理上是相鄰的，操作就會先引發行鎖，然後升級為頁面鎖。這樣， 兩個使用者都需要對方鎖定的東西，就造成了死結。

例如：

使用者A修改表A的一些記錄，引發的頁面鎖不光鎖定正在修改的記錄，還會有很多其它記錄也會被鎖定。

使用者B修改表B的一些記錄，引發的頁面鎖鎖定使用者A和其它正在修改的資料。

使用者A想修改使用者B在表B中鎖定(並不一定正在修改的)資料。

使用者B想修改或者僅僅想訪問使用者A在表A中鎖定(並不一定正在修改)的資料。

為瞭解決該問題，資料庫會經常去檢測是否有死結存在，如果有，就把其中的一個事務撤銷，好讓另一個事務能順利完成。一般來說，都是撤銷 那個修改資料量少的事務，這樣復原的開銷就比較少。使用行級鎖的資料庫 很少會有這個問題，因為兩個使用者同時修改同一條記錄的可能性極小，而且由於極其偶然的修改資料的順序而造成的鎖也少。

而且，資料庫使用鎖逾時來避免讓使用者等待時間過長。查詢逾時的引入也是為了同樣目的。我們可以重新遞交那些逾時的查詢，但是這隻會造成資料庫 的堵塞。如果經常發生逾時，說明使用者使用SQL Server的方式有問題。正常 情況是很少會發生逾時的。

在伺服器負載較高的運行環境下，使用混合鎖的SQL Server鎖機制，表現不會很好。 原因是鎖爭用(Lock Contention)。鎖爭用造成死結和鎖等待問題。在一個多使用者系統中，很多使用者會同時在修改資料庫，還有更多的使用者在同時訪問資料庫，隨時會產生鎖，使用者 也爭先恐後地擷取鎖以確保自己的操作的正確性，死結頻繁發生，這種情形下， 使用者的心情可想而知。

確實，如果只有少量使用者，SQL Server不會遇到多少麻煩。自我裝載和發布的時候，由於使用者較少， 也很難發現那些並發問題。但是當激發幾百個並發，進行持續不斷地INSERT,UPDATE，以及一些 DELETE操作時，如何觀察是否有麻煩出現，那時候你就會不得不手忙腳亂地去閱讀Oracle的文獻。 不過我有一個解決辦法，該方法只需要檢查你的T-SQL代碼，很少的調整和系統測試。用該方法教你進行適當的系統測試過程。

鎖爭用的解決方案

如果你在今年6月－8月之間訪問Streamload.com，你可能會看到諸如“遇到死結”，“鎖逾時”， “需要對象”等錯誤。這些錯誤都是由於鎖爭用引起的。在查閱大量文檔和討論後，我瞭解了這方面的知識，也就是上面所論述的內容，我再次敘述如下：

SQL Server開始是用行級鎖的，但是經常會擴大為頁面鎖和表鎖，最終造成死結。

即使使用者沒有修改資料，SQL Server在SELECT的時候也會遇到鎖。幸運的是，我們可以通過SQL Server 的兩個關鍵字來手工處理：NOLOCK和ROWLOCK。

它們的使用方法如下：

SELECT COUNT(UserID)
FROM Users WITH (NOLOCK)
WHERE Username LIKE ‘foobar‘

和

UPDATE Users WITH (ROWLOCK)
SET Username = ‘fred‘ WHERE Username = ‘foobar‘

NOLOCK的使用

NOLOCK可以忽略鎖，直接從資料庫讀取資料。這意味著可以避開鎖，從而提高效能和擴充性。但同時也意味著代碼出錯的可能性存在。你可能會讀取到運行事務正在處理的無須驗證的未遞交資料。 這種風險可以量化。

如果是金融方面的代碼或者一些非常規的總計(你想絕對保證安全性)，你應該小心行事並且不使用這種技術。 但是我認為使用該技術會比你90%應用系統效能要好，當使用者(或者是互動代碼)發現一個未遞交的修改時，使用技術會保證不會像未使用該技術那樣引起大麻煩。實際上，你可能發現你的大多數資料很少或者甚至不進行 修改的，這樣我們就不會因為這些資料被鎖住而浪費大量的時間。

例如，如果你想統計在2000年6月份到8月份之間加入Streamload.com的所有使用者，就沒有理由去鎖住任何記錄： 2000年9月1號一到來，這個使用者數就是確定的。又例如要列舉在Streamload.com的檔案清單：這種結果即使 不是100%的正確，也不是大問題。因為你要麼不擁有該檔案，當然也無所謂你是否能找到它，或者你確實擁有該檔案，這種情況下你當然知道你是否修改了該檔案，以及該檔案是否已經上傳完畢了。

但是，如果這些資料的修改，對資料庫來說是基礎性的修改，或者這些資料對於使用者來說，必須是百分之百保證 是修改正確的(例如帳單或者餘額資料)，那麼你不要使用該技術。

ROWLOCK的使用

ROWLOCK告訴SQL Server只使用行級鎖。ROWLOCK文法可以使用在SELECT,UPDATE和DELETE語句中，不過 我習慣僅僅在UPDATE和DELETE語句中使用。如果在UPDATE語句中有指定的主鍵，那麼就總是會引發行級鎖的。但是當SQL Server對幾個這種UPDATE進行批處理時，某些資料正好在同一個頁面(page)，這種情況在當前情況下 是很有可能發生的，這就象在一個目錄中，建立檔案需要較長的時間，而同時你又在更新這些檔案。當頁面鎖引發後，事情就開始變得糟糕了。而如果在UPDATE或者DELETE時，沒有指定主鍵，資料庫當然認為很多資料會收到影響，那樣 就會直接引發頁面鎖，事情同樣變得糟糕。

通過指定使用行級鎖，這種情況可以得到避免。但是需要小心的是，如果你錯誤地使用在過多行上，資料庫並不會聰明到自動將行級鎖定擴大到頁面鎖，伺服器也會因為行級鎖的開銷而消耗大量的記憶體和CPU，直至無法響應。尤其主要留意的是 企業管理器中"管理/當前活動"(Management/Current Activity)這一項。該項會花較長的時間來載入鎖的資訊。這些資訊 時十分有用的，當你使用行級鎖後，你如果在"鎖/處理"(Locks/Processes)下看到幾百個鎖，一點都不奇怪，而恰恰應該慶幸鎖逾時和死結的問題減少了。

注意事項

我認為SQL Server傾向於使用NOLOCK關鍵字，而ROWLOCK關鍵字由使用者根據情況自行決定。你可以僅僅在 SELECT語句中使用NOLOCK，這些SELECT語句場合包括INNER查詢，以及在INSERT語句中的SELECT使用，在串連查詢下也可以使用，例如：

SELECT COUNT(Users.UserID)
FROM Users WITH (NOLOCK)
JOIN UsersInUserGroups WITH (NOLOCK) ON 
Users.UserID = UsersInUserGroups.UserID

NOLOCK 和 ROWLOCK的使用效果

很難去量化在使用NOLOCK和ROWLOCK後，Streamload.com或者你的網站效能到底改善了多少。 不過在使用NOLOCK和ROWLOCK前，Streamload.com的速度很慢，而且經常無法使用，以及很不穩定。使用後，就變得快速、容易訪問以及穩定了。兩者簡直就是天壤之別。這些改變當然無法在 關於鎖的文檔中很難找到。那些文檔會建議你重寫你的應用，當表資料被使用，鎖產生了(沒錯，就是這樣)，然後你應該使用小事務並且以批處理的形式執行(不錯，實際經驗就是如此)，使用低層級的隔離措施 (也沒錯，NOLOCK就是一個極端的例子)，還建議你有限的串連，從而讓處理器進行合作(好複雜的描述，而且總覺得怪怪的不像個好點子)。我不知道是否用資料庫諮詢師會提到本文中的技術(或類似的技術)， 但是我只想說的是，Streamload.com的健全狀態的確因為該技術得到了改善。如果你遇到了鎖爭用的問題，也可以試試NOLOCK和ROWLOCK。

申明

是否使用NOLOCK和ROWLOCK，需要自行判斷，並謹慎運用。我用該技術的方法是通過查看我的預存程序和即時查詢語句，在我自己的理解上來覺得哪裡用和如何用。我需要判斷如果用NOLOCK 而引起一些返回的不準確，或者ROWLOCK是否會造成太多的鎖，這些情況出現時，對於訪問者或者使用者來說，是否是可以接受的。在大多數情況下，我認為是沒有問題的，但是也許你的代碼不適用， 你需要小心對待。你需要建立一些獨立的過程，是否加鎖，如何加鎖，以作為對比。當UPDATE或者 DELETE查詢影響到很多資料行時，你在使用PAGELOCK,TABLOCK時也會遇到別的問題。

 附：－－－－－－－－－－－－－－－　UPDLOCK　　讀取表時使用更新鎖定，而不使用共用鎖定，並將鎖一直保留到語句或事務的結束。UPDLOCK 的優點是允許您讀取資料（不阻塞其它事務）並在以後更新資料，同時確保自從上次讀取資料後資料沒有被更改。　　這是SqlServer2000中對更新鎖定的說明.　　當我們用UPDLOCK來讀取記錄時可以對取到的記錄加上更新鎖定，從而加上鎖的記錄在其它的線程中是不能更改的只能等本線程的事務結束後才能更改，我如下樣本： BEGIN TRANSACTION --開始一個事務
SELECT Qty
 FROM myTable WITH (UPDLOCK)
 WHERE Id in (1,2,3)
 UPDATE myTable SET Qty = Qty - A.Qty
 FROM myTable  AS A 
 INNER JOIN  @_Table AS B ON A.ID = B.ID
COMMIT TRANSACTION --提交事務 　　這樣在更新時其它的線程或事務在這些語句執行完成前是不能更改ID是１，２，３的記錄的．其它的都可以修改和讀，１，２，３的只能讀，要是修改的話只能等這些陳述式完成後才能操作.從而保證的資料的修改正確.

瞭解SQL Server鎖爭用：NOLOCK 和 ROWLOCK 的秘密