MS SQL Server資料庫事務鎖機制分析(轉)

zhouwf0726發表於2019-05-28
SQLServer資料庫
MS SQL Server資料庫事務鎖機制分析■鎖是網路資料庫中的一個非常重要的概念，它主要用於多使用者環境下保證資料庫完整性和一致性。各種大型資料庫所採用的鎖的基本理論是一致的，但在具體實現上各有差別。目前，大多數資料庫管理系統都或多或少具有自我調節、自我管理的功能，因此很多使用者實際上不清楚鎖的理論和所用資料庫中鎖的具體實現。

Microsoft SQL Server（以下簡稱SQL Server）作為一種中小型資料庫管理系統，已經得到了廣泛的應用，該系統更強調由系統來管理鎖。在使用者有SQL請求時，系統分析請求，自動在滿足鎖定條件和系統效能之間為資料庫加上適當的鎖，同時系統在執行期間常常自動進行優化處理，實行動態加鎖。對於一般的使用者而言，通過系統的自動鎖定管理機制基本可以滿足使用要求，但如果對資料安全、資料庫完整性和一致性有特殊要求，就必須自己控制資料庫的鎖定和解鎖，這就需要了解SQL Server的鎖機制，掌握資料庫鎖定方法。

鎖的多粒度性以及鎖升級

資料庫中的鎖是指一種軟體機制，用來指示某個使用者（也即程式會話，下同）已經佔用了某種資源，從而防止其他使用者做出影響本使用者的資料修改或導致資料庫資料的非完整性和非一致性。這兒所謂資源，主要指使用者可以操作的資料行、索引以及資料表等。根據資源的不同，鎖有多粒度（multigranular）的概念，也就是指可以鎖定的資源的層次。SQL Server中能夠鎖定的資源粒度包括：資料庫、表、區域、頁面、鍵值（指帶有索引的行資料）、行識別符號（RID，即表中的單行資料）。

採用多粒度鎖的重要用途是用來支援併發操作和保證資料的完整性。SQL Server根據使用者的請求，做出分析後自動給資料庫加上合適的鎖。假設某使用者只操作一個表中的部分行資料，系統可能會只新增幾個行鎖（RID）或頁面鎖，這樣可以儘可能多地支援多使用者的併發操作。但是，如果使用者事務中頻繁對某個表中的多條記錄操作，將導致對該表的許多記錄行都加上了行級鎖，資料庫系統中鎖的數目會急劇增加，這樣就加重了系統負荷，影響系統效能。因此，在資料庫系統中，一般都支援鎖升級(lock escalation)。所謂鎖升級是指調整鎖的粒度，將多個低粒度的鎖替換成少數的更高粒度的鎖，以此來降低系統負荷。在SQL Server中當一個事務中的鎖較多，達到鎖升級門限時，系統自動將行級鎖和頁面鎖升級為表級鎖。特別值得注意的是，在SQL Server中，鎖的升級門限以及鎖升級是由系統自動來確定的，不需要使用者設定。

鎖的模式和相容性

在資料庫中加鎖時，除了可以對不同的資源加鎖，還可以使用不同程度的加鎖方式，即鎖有多種模式，SQL Server中鎖模式包括：

1．共享鎖

SQL Server中，共享鎖用於所有的只讀資料操作。共享鎖是非獨佔的，允許多個併發事務讀取其鎖定的資源。預設情況下，資料被讀取後，SQL Server立即釋放共享鎖。例如，執行查詢“SELECT * FROM my_table”時，首先鎖定第一頁，讀取之後，釋放對第一頁的鎖定，然後鎖定第二頁。這樣，就允許在讀操作過程中，修改未被鎖定的第一頁。但是，事務隔離級別連線選項設定和SELECT語句中的鎖定設定都可以改變SQL Server的這種預設設定。例如，“ SELECT * FROM my_table HOLDLOCK”就要求在整個查詢過程中，保持對錶的鎖定，直到查詢完成才釋放鎖定。

