sql server鎖機制

[導讀: 各種大型資料庫所採用的鎖的基本理論是一致的，但在具體實現上各有差別。SQL Server更強調由系統來管理鎖。在使用者有SQL請求時，系統分析請求，自動在滿足鎖定條件和系統效能之間為資料庫加上適當的鎖，同時系統在運行期間常常自動進行最佳化處理，實行動態加鎖。對於一般的使用者而言，通過系統的自動鎖定管理機制基本可以滿足使用要求，但如果對資料安全、資料庫完整性和一致性有特殊要求，就需要瞭解SQL Server的鎖機制，掌握資料庫鎖定方法。]  

    鎖是資料庫中的一個非常重要的概念，它主要用於多使用者環境下保證資料庫完整性和一致性。 我們知道，多個使用者能夠同時操縱同一個資料庫中的資料，會發生資料不一致現象。即如果沒有鎖定且多個使用者同時訪問一個資料庫，則當他們的事務同時使用相同的資料時可能會發生問題。這些問題包括：丟失更新、髒讀、不可重複讀取和幻覺讀：
1．當兩個或多個事務選擇同一行，然後基於最初選定的值更新該行時，會發生丟失更新問題。每個事務都不知道其它事務的存在。最後的更新將重寫由其它事務所做的更新，這將導致資料丟失。例如，兩個編輯人員製作了同一文檔的電子複本。每個編輯人員獨立地更改其複本，然後儲存更改後的複本，這樣就覆蓋了原始文檔。最後儲存其更改複本的編輯人員覆蓋了第一個編輯人員所做的更改。如果在第一個編輯人員完成之後第二個編輯人員才能變更，則可以避免該問題。
2. 髒讀就是指當一個事務正在訪問資料，並且對資料進行了修改，而這種修改還沒有提交到資料庫中，這時，另外一個事務也訪問這個資料，然後使用了這個資料。因為這個資料是還沒有提交的資料，那麼另外一個事務讀到的這個資料是髒資料，依據髒資料所做的操作可能是不正確的。例如，一個編輯人員正在更改電子文檔。在更改過程中，另一個編輯人員複製了該文檔（該複本包含到目前為止所做的全部更改）並將其分發給預期的使用者。此後，第一個編輯人員認為目前所做的更改是錯誤的，於是刪除了所做的編輯並儲存了文檔。分發給使用者的文檔包含不再存在的編輯內容，並且這些編輯內容應認為從未存在過。如果在第一個編輯人員確定最終更改前任何人都不能讀取更改的文檔，則可以避免該問題。
3．不可重複讀取是指在一個事務內，多次讀同一資料。在這個事務還沒有結束時，另外一個事務也訪問該同一資料。那麼，在第一個事務中的兩次讀資料之間，由於第二個事務的修改，那麼第一個事務兩次讀到的的資料可能是不一樣的。這樣就發生了在一個事務內兩次讀到的資料是不一樣的，因此稱為是不可重複讀取。例如，一個編輯人員兩次讀取同一文檔，但在兩次讀取之間，作者重寫了該文檔。當編輯人員第二次讀取文檔時，文檔已更改。原始讀取不可重複。如果只有在作者全部完成編寫後編輯人員才可以讀取文檔，則可以避免該問題。
4．幻覺讀是指當事務不是獨立執行時發生的一種現象，例如第一個事務對一個表中的資料進行了修改，這種修改涉及到表中的全部資料行。同時，第二個事務也修改這個表中的資料，這種修改是向表中插入一行新資料。那麼，以後就會發生操作第一個事務的使用者發現表中還有沒有修改的資料行，就好象發生了幻覺一樣。例如，一個編輯人員更改作者提交的文檔，但當生產部門將其更改內容合并到該文檔的主複本時，發現作者已將未編輯的新材料添加到該文檔中。如果在編輯人員和生產部門完成對原始文檔的處理之前，任何人都不能將新材料添加到文檔中，則可以避免該問題。
所以，處理多使用者並發訪問的方法是加鎖。鎖是防止其他事務訪問指定的資源控制、實現並發控制的一種主要手段。當一個使用者鎖住資料庫中的某個對象時，其他使用者就不能再訪問該對象。加鎖對並發訪問的影響體現在鎖的粒度上。為了控制鎖定資源，應該首先瞭解系統的空間管理。在SQL Server 2000系統中，最小的空間管理單位是頁，一個頁有8K。所有的資料、日誌、索引都存放在頁上。另外，使用頁有一個限制，這就是表中的一行資料必須在同一個頁上，不能跨頁。頁上面的空間管理單位是盤區，一個盤區是8個連續的頁。表和索引的最小佔用單位是盤區。資料庫是由一個或者多個表或者索引組成，即是由多個盤區組成。放在一個表上的鎖限制對整個表的並發訪問；放在盤區上的鎖限制了對整個盤區的訪問；放在資料頁上的鎖限制了對整個資料頁的訪問；放在行上的鎖只限制對該行的並發訪問。

