原文出處 : http://www.ithome.com.tw/plog/index.php?op=ViewArticle&articleId=6287&blogId=620

淺談SQL Server的鎖定原理
neo_lin_42 | 02 Oct, 2006 10:21

在說明SQL Server的鎖定原理之前,首先來看一個基本的問題,為什麼要使用鎖定?我們可以從逆向思考的方式來回答這個問題,也就是不使用鎖定,會出現什麼問題,特別是當多人同時存取資料時,若不使用鎖定,可能會發生lost update、dirty read、nonrepeatable read與phantoms read等問題。這也是為什麼要使用鎖定的主要原因。

談到鎖定,就不得不談到交易處理(transaction)。所謂交易處理是指在一個工作單元中,執行的一系列資料操作。ATM跨行轉帳就是一個很典型的例子,這個工作單元中包含從轉出帳戶扣款與將金額存入轉入帳號兩個操作,這兩個操作必須有一致的結果,不是全部成功,確認最終狀態;就是全部失敗,回復原始狀態,沒有部分成功,部分失敗的情況,只有轉出沒有轉入或只有轉入沒有轉出都是不允許的。也就是交易處理具有不可分割性(Atomicity)、一致性(Consistency)、隔離性(Isolation)以及持久性(Durability)等特性。而鎖定機制便是為了實現交易的隔離性,好像此時資料庫系統是專屬你一個人的。

鎖定的範圍
在SQL Server可以鎖定哪些資源?依據鎖定的範圍可以分成RID 列級鎖定、Key 索引鎖定、Page 頁級鎖定、Extent 擴展鎖定(每8個Page為1個extent,用於SQL Server儲存空間的分配)、Table 表級鎖定,以及Database 資料庫級鎖定。

鎖定的範圍越小,耗用的資源越大;鎖定的範圍越大,耗用的資源越小。列級鎖定可以增加同時線上(concurrent)數量,但卻增加資源花費;頁級或表級鎖定則正好相反。SQL Server會衡量目前的concurrent數量與系統資源,自動管理鎖定的範圍,同時為了獲得最佳的鎖定效能,SQL Server提供一種動態鎖定的機制,也就適當列級鎖定耗用的資源達到某個程度時,SQL Server會自動將列級鎖定升級成頁級或表級鎖定。

鎖定的類型

SQL Server鎖定可以分成基本鎖定與特殊鎖定兩大類。基本鎖定又可分成共用鎖定(shared lock)與獨占鎖定(exclusive lock) 。一般而言,當Select資料時會使用共用鎖定,Insert、Update和Delete資料時會使用獨佔鎖定。特殊鎖定又可分成意圖鎖定(Intent lock)、更新鎖定(Update lock)、綱要鎖定(Schema lock)與大量新鎖定(Bulk update lock)。接下來,對常用的鎖定類型,簡單說明如下:

共用鎖定
當讀取資料時,SQL Server會使用共用鎖定,即使尚未結束,其他交易也可以獲得共用鎖定,也就是說多個交易可以同時讀取table、page、key和row。

獨佔鎖定
當修改資料時,SQL Server會使用獨佔鎖定。在交易結束之前,其他交易的鎖定請求都會被拒絕。一個資源只能有一個獨佔鎖定,當一個交易對某個資源進行獨占鎖定時,其他交易無法讀取該資源,由此可知,這種鎖定會限制了同時線上數量。
更新鎖定是共用鎖定與獨占鎖定的混合體。在修改之前會使用共用鎖定,其他交易可以讀取被鎖定的資料,但不可以修改。一旦開始修改時,就變成了獨占鎖定,其他交易無法讀取和更新被鎖定的資料,直到交易結束。

一個鎖定可以與另一個鎖定同時使用,這種特性稱為鎖定的相容性。相反的,若一個交易正在操作而鎖定資料,另一個交易必須等前一個交易結束後,才能繼續,則稱為不相容。相容性越好,表示支援的同時線上數量越大。哪些鎖定式相容的?哪些鎖定又是不相容的?一般的原則如下:
共用鎖定可以與獨占鎖定之外的其他鎖定相容。多個交易可以在相同資料上獲得共用鎖定,但沒有一個交易可以在已經獲得共用鎖定的資料上獲得獨佔鎖定。

獨占鎖定不可以其他鎖定相容。

更新鎖定只能與共用鎖定相容。

我們可以使用交易的隔離級別來設定鎖定的策略,以防止前面提到多人存取所可能出現的問題。SQL Server提供下列4種交易隔離級別,其特性簡單說明如下。
read uncommitted 會出現dirty read、non-repeatable read與phantoms等問題。
read committed 會出現non-repeatable read與phantoms等問題。
repeatable read 會出現phantoms的問題。
serializable 無

若未指定SQL Server預設使用read committed。在SQL Server 2005,提供了另外兩個基於資料版本的隔離級別,snapshot與Read Committed using Statement-level Snapshot(RCSI),使得讀取與更新的操作不會互相影響,加快交易的執行速度。

除了隔離級別的鎖定策略之外,您也可以使用鎖定提示來控制資料表的鎖定行為,以及覆蓋目前交易的隔離級別的鎖定策略。當隔離級別的鎖定策略與鎖定提示相衝突時, SQL Server是採用級別越小越優先的處理原則。例如,使用serializable隔離級別,若在交易中使用with (nolock)讀取資料,SQL Server會允許其他交易讀取資料。可用的表級鎖定提示摘要如下:
holdlock:當交易開始之後,交易結束之前,不允許其他交易讀取被holdlock的資料。
nolock:不使用任何的鎖定。
使用rowlock、paglock,tablock與tablockx來指定資料表的鎖定類型和大小。例如使用tablockx表示當讀取資料列時,在資料列所在的資料表上使用獨占索定。
readpast:忽略鎖定的資料列
updlock:當讀取資料使用更新鎖定,而不是預設的共用鎖定。當交易尚未結束前,不允許其他交易更新被updlock的資料,可避免non-repeatable read。

您可以根據個別的需要來組合這些鎖定提示,以獲得更加靈活、更有彈性的應用。例如,with (paglock, holdlock)表示在資料列所在的資料頁上使用hold lock。

有關SQL Server的鎖定原理,拉哩拉喳地介紹到此,相信大家對SQL Server的鎖定已有初步的認識。至於進一步的實作細節,還是那一句老話,多翻翻SQL Server的線上叢書囉!!
創作者介紹

A little IT experience/study/share garden

cbw0731 發表在 痞客邦 PIXNET 留言(0) 人氣()