2．修改鎖

修改鎖在修改操作的初始化階段用來鎖定可能要被修改的資源，這樣可以避免使用共享鎖造成的死鎖現象。因為使用共享鎖時，修改資料的操作分為兩步，首先獲得一個共享鎖，讀取資料，然後將共享鎖升級為獨佔鎖，然後再執行修改操作。這樣如果同時有兩個或多個事務同時對一個事務申請了共享鎖，在修改資料的時候，這些事務都要將共享鎖升級為獨佔鎖。這時，這些事務都不會釋放共享鎖而是一直等待對方釋放，這樣就造成了死鎖。如果一個資料在修改前直接申請修改鎖，在資料修改的時候再升級為獨佔鎖，就可以避免死鎖。修改鎖與共享鎖是相容的，也就是說一個資源用共享鎖鎖定後，允許再用修改鎖鎖定。

3．獨佔鎖

獨佔鎖是為修改資料而保留的。它所鎖定的資源，其他事務不能讀取也不能修改。獨佔鎖不能和其他鎖相容。

4．結構鎖

結構鎖分為結構修改鎖（Sch-M）和結構穩定鎖（Sch-S）。執行表定義語言操作時，SQL Server採用Sch-M鎖，編譯查詢時，SQL Server採用Sch-S鎖。

5．意向鎖

意向鎖說明SQL Server有在資源的低層獲得共享鎖或獨佔鎖的意向。例如，表級的共享意向鎖說明事務意圖將獨佔鎖釋放到表中的頁或者行。意向鎖又可以分為共享意向鎖、獨佔意向鎖和共享式獨佔意向鎖。共享意向鎖說明事務意圖在共享意向鎖所鎖定的低層資源上放置共享鎖來讀取資料。獨佔意向鎖說明事務意圖在共享意向鎖所鎖定的低層資源上放置獨佔鎖來修改資料。共享式獨佔鎖說明事務允許其他事務使用共享鎖來讀取頂層資源，並意圖在該資源低層上放置獨佔鎖。

6．批量修改鎖

批量複製資料時使用批量修改鎖。可以通過表的TabLock提示或者使用系統儲存過程sp_tableoption的“table lock on bulk load”選項設定批量修改鎖。

另外，SQL Server命令語句操作會影響鎖定的方式，語句的組合也同樣能產生不同的鎖定，詳情如下表：

鎖衝突及其防止辦法

在資料庫系統中，死鎖是指多個使用者（程式）分別鎖定了一個資源，並又試圖請求鎖定對方已經鎖定的資源，這就產生了一個鎖定請求環，導致多個使用者（程式）都處於等待對方釋放所鎖定資源的狀態。

在SQL Server中，系統能夠自動定期搜尋和處理死鎖問題。系統在每次搜尋中標識所有等待鎖定請求的程式會話，如果在下一次搜尋中該被標識的程式仍處於等待狀態，SQL Server就開始遞迴死鎖搜尋。

(上接第D21版) 當搜尋檢測到鎖定請求環時，系統將根據各程式會話的死鎖優先順序別來結束一個優先順序最低的事務，此後，系統回滾該事務，並向該程式發出1205號錯誤資訊。這樣，其他事務就有可能繼續執行了。死鎖優先順序的設定語句為：

SET DEADLOCK_PRIORITY { LOW | NORMAL}

其中LOW說明該程式會話的優先順序較低，在出現死鎖時，可以首先中斷該程式的事務。另外，各程式中通過設定LOCK_TIMEOUT選項能夠設定程式處於鎖定請求狀態的最長等待時間。該設定的語句：

SET LOCK_TIMEOUT { timeout_period }

其中，timeout_period以毫秒為單位。

理解了死鎖的概念，在應用程式中就可以採用下面的一些方法來儘量避免死鎖了：

（1）合理安排表訪問順序。

（2）在事務中儘量避免使用者干預，儘量使一個事務處理的任務少些。