SQL Server 2000 具有多粒度鎖定，允許一個事務鎖定不同類型的的資源。為了使鎖定的成本減至最少，SQL Server 自動將資源鎖定在適合任務的層級。鎖定在較小的粒度（例如行）可以增加並發但需要較大的開銷，因為如果鎖定了許多行，則需要控制更多的鎖。鎖定在較大的粒度（例如表）就並發而言是相當昂貴的，因為鎖定整個表限制了其它事務對錶中任意部分進行訪問，但要求的開銷較低，因為需要維護的鎖較少。SQL Server 可以鎖定行、頁、擴充盤區、表、庫等資源。
行是可以鎖定的最小空間, 行級鎖佔用的資料資源最少，所以在事務的處理過程中，允許其他事務繼續操縱同一個表或者同一個頁的其他資料，大大降低了其他事務等待處理的時間，提高了系統的並發性。
頁級鎖是指在事務的操縱過程中，無論交易處理資料的多少，每一次都鎖定一頁，在這個頁上的資料不能被其他事務操縱。在SQL Server 7.0以前，使用的是頁級鎖。頁級鎖鎖定資源比行級鎖鎖定的資料資源多。在頁級鎖中，即使是一個事務只操縱頁上的一行資料，那麼該頁上的其他資料行也不能被其他事務使用。因此，當使用頁級鎖時，會出現資料的浪費現象，也就是說，在同一個頁上會出現資料被佔用卻沒有使用的現象。在這種現象中，資料的浪費最多不超過一個頁上的資料行。
表級鎖也是一個非常重要的鎖。表級鎖是指事務在操縱某一個表的資料時，鎖定了這個資料所在的整個表，其他事務不能訪問該表中的其他資料。當交易處理的資料量比較大時，一般使用表級鎖。表級鎖的特點是使用比較少的系統資源，但是卻佔用比較多的資料資源。與行級鎖和頁級鎖相比，表級鎖佔用的系統資源例如記憶體比較少，但是佔用的資料資源卻是最大。在表級鎖時，有可能出現資料的大量浪費現象，因為表級鎖鎖定整個表，那麼其他的事務都不能操縱表中的其他資料。
盤區鎖是一種特殊類型的鎖，只能用在一些特殊的情況下。簇級鎖就是指事務佔用一個盤區，這個盤區不能同時被其他事務佔用。例如在建立資料庫和建立表時，系統分配物理空間時使用這種類型的鎖。系統是按照盤區分配空間的。當系統分配空間時，使用盤區鎖，防止其他事務同時使用同一個盤區。當系統完成分配空間之後，就不再使用這種類型的盤區鎖。特別是，當涉及到對資料操作的事務時，不使用盤區鎖。
資料庫級鎖是指鎖定整個資料庫，防止任何使用者或者事務對鎖定的資料庫進行訪問。資料庫級鎖是一種非常特殊的鎖，它只是用於資料庫的恢複操作過程中。這種等級的鎖是一種最高等級的鎖，因為它控制整個資料庫的操作。只要對資料庫進行恢複操作，那麼就需要設定資料庫為單一使用者模式，這樣系統就能防止其他使用者對該資料庫進行各種操作。
行級鎖是一種最優鎖，因為行級鎖不可能出現資料既被佔用又沒有使用的浪費現象。但是，如果使用者事務中頻繁對某個表中的多條記錄操作，將導致對該表的許多記錄行都加上了行級鎖，資料庫系統中鎖的數目會急劇增加，這樣就加重了系統負荷，影響系統效能。因此，在SQL Server中，還支援鎖定擴大(lock escalation)。所謂鎖定擴大是指調整鎖的粒度，將多個低粒度的鎖替換成少數的更高粒度的鎖，以此來降低系統負荷。在SQL Server中當一個事務中的鎖較多，達到鎖定擴大門限時，系統自動將行級鎖和頁面鎖定擴大為表級鎖。特別值得注意的是，在SQL Server中，鎖的升級門限以及鎖定擴大是由系統自動來確定的，不需要使用者佈建。
       在SQL  Server資料庫中加鎖時，除了可以對不同的資源加鎖，還可以使用不同程度的加鎖方式，即鎖有多種模式，SQL Server中鎖模式包括：    
