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

現象:
在查詢分析器裡建立三個查詢語句
begin tran
select * from table1 with(tablock,updlock)

分別執行後，

在第一個查詢分析器介面裡面屏蔽掉原先的查詢語句
然後寫入
commit tran

這時第二個查詢介面或者第三個查詢介面將查詢出鄉瑤的資料，但是其中另外一個將出現死結資訊。

以下鎖的機制：（源自www.aspxuexi.com ）

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

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_t
ableoption的“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<(select avg(price)from titles)

commit tran

在這個事務中，重要的是沒有其他進程修改表中任何行的price，或者說在事務結束時檢
索的值與事務開始時檢索的值不同。這裡的where子句看起來很奇怪，但是不管你相信與
否，這是迄今為止最佳化器所遇到的最完美有效where子句，儘管計算出的結果總是fals
e。當最佳化器處理此查詢時，因為它找不到任何有效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建議讓系統自動管理資料庫中的
鎖，而且一些關於鎖的設定選項也沒有提供給使用者和資料庫管理員，對於特殊使用者，
通過給資料庫中的資源顯式加鎖，可以滿足很高的資料一致性和可靠性要求，只是需要
特別注意避免死結現象的出現。