（3）採用髒讀技術。髒讀由於不對被訪問的表加鎖，而避免了鎖衝突。在客戶機/伺服器應用環境中，有些事務往往不允許讀髒資料，但在特定的條件下，我們可以用髒讀。

（4）資料訪問時域離散法。資料訪問時域離散法是指在客戶機/伺服器結構中，採取各種控制手段控制對資料庫或資料庫中的物件訪問時間段。主要通過以下方式實現: 合理安排後臺事務的執行時間，採用工作流對後臺事務進行統一管理。工作流在管理任務時，一方面限制同一類任務的執行緒數（往往限制為1個），防止資源過多佔用; 另一方面合理安排不同任務執行時序、時間，儘量避免多個後臺任務同時執行，另外， 避免在前臺交易高峰時間執行後臺任務。

（5）資料儲存空間離散法。資料儲存空間離散法是指採取各種手段，將邏輯上在一個表中的資料分散到若干離散的空間上去，以便改善對錶的訪問效能。主要通過以下方法實現: 第一，將大表按行或列分解為若干小表; 第二，按不同的使用者群分解。

（6）使用盡可能低的隔離性級別。隔離性級別是指為保證資料庫資料的完整性和一致性而使多使用者事務隔離的程度，SQL92定義了4種隔離性級別：未提交讀、提交讀、可重複讀和可序列。如果選擇過高的隔離性級別，如可序列，雖然系統可以因實現更好隔離性而更大程度上保證資料的完整性和一致性，但各事務間衝突而死鎖的機會大大增加，大大影響了系統效能。

（7）使用Bound Connections。Bound connections 允許兩個或多個事務連線共享事務和鎖，而且任何一個事務連線要申請鎖如同另外一個事務要申請鎖一樣，因此可以允許這些事務共享資料而不會有加鎖的衝突。

（8）考慮使用樂觀鎖定或使事務首先獲得一個獨佔鎖定。一個最常見的死鎖情況發生在系列號生成器中，它們通常是這樣編寫的：

begin tran

select new_id from keytab holdlock

update keytab set new_id＝new_id+l

commit tran

如果有兩個使用者在同時執行這一事務，他們都會得到共享鎖定並保持它。當兩個使用者都試圖得到keytab表的獨佔鎖定時，就會進入死鎖。為了避免這種情況的發生，應將上述事務重寫成如下形式：

begin tran

update keytab set new_id=new_id+l

select new_id from keytab

commit tran

以這種方式改寫後，只有一個事務能得到keytab的獨佔鎖定，其他程式必須等到第一個事務的完成，這樣雖增加了執行時間，但避免了死鎖。

如果要求在一個事務中具有讀取的可重複能力，就要考慮以這種方式來編寫事務，以獲得資源的獨佔鎖定，然後再去讀資料。例如，如果一個事務需要檢索出titles表中所有書的平均價格，並保證在update被應用前，結果不會改變，優化器就會分配一個獨佔的表鎖定。考慮如下的SQL程式碼：

begin tran

update titles set title_idid＝title_id ．

where 1＝2

if (selectavg(price)fromtitles)>$15

begin

/* perform some additional processing */

end

update titles set price＝price*1.10

where price
commit tran

在這個事務中，重要的是沒有其他程式修改表中任何行的price，或者說在事務結束時檢索的值與事務開始時檢索的值不同。這裡的where子句看起來很奇怪，但是不管你相信與否，這是迄今為止優化器所遇到的最完美有效的where子句，儘管計算出的結果總是false。當優化器處理此查詢時，因為它找不到任何有效的SARG，它的查詢規劃就會強制使用一個獨佔鎖定來進行表掃描。此事務執行時，where子句立即得到一個false值，於是不會執行實際上的掃描，但此程式仍得到了一個獨佔的表鎖定。