1．共用鎖定     SQL Server中，共用鎖定用於所有的唯讀資料操作。共用鎖定是非獨佔的，允許多個並發事務讀取其鎖定資源。預設情況下，資料被讀取後，SQL Server立即釋放共用鎖定。例如，執行查詢“SELECT  *  FROM  AUTHORS”時，首先鎖定第一頁，讀取之後，釋放對第一頁的鎖定，然後鎖定第二頁。這樣，就允許在讀操作過程中，修改未被鎖定的第一頁。但是，交易隔離等級串連選項設定和SELECT語句中的鎖定設定都可以改變SQL Server的這種預設設定。例如，“ SELECT  *  FROM  AUTHORS  HOLDLOCK”就要求在整個查詢過程中，保持對錶的鎖定，直到查詢完成才釋放鎖定。   
 2．更新鎖定     更新鎖定在修改操作的初始化階段用來鎖定可能要被修改的資源，這樣可以避免使用共用鎖定造成的死結現象。因為使用共用鎖定時，修改資料的操作分為兩步，首先獲得一個共用鎖定，讀取資料，然後將共用鎖定升級為排它鎖，然後再執行修改操作。這樣如果同時有兩個或多個事務同時對一個事務申請了共用鎖定，在修改資料的時候，這些事務都要將共用鎖定升級為排它鎖。這時，這些事務都不會釋放共用鎖定而是一直等待對方釋放，這樣就造成了死結。如果一個資料在修改前直接申請更新鎖定，在資料修改的時候再升級為排它鎖，就可以避免死結。
3．排它鎖     排它鎖是為修改資料而保留的。它所鎖定資源，其他事務不能讀取也不能修改。  
4．結構鎖     執行表的資料定義語言 (Data Definition Language) (DDL) 操作（例如添加列或除去表）時使用架構修改 (Sch-M) 鎖。當編譯查詢時，使用架構穩定性 (Sch-S) 鎖。架構穩定性 (Sch-S) 鎖不阻塞任何事務鎖，包括排它鎖。因此在編譯查詢時，其它事務（包括在表上有排它鎖的事務）都能繼續運行。但不能在表上執行 DDL 操作。
5．意圖鎖定     意圖鎖定說明SQL Server有在資源的低層獲得共用鎖定或排它鎖的意向。例如，表級的共用意圖鎖定說明事務意圖將排它鎖釋放到表中的頁或者行。意圖鎖定又可以分為共用意圖鎖定、獨佔意圖鎖定和共用式獨佔意圖鎖定。共用意圖鎖定說明事務意圖在共用意圖鎖定所鎖定的低層資源上放置共用鎖定來讀取資料。獨佔意圖鎖定說明事務意圖在共用意圖鎖定所鎖定的低層資源上放置排它鎖來修改資料。共用式排它鎖說明事務允許其他事務使用共用鎖定來讀取頂層資源，並意圖在該資源低層上放置排它鎖。    
6．大容量更新鎖定  當將資料大量複製到表，且指定了 TABLOCK 提示或者使用 sp_tableoption 設定了 table lock on bulk 表選項時，將使用大容量更新 鎖。大容量更新鎖定允許進程將資料並發地大量複製到同一表，同時防止其它不進行大量複製資料的進程訪問該表。
   
