sqlserver鎖機制（轉載）

[導讀: 各種大型資料庫所採用的鎖的基本理論是一致的，但在具體實現上各有差別。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．結構鎖 執行表的資料定義語言 (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來說是很重要的。[@more@]