因為此程式現在已有一個獨佔的表鎖，所以可以保證沒有其他事務會修改任何資料行，能進行重複讀，且避免了由於holdlock所引起的潛在性死鎖。但是，要避免死鎖，不可能不付出代價。在使用表鎖定來儘可能地減少死鎖的同時，也增加了對錶鎖定的爭用。因此，在實現這種方法之前，你需要權衡一下：避免死鎖是否比允許併發地對錶進行訪問更重要。

手工加鎖

SQL Server系統中建議讓系統自動管理鎖，該系統會分析使用者的SQL語句要求，自動為該請求加上合適的鎖，而且在鎖的數目太多時，系統會自動進行鎖升級。如前所述，升級的門限由系統自動配置，並不需要使用者配置。

在實際應用中，有時為了應用程式正確執行和保持資料的一致性，必須人為地給資料庫的某個表加鎖。比如，在某應用程式的一個事務操作中，需要根據一編號對幾個資料表做統計操作，為保證統計資料時間的一致性和正確性，從統計第一個表開始到全部表結束，其他應用程式或事務不能再對這幾個表寫入資料，這個時候，該應用程式希望在從統計第一個資料表開始或在整個事務開始時能夠由程式人為地（顯式地）鎖定這幾個表，這就需要用到手工加鎖（也稱顯式加鎖）技術。

在SQL Server 的SQL語句（SELECT、INSERT、DELETE、UPDATE）支援顯式加鎖。這4個語句在顯式加鎖的語法上類似，下面僅以SELECT語句為例給出語法：

SELECT FROM [ WITH ]

其中，指需要在該語句執行時新增在該表上的鎖型別。所指定的鎖型別有如下幾種：

1．HOLDLOCK: 在該表上保持共享鎖，直到整個事務結束，而不是在語句執行完立即釋放所新增的鎖。

2．NOLOCK：不新增共享鎖和排它鎖，當這個選項生效後，可能讀到未提交讀的資料或“髒資料”，這個選項僅僅應用於SELECT語句。

3． PAGLOCK：指定新增頁面鎖（否則通常可能新增表鎖）。

4．READCOMMITTED：設定事務為讀提交隔離性級別。

5．READPAST: 跳過已經加鎖的資料行，這個選項將使事務讀取資料時跳過那些已經被其他事務鎖定的資料行，而不是阻塞直到其他事務釋放鎖，READPAST僅僅應用於READ COMMITTED隔離性級別下事務操作中的SELECT語句操作。

6．READUNCOMMITTED：等同於NOLOCK。

7．REPEATABLEREAD：設定事務為可重複讀隔離性級別。

8．ROWLOCK：指定使用行級鎖。

9．SERIALIZABLE：設定事務為可序列的隔離性級別。

10．TABLOCK：指定使用表級鎖，而不是使用行級或頁面級的鎖，SQL Server在該語句執行完後釋放這個鎖，而如果同時指定了HOLDLOCK，該鎖一直保持到這個事務結束。

11．TABLOCKX：指定在表上使用排它鎖，這個鎖可以阻止其他事務讀或更新這個表的資料，直到這個語句或整個事務結束。

12． UPDLOCK ：指定在讀表中資料時設定修改鎖（update lock）而不是設定共享鎖，該鎖一直保持到這個語句或整個事務結束，使用UPDLOCK的作用是允許使用者先讀取資料（而且不阻塞其他使用者讀資料），並且保證在後來再更新資料時，這一段時間內這些資料沒有被其他使用者修改。

由上可見，在SQL Server中可以靈活多樣地為SQL語句顯式加鎖，若適當使用，我們完全可以完成一些程式的特殊要求，保證資料的一致性和完整性。對於一般使用者而言，瞭解鎖機制並不意味著必須使用它。事實上，SQL Server建議讓系統自動管理資料庫中的鎖，而且一些關於鎖的設定選項也沒有提供給使用者和資料庫管理人員，對於特殊使用者，通過給資料庫中的資源顯式加鎖，可以滿足很高的資料一致性和可靠性要求，只是需要特別注意避免死鎖現象的出現。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242062/，如需轉載，請註明出處，否則將追究法律責任。

相關文章