SQL Server系統中建議讓系統自動管理鎖，該系統會分析使用者的SQL語句要求，自動為該請求加上合適的鎖，而且在鎖的數目太多時，系統會自動進行鎖定擴大。如前所述，升級的門限由系統自動設定，並不需要使用者配置。 在實際應用中，有時為了應用程式正確運行和保持資料的一致性，必須人為地給資料庫的某個表加鎖。比如，在某應用程式的一個事務操作中，需要根據一編號對幾個資料表做統計操作，為保證統計資料時間的一致性和正確性，從統計第一個表開始到全部表結束，其他應用程式或事務不能再對這幾個表寫入資料，這個時候，該應用程式希望在從統計第一個資料表開始或在整個事務開始時能夠由程式人為地（顯式地）鎖定這幾個表，這就需要用到手工加鎖（也稱顯式加鎖）技術。
可以使用 SELECT、INSERT、UPDATE 和 DELETE 語句指定表級鎖定提示的範圍，以引導 Microsoft  SQL Server 2000 使用所需的鎖類型。當需要對對象所獲得鎖類型進行更精細控制時，使用表級鎖定提示更改預設的鎖定行為。
所指定的表級鎖定提示有如下幾種：   
1． HOLDLOCK: 在該表上保持共用鎖定，直到整個事務結束，而不是在語句執行完立即釋放所添加的鎖。    
2． NOLOCK：不添加共用鎖定和排它鎖，當這個選項生效後，可能讀到未提交讀的資料或“髒資料”，這個選項僅僅應用於SELECT語句。    
3． PAGLOCK：指定添加頁鎖（否則通常可能添加表鎖）。  
4． READCOMMITTED用與運行在提交讀隔離等級的事務相同的鎖語義執行掃描。預設情況下，SQL Server 2000 在此隔離等級上操作。。  
5． READPAST: 跳過已經加鎖的資料行，這個選項將使事務讀取資料時跳過那些已經被其他事務鎖定的資料行，而不是阻塞直到其他事務釋放鎖，READPAST僅僅應用於READ COMMITTED隔離性層級下事務操作中的SELECT語句操作。   
6． READUNCOMMITTED：等同於NOLOCK。    
7． REPEATABLEREAD：設定事務為可重複讀隔離性層級。  
8． ROWLOCK：使用行級鎖，而不使用粒度更粗的頁級鎖和表級鎖。   
9． SERIALIZABLE：用與運行在可串列讀隔離等級的事務相同的鎖語義執行掃描。等同於 HOLDLOCK。 
10． TABLOCK：指定使用表級鎖，而不是使用行級或頁面級的鎖，SQL Server在該語句執行完後釋放這個鎖，而如果同時指定了HOLDLOCK，該鎖一直保持到這個事務結束。    
11． TABLOCKX：指定在表上使用排它鎖，這個鎖可以阻止其他事務讀或更新這個表的資料，直到這個語句或整個事務結束。  
12． UPDLOCK ：指定在讀表中資料時設定更新 鎖（update lock）而不是設定共用鎖定，該鎖一直保持到這個語句或整個事務結束，使用UPDLOCK的作用是允許使用者先讀取資料（而且不阻塞其他使用者讀資料），並且保證在後來再更新資料時，這一段時間內這些資料沒有被其他使用者修改。   
死結問題
在資料庫系統中，死結是指多個使用者（進程）分別鎖定了一個資源，並又試圖請求鎖定對方已經鎖定資源，這就產生了一個鎖定請求環，導致多個使用者（進程）都處於等待對方釋放所鎖定資源的狀態。這種死結是最典型的死結形式, 例如在同一時間內有兩個事務A和B，事務A有兩個操作：鎖定表part和請求訪問表supplier；事務B也有兩個操作：鎖定表supplier和請求訪問表part。結果，事務A和事務B之間發生了死結。
    死結的第二種情況是，當在一個資料庫中時，有若干個長時間啟動並執行事務執行並行的操作，當查詢分析器處理一種非常複雜的查詢例如串連查詢時，那麼由於不能控制處理的順序，有可能發生死結現象。
    在SQL Server中，系統能夠自動定期搜尋和處理死結問題。系統在每次搜尋中標識所有等待鎖定請求的進程會話，如果在下一次搜尋中該被標識的進程仍處於等待狀態，SQL Server就開始遞迴死結搜尋。當搜尋檢測到鎖定請求環時，SQL Server 通過自動選擇可以打破死結的線程（死結犧牲品）來結束死結。SQL Server 復原作為死結犧牲品的事務，通知線程的應用程式（通過返回 1205 號錯誤資訊），取消線程的當前請求，然後允許不間斷線程的事務繼續進行。SQL Server 通常選擇運行撤消時花費最少的事務的線程作為死結犧牲品。另外，使用者可以使用 SET 語句將會話的 DEADLOCK_PRIORITY 設定為 LOW。DEADLOCK_PRIORITY 選項控制在死結情況下如何衡量會話的重要性。如果會話的設定為 LOW ，則當會話陷入死結情況時將成為首選犧牲品。
    理解了死結的概念，在應用程式中就可以採用下面的一些方法來盡量避免死結了： （1）合理安排表訪問順序。 （2）在事務中盡量避免使用者幹預，盡量使一個交易處理的任務少些, 保持事務簡短並在一個批處理中。 （3）資料訪問時域離散法, 資料訪問時域離散法是指在客戶機/伺服器結構中，採取各種控制手段控制對資料庫或資料庫中的對象訪問時間段。主要通過以下方式實現: 合理安排後台事務的執行時間，採用工作流程對後台事務進行統一管理。工作流程在管理工作時，一方面限制同一類任務的線程數（往往限制為1個），防止資源過多佔用; 另一方面合理安排不同任務執行時序、時間，盡量避免多個背景工作同時執行，另外， 避免在前台交易高峰時間運行背景工作。 （4）資料存放區空間離散法。資料存放區空間離散法是指採取各種手段，將邏輯上在一個表中的資料分散到若干離散的空間上去，以便改善對錶的訪問效能。主要通過以下方法實現: 第一，將大表按行或列分解為若干小表; 第二，按不同的使用者群分解。 （5）使用儘可能低的隔離性層級。隔離性層級是指為保證資料庫資料的完整性和一致性而使多使用者事務隔離的程度，SQL92定義了4種隔離性層級：未提交讀、提交讀、可重複讀和可串列。如果選擇過高的隔離性層級，如可串列，雖然系統可以因實現更好隔離性而更大程度上保證資料的完整性和一致性，但各事務間衝突而死結的機會大大增加，大大影響了系統效能。 （6）使用綁定串連,  綁定串連允許兩個或多個事務串連共用事務和鎖，而且任何一個事務串連要申請鎖如同另外一個事務要申請鎖一樣，因此可以允許這些事務共用資料而不會有加鎖的衝突。   

    總之，瞭解SQL Server的鎖機制，掌握資料庫鎖定方法, 對一個合格的DBA來說是很重要的。