|
相比于 SQL Server 2005（比如快照隔離和改進的鎖與死鎖監(jiān)視），SQL Server 2008 并沒有在鎖的行為和特性上做出任何重大改變。SQL Server 2008 引入的一個主要新特性是在表級控制鎖升級行為的能力。新的LOCK_ESCALATION表選項允許你啟用或禁用表級鎖升級。這個新特性能夠減少鎖競爭并且改善并發(fā)性，特別是對于分區(qū)表（partitioned tables）。 SQL Server 2008 的另一個改變是不再支持Locks configuration設定。同樣不再被支持的還有timestamp數(shù)據(jù)類型，它已被rowversion數(shù)據(jù)類型取代。 為什么需要鎖？在任何多用戶的數(shù)據(jù)庫中，必須有一套用于數(shù)據(jù)修改的一致的規(guī)則。對于真正的事務處理型數(shù)據(jù)庫，當兩個不同的進程試圖同時修改同一份數(shù)據(jù)時，數(shù)據(jù)庫管理系統(tǒng)（DBMS）負責解決它們之間潛在的沖突。任何關系數(shù)據(jù)庫必須支持事務的ACID屬性，即原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、永久性（Durability）。ACID屬性確保數(shù)據(jù)庫中的數(shù)據(jù)更改被正確地收集到一起，并且數(shù)據(jù)將保持在與所采取動作相一致的狀態(tài)。 鎖的主要作用是提供事務所需的隔離。隔離確保事務之間不會相互干擾，即，一個給定的事務不會讀取或修改正在被另一個事務修改的數(shù)據(jù)。此外，鎖提供的隔離性有助于保證事務間的一致性。沒有鎖，一致的事務處理是不可能的。 SQL Server 中的事務隔離級別隔離級別決定了一個事務中正被訪問或修改的數(shù)據(jù)受保護并免于被他事務修改的程度。理論上，每個事務都應該完全與其他事務隔離開來。然而，出于可行性和性能方面的原因，實踐中這幾乎是不可能做到的。在并發(fā)環(huán)境中如果沒有鎖和隔離，可能發(fā)生以下4種情況：
SQL Server 2008 支持6種隔離級別，分別是 （詳情請參考我的另一篇blog：SQL Server 2008 R2 事務與隔離級別實例講解） 鎖管理器解決不同用戶進程間鎖沖突的職責落到了SQL Server Lock Manager身上。SQL Server 自動地給進程分配鎖，以保證資源的當前用戶擁有該資源的一致視圖，從某個特定操作的開始至結束。Lock Manager 負責決定適當?shù)逆i類型（如shared, exclusive, update）和鎖粒度（如row, page,table），根據(jù)正在執(zhí)行的操作類型和所影響的數(shù)據(jù)量。 Lock Manager還管理試圖訪問同一資源的鎖類型之間的兼容性，解決死鎖，必要時升級鎖到一個更高的級別。 Lock Manager 為共享數(shù)據(jù)和內部系統(tǒng)資源管理鎖。對于共享數(shù)據(jù)，Lock Manager 管理表以及數(shù)據(jù)頁、文本頁、葉級索引頁上的行級鎖、頁級鎖和表級鎖。內部地，Lock Manager使用門閂（latch）來管理索引行和頁上的鎖控制對內部數(shù)據(jù)結構的訪問，以及在某些情況下，用于取回單個的數(shù)據(jù)行。門閂提供了更好的系統(tǒng)性能，因為它不像鎖那般資源密集。門閂也提供了比鎖更好的并發(fā)性。門閂典型地用于像頁拆分、索引行的刪除、索引中行的移動等操作。鎖與門閂之間最主要的區(qū)別在于，鎖在整個事務存續(xù)期間都被持有，而門閂僅在需要它的操作存續(xù)期間被持有。鎖用于保證數(shù)據(jù)的邏輯一致性，而門閂用于保證數(shù)據(jù)和數(shù)據(jù)結構的物理一致性。 SQL Server 鎖類型鎖在SQL Server中是自動處理的。Lock Manager 基于事務類型（如SELECT, INSERT, UPDATE, 或者DELETE)選擇鎖的類型.Lock Manager使用以下的鎖類型：除了選擇鎖類型，Lock Manager還基于所執(zhí)行語句的性質以及所影響的行數(shù)自動地調整鎖粒度（如row, page, table）。 共享鎖缺省地，SQL Server 為所有讀操作應用共享鎖。顧名思義，共享鎖不是獨占的。理論上，在任何時刻，一個資源上可以持有無限數(shù)量的共享鎖。此外，默認情況下，一個進程僅僅當資源正被讀取期間才會鎖定該資源，這時也只有唯一的共享鎖存在。比如SELECT * from authors，當查詢開始時，先鎖定authors表中的第一行；當?shù)谝恍斜蛔x取以后，它上面的鎖被釋放，并且了第二行上的鎖；第二行讀到以后，它上面的鎖被釋放，同時獲取了第三行上的鎖；以此類推。按此方式，一個SELECT查詢允許在讀操作期間修改那些沒有正在被讀取的數(shù)據(jù)行。這增強了數(shù)據(jù)訪問的并發(fā)性。共享鎖不僅與其他共享鎖兼容，也與更新鎖兼容。共享鎖不會阻止其他進程在一個給定的行或頁上獲取額外的共享鎖或更新鎖。任何時候事務多個事務或進程可以持有多個共享鎖，這些事務不會影響數(shù)據(jù)的一致性。然而，共享鎖確實會阻止獨占鎖的獲取。當行或頁上持有共享鎖的時候，任何試圖修改其數(shù)據(jù)的事務將被阻塞，直到 所有的共享鎖被釋放。 更新鎖更新鎖用于鎖定用戶進程想要修改的行或頁。當一個事務試圖修改某行時，它必須先讀取該行以確保它正在修改合適的記錄。假如事務先在資源上加了共享鎖，要修改該記錄，最終它將需要獲取該資源上的獨占鎖，以防止任何其他事務修改同一記錄。問題是，當多個事務試圖同時修改同一資源的時候這可能導致死鎖。如圖所示。 SQL Server中的更新鎖就是用來防止此類死鎖場景的。更新鎖是部分獨占的，就是說在任何時候任何資源上只能獲取唯一的更新鎖。然而，更新鎖兼容于共享鎖，即它們可以同時被同一資所獲取。事實上，更新鎖意味著一個進程想要修改某記錄，并且將也想修改該記錄的其他進程排除在外。然而，更新鎖允許其他進程獲取共享鎖以便讀取數(shù)據(jù)，直到UPDATE或DELETE語句完成被影響記錄的定位。之后，進程嘗試將每一個更新鎖升級為獨占鎖。這時候，進程等待該記錄上當前被持有的所有共享鎖釋放。當共享鎖全部釋放以后，共享鎖就被升級為獨占鎖。接著執(zhí)行數(shù)據(jù)修改，獨占鎖在事務的余下時間內一直被持有。 獨占鎖如前所述，當事務準備好要修改數(shù)據(jù)時，獨占鎖被分配給它。資源上的獨占鎖確保沒有其他任何事務能妨礙被持有獨占鎖的事務鎖定的數(shù)據(jù)。SQL Server在事務結束時釋放獨占鎖。獨占鎖與其他的所類型不兼容。如果資源持有了獨占鎖，那么任何其他進程對該資源的讀取或修改請求都將強制等待直到獨占鎖釋放為止。同樣地，如果其他進程當前持有該資源的讀取鎖（共享鎖或更新鎖），獨占鎖請求也被強制排隊等待直到資源變得可用為止。 意向鎖意向鎖并不正真的構成一種鎖定方式，而是充當一種機制，用以在較高的粒度級別上指示在較低（粒度）級別上所持有的鎖類型。有3種類型的意向鎖（分別對應于之前提到的3種鎖類型）：共享意向鎖、獨占意向鎖、更新意向鎖。舉個例子來說，某進程持有的表級共享意向鎖意味著，該進程當前在該表的行或頁級持有共享鎖。意向鎖的存在防止其他事務獲取與現(xiàn)存的行或頁級鎖不兼容的表級鎖的企圖。意向鎖提升了SQL Server鎖的性能。它允許在表級別檢查鎖來決定在該表的行或頁級持有的鎖類型，而不是在表中的行或頁級查遍多個鎖。 當監(jiān)視鎖活動時典型地你將看到3種類型的意向鎖：意向共享鎖（IS）、意向獨占鎖（IX）、意向獨占共享鎖（SIX）。 IS鎖表明，在低級別資源（行或頁）上，進程當前持有或有意圖持有共享鎖。 IX鎖表明，在低級別資源上，進程當前持有或有意圖持有獨占鎖。 SIX鎖出現(xiàn)在特殊情況下，當一個事務在資源上持有共享鎖，后來又需要意向獨占鎖（IX），這時候，S鎖被轉換成SIX鎖。 架構鎖SQL Server 使用架構鎖來保持表結構的完整性。不像其他提供數(shù)據(jù)隔離的鎖類型，架構鎖提供事務中對數(shù)據(jù)庫對象如表、視圖、索引的schema隔離。Lock Manager提供2種類型的架構鎖：架構穩(wěn)定性鎖（Sch-S）- 當事務引用了索引或數(shù)據(jù)頁時，SQL Server在對象上加Sch-S鎖。這確保當其他進程仍然引用著該對象時，沒有其他事務能夠修改該對象的Schema，如刪除索引或刪除、修改存儲過程或表。 架構修改鎖（Sch-M） - 當一個進程需要修改某對象的結構（如修改表，重編譯存儲過程）時， Lock Manager在對象上加Sch-M鎖。在鎖存在期間，沒有其他任何事務能夠引用該對象，直到（對象結構的）修改完成并提交為止。 大容量更新鎖（BU）大容量更新鎖是一種特殊類型的鎖，僅用于使用bcp實用程序或者BULK INSERT命令向表中大容量復制數(shù)據(jù)時。僅僅當給bcp或BULK INSERT命令指定了TABLOCK提示，或者使用 sp_tableoption 設置了 table lock on bulk load 表選項時,BU鎖才能用于大容量數(shù)據(jù)復制操作。大容量更新 (BU) 鎖允許多個 bulk copy 進程將數(shù)據(jù)并發(fā)地大容量復制到同一表，同時防止其它不進行大容量復制數(shù)據(jù)的進程訪問該表。如果有任何其他進程在該表上持有鎖，則不能給該表施加BU鎖。SQL Server 鎖粒度所謂所粒度，從本質上說就是，為了給事務提供完全的隔離和序列化，作為查詢或更新的一部分被鎖定的數(shù)據(jù)的總量（的大小）。Lock Manager需要在資源的并發(fā)訪問與維護大量低級別鎖的管理開銷之間取得平衡。比如，鎖的粒度越小，能夠同時訪問同一張表的并發(fā)用戶的數(shù)量就越大，不過維護這些鎖的管理開銷也越大。鎖的粒度越大，管理鎖需要的開銷就越少，而并發(fā)性也降低了。下圖說明了鎖的大小與并發(fā)性之間的權衡取舍。 當前，SQL Server通過在行或更高級別加鎖來平衡性能和并發(fā)性。基于各種因素，如key的分布，行的數(shù)量，行的密度，查詢參數(shù)（SARGs）等等，Query Optimizer內部地做出鎖粒度選擇，程序員不需要為此擔心。SQL Server提供了大量T_SQL擴展，使你能從鎖的角度來更好地控制查詢行為。 SQL Server 提供以下的鎖級別:
Serialization 與 Key-Range Locking如前所述， SQL Server 通過key-range鎖防止了“幻讀”。下面將介紹key-range鎖如何與各種鎖模式一起工作。Key-Range Locking for a Range Search在涉及范圍查找的key-range鎖的情況下，SQL Server 在查詢的WHERE子句所包含的數(shù)據(jù)范圍的索引頁上加鎖。（對于聚集索引，則是對表中的實際數(shù)據(jù)行加鎖。）因為該區(qū)間被鎖定了，不允許其他事務往那個區(qū)間內插入新的行。如下圖所示。 Key-Range Locking When Searching Nonexistent Rows在涉及此種類型的鎖的情況下，如果事務試圖刪除或讀取數(shù)據(jù)庫中不存在的行，那么在該事務的以后階段，該查詢也不應該找到任何行。如下圖所示。 行級鎖與頁級鎖之比較行級鎖是否優(yōu)于頁級鎖的的爭論持續(xù)了多年，在某些圈子里至今仍在繼續(xù)。許多人堅持認為如果數(shù)據(jù)庫和應用程序經過良好的設計和優(yōu)化，行級鎖是不必要的。這種觀點誕生于行級鎖甚至還不存在的時候。（在SQL Server 7.0 之前，能夠鎖定的最小數(shù)據(jù)單元是頁。）然而，那時候SQL Server 中頁的大小只有2KB。隨著頁大小擴大到8KB，單個頁中能夠包含更多數(shù)量的行（是先前的4倍）。8KB頁上的鎖可能導致更多的頁級競爭，因為不同進程請求同一個頁上數(shù)據(jù)行的可能性變得更大了。使用行級鎖將增加數(shù)據(jù)訪問的可并發(fā)性。另一方面，行級鎖比頁級鎖占用更多的資源（內存和CPU），因為表中的行比頁數(shù)量更多。如果進程需要訪問頁上的所有行，鎖定整個頁比每行獲取一個鎖更加高效。這將減少Lock Manager需要管理的內存中鎖結構的數(shù)量。 哪一個更優(yōu) -- 更好的并發(fā)性還是較低的管理開銷？如前所述，這二者間需要平衡。當鎖的粒度變小，并發(fā)性就會得到提升，但性能會因額外的開銷而降低。隨著鎖粒度變大，性能因管理開銷的降低而得到提升，但是并發(fā)性降低了。取決于應用程序、數(shù)據(jù)庫設計和數(shù)據(jù)（量的大?。?，行級鎖與頁級鎖哪個更合適得具體分析。 SQL Server 在運行時自動地做出決一開始是鎖定行、頁還是整個表，基于查詢的性質、表的大小、預計被影響的行的數(shù)量。一般地，SQL Server 更經常地嘗試先應用行級鎖而非頁級鎖，以便提供最佳的并發(fā)性。今天有了更快速的CPU和更大內存的支持，行級鎖的管理開銷不再像過去那樣昂貴。然而，當查詢進程和實際被鎖定的資源數(shù)量超過一定的閥值，SQL Server可能會嘗試從低級別鎖升級至適當?shù)母呒墑e。 鎖競爭與死鎖SQL Server應用程序性能問題的最可能的原因是糟糕的查詢語句、糟糕的數(shù)據(jù)庫和索引設計、以及鎖競爭。前2個問題無論系統(tǒng)的用戶多少都會導致糟糕的應用程序性能；而鎖競爭導致的性能問題隨著用戶數(shù)量的增加而顯現(xiàn)出來，隨著事務越來越復雜或者運行時間越來越長而更加趨于復雜化。當一個事務請求的鎖類型與該資源上現(xiàn)存的鎖類型不兼容時，鎖競爭就發(fā)生了。默認地，進程無限期地等待鎖資源變得可用。如果客戶端應用程序中來自 SQL Server 的響應明顯不足，你應該警惕鎖競爭（問題）。 下圖演示了一個鎖競爭的例子。  設置鎖超時間隔如果你不想讓進程無限期等待鎖變得可用， SQL Server 允許你使用SET LOCK_TIMEOUT命令設定鎖超時間隔。你以毫秒為單位指定超時間隔。比如，如果你想讓進程在鎖變得可用前僅等待5秒，那么執(zhí)行以下命令SET LOCK_TIMEOUT 5000 如果請求鎖資源超時的話，語句將會中止，你將得到以下Error Message： Server: Msg 1222, Level 16, State 52, Line 1 Lock request time out period exceeded. 查看當前 LOCK_TIMEOUT 設置，可以使用系統(tǒng)函數(shù)@@lock_timeout。 select @@lock_timeout 如果你希望當不能獲得鎖時進程立即中止，則 set LOCK_TIMEOUT 0 如果你想要將timeout重新置為無限期，則 set LOCK_TIMEOUT -1 最小化鎖競爭為了最大化并發(fā)性和應用程序性能，你應該盡可能最小化進程間的鎖競爭。下面是一些一般性指導原則：盡可能然事務保持運行時間短和簡潔。事務持有鎖的時間越短，鎖競爭發(fā)生的機會就越少；將不是事務所管理的工作單元鎖必需的命令移出事務。 將組成事務的語句作為一個的單獨的批命令處理，以消除 BEGIN TRAN 和 COMMIT TRAN 語句之間的網(wǎng)絡延遲造成的不必要的延遲。 考慮完全地使用存儲過程編寫事務代碼。典型地，存儲過程比批命令運行更快。 在游標中盡可早地Commit更新。因為游標處理比面向集合的處理慢得多，因此導致鎖被持有的時間更久。 使用每個進程所需的最低級別的鎖隔離。比如說，如果臟讀是可接受的并且不要求結果必須精確，那么可以考慮使用事務隔離級別0（Read Uncommitted）,僅在絕對必要時才使用Repeatable Read or Serializable隔離級別。 在 BEGIN TRAN 和 COMMIT TRAN 語句之間，絕不允許用戶交互，因為這樣做可能鎖被持有無限期的時間。 最小化表中的“熱點”。當表中的大多數(shù)Update活動發(fā)生在少量的頁中時，熱點出現(xiàn)了。 死鎖當兩個進程各自都在等在對方當前鎖定的資源時，死鎖就發(fā)生了。兩個進程在獲得所請求資源上的鎖之前既不能前進，也不能釋放當前持有的鎖。SQL Server 中可能發(fā)生2種類型的死鎖： 人們經常以為死鎖發(fā)生在數(shù)據(jù)頁級或數(shù)據(jù)行級。事實上，死鎖經常發(fā)生在索引頁級或索引鍵級。下圖展示了由于索引鍵級的競爭引發(fā)的死鎖場景。  SQL Server自動地偵測何時死鎖情況發(fā)生。SQL Server 中一個獨立的進程叫做LOCK_MONITOR，大約每5秒鐘檢查一次系統(tǒng)是否存在死鎖。 避免死鎖遵循前文給出的最小化鎖競爭指導原則，有助于消除死鎖。此外，當設計應用程序是你還需要遵循下列指導原則：按照一致的順序訪問多個表的數(shù)據(jù)以避免循環(huán)死鎖。 最小化HOLDLOCK的使用，或者最小化運行于Repeatable Read 或者 Serializable 隔離模式下的查詢。這將有助于避免轉換死鎖。 明智而審慎地選擇事物隔離級別。選擇較低的隔離級別或許能減少死鎖。 Table Hints for Locking前面提到過，你可以使用SET TRANSACTION ISOLATION LEVEL 命令為連接設置隔離級別。該命令為整個會話設定了全局的隔離級別，如果你想要為應用程序提供一致的隔離級別，這很有用。然而，有時候你也想要許為特定的查詢或者單個查詢中的不同表指定不同的隔離級別。SQL Server 允許你在 SELECT, MERGE, UPDATE, INSERT, 和 DELETE 語句中使用表提示來實現(xiàn)此目的。這樣一來，你在會話級別改變了當前的隔離級別。用于改變表級鎖隔離、粒度或者鎖類型的表提示，通過 SELECT, UPDATE, INSERT, 和 DELETE 語句的 WITH 操作符提供。 注意： 盡管許多表提示是可以組合使用的，但是，你不能一次在一個表上組合超過一個隔離級別或者鎖粒度的提示。另外，NOLOCK, READUNCOMMITTED, 和 READPAST 提示不能用于 INSERT, UPDATE, MERGE, 或 DELETE 語句的目標表上。 Transaction Isolation–Level HintsSQL Server 提供了許多提示用于在查詢中改變默認的事務隔離級別。
Lock Granularity Hints用于改變鎖粒度：
Lock Type Hints用于改變SQL Server 使用的鎖類型：
樂觀鎖許多應用程序中，客戶端需要讀取數(shù)據(jù)用于瀏覽，然后修改其中的一些行并將修改提交回SQL Server 數(shù)據(jù)庫。讀取數(shù)據(jù)和提交更改后的數(shù)據(jù)之間的時間間隔可能很長（假如用戶讀取數(shù)據(jù)后去吃午飯了）。在這類應用程序中，你不愿使用如SERIALIZABLE或HOLDLOCK鎖模式來鎖定數(shù)據(jù)，因為從用戶讀取數(shù)據(jù)到提交更新的期間，沒有人能更改它。這違背了最小化鎖競爭和死鎖的原則--不允許事務中的用戶交互。在多用戶的OLTP環(huán)境下，由于所阻塞和鎖競爭，無限期持有共享鎖將對并發(fā)性和應用的整體性能有重大影響。 另一方面，如果不在被讀取的行上加鎖，在這期間另一個進程可能會更新其中某一行數(shù)據(jù)，當?shù)谝粋€進程提交它的更新時，將覆蓋另一個進程先前所做的更改，從而導致Lost Update。 那么，該如何實現(xiàn)這樣的應用程序呢？怎樣讓用戶讀取數(shù)據(jù)而無需鎖定數(shù)據(jù)并仍能保證不會發(fā)生Lost Update呢？ 樂觀鎖就是在讀取數(shù)據(jù)與提交更改之間時間間隔很久的情況下使用的技術。樂觀鎖避免了一個客戶端覆蓋另一個客戶端對數(shù)據(jù)的修改并且無需持有數(shù)據(jù)庫中的鎖。 實現(xiàn)樂觀鎖有2個辦法，其一是使用rowversion數(shù)據(jù)類型，其二是利用snapshot隔離的樂觀并發(fā)性特性。 使用rowversion數(shù)據(jù)類型實現(xiàn)樂觀鎖SQL Server 2008 提供了一個特殊數(shù)據(jù)類型rowversion,它可以用于在應用程序中實現(xiàn)樂觀鎖。rowversion數(shù)據(jù)類型在樂觀鎖模式下充當版本號。無論何時包含rowversion類型數(shù)據(jù)列的行被插入或更新時，SQL Server 自動為該列生成一個值。rowversion數(shù)據(jù)類型是8字節(jié)的二進制數(shù)據(jù)類型，除了保證值的唯一性和單向增長外，它的值不具有意義。你不能夠查看它的每個字節(jié)來搞懂它是什么意思。客戶端從表中讀取數(shù)據(jù)，確保返回的結果集中包含了主鍵和rowversion列，以及其他想要的數(shù)據(jù)列。由于查詢并不運行在事務中，一旦數(shù)據(jù)被讀取，SELECT查詢獲取的鎖即被釋放。當一段時間過后用戶想要更新某行時，必須確保在此期間該數(shù)據(jù)沒有被其他客戶端修改過。Update語句必須包含WHERE子句用以比較取回的rowversion值與數(shù)據(jù)庫中該列的當前值。如果兩個值匹配（即相同），說明該行記錄在此期間沒有被修改過。因此可以放心提交更改。如果不匹配，則說明該行記錄已經被修改過。為了避免Lost Update問題發(fā)生，不應提交本次更新。 下面是一個完整實現(xiàn)的示例代碼。  使用Snapshot隔離級別的樂觀鎖SQL Server 2008 的Snapshot隔離模式通過自動的row versioning提供了實現(xiàn)樂觀鎖的另一種機制。當Snapshot隔離模式啟用時，如果一個進程在事務中讀取數(shù)據(jù)，當前版本的數(shù)據(jù)行上不會獲得或持有鎖。進程讀取的是查詢發(fā)生時候的數(shù)據(jù)版本。由于數(shù)據(jù)行沒有被鎖定，因而不會導致阻塞，其他進程在數(shù)據(jù)被讀取后可以修改它。如果另外的進程修改了該數(shù)據(jù)行，就會產生該行的一個新版本。如果第一個進程這時試圖更新該數(shù)據(jù)行，SQL Server 通過檢查 row version 自動地防止了Lost Update問題。由于 row version 不同，SQL Server阻止第一個進程修改該數(shù)據(jù)行。如果試圖修改，將出現(xiàn)類似于以下錯誤消息：
|
|
|
