MS SQL Server數據庫事務(wù)鎖機制分析
■ 北京師范大學(xué) 劉永明
鎖是網(wǎng)絡(luò )數據庫中的一個(gè)非常重要的概念，它主要用于多用戶(hù)環(huán)境下保證數據庫完整性和一致性。各種大型數據庫所采用的鎖的基本理論是一致的，但在具體實(shí)現上各有差別。目前，大多數數據庫管理系統都或多或少具有自我調節、自我管理的功能，因此很多用戶(hù)實(shí)際上不清楚鎖的理論和所用數據庫中鎖的具體實(shí)現。 Microsoft SQL Server（以下簡(jiǎn)稱(chēng)SQL Server）作為一種中小型數據庫管理系統，已經(jīng)得到了廣泛的應用，該系統更強調由系統來(lái)管理鎖。在用戶(hù)有SQL請求時(shí)，系統分析請求，自動(dòng)在滿(mǎn)足鎖定條件和系統性能之間為數據庫加上適當的鎖，同時(shí)系統在運行期間常常自動(dòng)進(jìn)行優(yōu)化處理，實(shí)行動(dòng)態(tài)加鎖。對于一般的用戶(hù)而言，通過(guò)系統的自動(dòng)鎖定管理機制基本可以滿(mǎn)足使用要求，但如果對數據安全、數據庫完整性和一致性有特殊要求，就必須自己控制數據庫的鎖定和解鎖，這就需要了解SQL Server的鎖機制，掌握數據庫鎖定方法。 鎖的多粒度性以及鎖升級 數據庫中的鎖是指一種軟件機制，用來(lái)指示某個(gè)用戶(hù)（也即進(jìn)程會(huì )話(huà)，下同）已經(jīng)占用了某種資源，從而防止其他用戶(hù)做出影響本用戶(hù)的數據修改或導致數據庫數據的非完整性和非一致性。這兒所謂資源，主要指用戶(hù)可以操作的數據行、索引以及數據表等。根據資源的不同，鎖有多粒度（multigranular）的概念，也就是指可以鎖定的資源的層次。SQL Server中能夠鎖定的資源粒度包括：數據庫、表、區域、頁(yè)面、鍵值（指帶有索引的行數據）、行標識符（RID，即表中的單行數據）。 采用多粒度鎖的重要用途是用來(lái)支持并發(fā)操作和保證數據的完整性。SQL Server根據用戶(hù)的請求，做出分析后自動(dòng)給數據庫加上合適的鎖。假設某用戶(hù)只操作一個(gè)表中的部分行數據，系統可能會(huì )只添加幾個(gè)行鎖（RID）或頁(yè)面鎖，這樣可以盡可能多地支持多用戶(hù)的并發(fā)操作。但是，如果用戶(hù)事務(wù)中頻繁對某個(gè)表中的多條記錄操作，將導致對該表的許多記錄行都加上了行級鎖，數據庫系統中鎖的數目會(huì )急劇增加，這樣就加重了系統負荷，影響系統性能。因此，在數據庫系統中，一般都支持鎖升級(lock escalation)。所謂鎖升級是指調整鎖的粒度，將多個(gè)低粒度的鎖替換成少數的更高粒度的鎖，以此來(lái)降低系統負荷。在SQL Server中當一個(gè)事務(wù)中的鎖較多，達到鎖升級門(mén)限時(shí)，系統自動(dòng)將行級鎖和頁(yè)面鎖升級為表級鎖。特別值得注意的是，在SQL Server中，鎖的升級門(mén)限以及鎖升級是由系統自動(dòng)來(lái)確定的，不需要用戶(hù)設置。 鎖的模式和兼容性 在數據庫中加鎖時(shí)，除了可以對不同的資源加鎖，還可以使用不同程度的加鎖方式，即鎖有多種模式，SQL Server中鎖模式包括： 1．共享鎖 SQL Server中，共享鎖用于所有的只讀數據操作。共享鎖是非獨占的，允許多個(gè)并發(fā)事務(wù)讀取其鎖定的資源。默認情況下，數據被讀取后，SQL Server立即釋放共享鎖。例如，執行查詢(xún)“SELECT * FROM my_table”時(shí)，首先鎖定第一頁(yè)，讀取之后，釋放對第一頁(yè)的鎖定，然后鎖定第二頁(yè)。這樣，就允許在讀操作過(guò)程中，修改未被鎖定的第一頁(yè)。但是，事務(wù)隔離級別連接選項設置和SELECT語(yǔ)句中的鎖定設置都可以改變SQL Server的這種默認設置。例如，“ SELECT * FROM my_table HOLDLOCK”就要求在整個(gè)查詢(xún)過(guò)程中，保持對表的鎖定，直到查詢(xún)完成才釋放鎖定。 2．修改鎖 修改鎖在修改操作的初始化階段用來(lái)鎖定可能要被修改的資源，這樣可以避免使用共享鎖造成的死鎖現象。因為使用共享鎖時(shí)，修改數據的操作分為兩步，首先獲得一個(gè)共享鎖，讀取數據，然后將共享鎖升級為獨占鎖，然后再執行修改操作。這樣如果同時(shí)有兩個(gè)或多個(gè)事務(wù)同時(shí)對一個(gè)事務(wù)申請了共享鎖，在修改數據的時(shí)候，這些事務(wù)都要將共享鎖升級為獨占鎖。這時(shí)，這些事務(wù)都不會(huì )釋放共享鎖而是一直等待對方釋放，這樣就造成了死鎖。如果一個(gè)數據在修改前直接申請修改鎖，在數據修改的時(shí)候再升級為獨占鎖，就可以避免死鎖。修改鎖與共享鎖是兼容的，也就是說(shuō)一個(gè)資源用共享鎖鎖定后，允許再用修改鎖鎖定。 3．獨占鎖 獨占鎖是為修改數據而保留的。它所鎖定的資源，其他事務(wù)不能讀取也不能修改。獨占鎖不能和其他鎖兼容。 4．結構鎖 結構鎖分為結構修改鎖（Sch-M）和結構穩定鎖（Sch-S）。執行表定義語(yǔ)言操作時(shí)，SQL Server采用Sch-M鎖，編譯查詢(xún)時(shí)，SQL Server采用Sch-S鎖。 5．意向鎖 意向鎖說(shuō)明SQL Server有在資源的低層獲得共享鎖或獨占鎖的意向。例如，表級的共享意向鎖說(shuō)明事務(wù)意圖將獨占鎖釋放到表中的頁(yè)或者行。意向鎖又可以分為共享意向鎖、獨占意向鎖和共享式獨占意向鎖。共享意向鎖說(shuō)明事務(wù)意圖在共享意向鎖所鎖定的低層資源上放置共享鎖來(lái)讀取數據。獨占意向鎖說(shuō)明事務(wù)意圖在共享意向鎖所鎖定的低層資源上放置獨占鎖來(lái)修改數據。共享式獨占鎖說(shuō)明事務(wù)允許其他事務(wù)使用共享鎖來(lái)讀取頂層資源，并意圖在該資源低層上放置獨占鎖。 6．批量修改鎖 批量復制數據時(shí)使用批量修改鎖?？梢酝ㄟ^(guò)表的TabLock提示或者使用系統存儲過(guò)程sp_tableoption的“table lock on bulk load”選項設定批量修改鎖。 另外，SQL Server命令語(yǔ)句操作會(huì )影響鎖定的方式，語(yǔ)句的組合也同樣能產(chǎn)生不同的鎖定，詳情如下表：
鎖沖突及其防止辦法 在數據庫系統中，死鎖是指多個(gè)用戶(hù)（進(jìn)程）分別鎖定了一個(gè)資源，并又試圖請求鎖定對方已經(jīng)鎖定的資源，這就產(chǎn)生了一個(gè)鎖定請求環(huán)，導致多個(gè)用戶(hù)（進(jìn)程）都處于等待對方釋放所鎖定資源的狀態(tài)。 在SQL Server中，系統能夠自動(dòng)定期搜索和處理死鎖問(wèn)題。系統在每次搜索中標識所有等待鎖定請求的進(jìn)程會(huì )話(huà)，如果在下一次搜索中該被標識的進(jìn)程仍處于等待狀態(tài)，SQL Server就開(kāi)始遞歸死鎖搜索。 (上接第D21版) 當搜索檢測到鎖定請求環(huán)時(shí)，系統將根據各進(jìn)程會(huì )話(huà)的死鎖優(yōu)先級別來(lái)結束一個(gè)優(yōu)先級最低的事務(wù)，此后，系統回滾該事務(wù)，并向該進(jìn)程發(fā)出1205號錯誤信息。這樣，其他事務(wù)就有可能繼續運行了。死鎖優(yōu)先級的設置語(yǔ)句為： SET DEADLOCK_PRIORITY { LOW | NORMAL} 其中LOW說(shuō)明該進(jìn)程會(huì )話(huà)的優(yōu)先級較低，在出現死鎖時(shí)，可以首先中斷該進(jìn)程的事務(wù)。另外，各進(jìn)程中通過(guò)設置LOCK_TIMEOUT選項能夠設置進(jìn)程處于鎖定請求狀態(tài)的最長(cháng)等待時(shí)間。該設置的語(yǔ)句： SET LOCK_TIMEOUT { timeout_period } 其中，timeout_period以毫秒為單位。 理解了死鎖的概念，在應用程序中就可以采用下面的一些方法來(lái)盡量避免死鎖了： （1）合理安排表訪(fǎng)問(wèn)順序。 （2）在事務(wù)中盡量避免用戶(hù)干預，盡量使一個(gè)事務(wù)處理的任務(wù)少些。 （3）采用臟讀技術(shù)。臟讀由于不對被訪(fǎng)問(wèn)的表加鎖，而避免了鎖沖突。在客戶(hù)機/服務(wù)器應用環(huán)境中，有些事務(wù)往往不允許讀臟數據，但在特定的條件下，我們可以用臟讀。 （4）數據訪(fǎng)問(wèn)時(shí)域離散法。數據訪(fǎng)問(wèn)時(shí)域離散法是指在客戶(hù)機/服務(wù)器結構中，采取各種控制手段控制對數據庫或數據庫中的對象訪(fǎng)問(wèn)時(shí)間段。主要通過(guò)以下方式實(shí)現: 合理安排后臺事務(wù)的執行時(shí)間，采用工作流對后臺事務(wù)進(jìn)行統一管理。工作流在管理任務(wù)時(shí)，一方面限制同一類(lèi)任務(wù)的線(xiàn)程數（往往限制為1個(gè)），防止資源過(guò)多占用; 另一方面合理安排不同任務(wù)執行時(shí)序、時(shí)間，盡量避免多個(gè)后臺任務(wù)同時(shí)執行，另外， 避免在前臺交易高峰時(shí)間運行后臺任務(wù)。 （5）數據存儲空間離散法。數據存儲空間離散法是指采取各種手段，將邏輯上在一個(gè)表中的數據分散到若干離散的空間上去，以便改善對表的訪(fǎng)問(wèn)性能。主要通過(guò)以下方法實(shí)現: 第一，將大表按行或列分解為若干小表; 第二，按不同的用戶(hù)群分解。 （6）使用盡可能低的隔離性級別。隔離性級別是指為保證數據庫數據的完整性和一致性而使多用戶(hù)事務(wù)隔離的程度，SQL92定義了4種隔離性級別：未提交讀、提交讀、可重復讀和可串行。如果選擇過(guò)高的隔離性級別，如可串行，雖然系統可以因實(shí)現更好隔離性而更大程度上保證數據的完整性和一致性，但各事務(wù)間沖突而死鎖的機會(huì )大大增加，大大影響了系統性能。 （7）使用Bound Connections。Bound connections 允許兩個(gè)或多個(gè)事務(wù)連接共享事務(wù)和鎖，而且任何一個(gè)事務(wù)連接要申請鎖如同另外一個(gè)事務(wù)要申請鎖一樣，因此可以允許這些事務(wù)共享數據而不會(huì )有加鎖的沖突。 （8）考慮使用樂(lè )觀(guān)鎖定或使事務(wù)首先獲得一個(gè)獨占鎖定。一個(gè)最常見(jiàn)的死鎖情況發(fā)生在系列號生成器中，它們通常是這樣編寫(xiě)的： begin tran select new_id from keytab holdlock update keytab set new_id＝new_id+l commit tran 如果有兩個(gè)用戶(hù)在同時(shí)運行這一事務(wù)，他們都會(huì )得到共享鎖定并保持它。當兩個(gè)用戶(hù)都試圖得到keytab表的獨占鎖定時(shí)，就會(huì )進(jìn)入死鎖。為了避免這種情況的發(fā)生，應將上述事務(wù)重寫(xiě)成如下形式： begin tran update keytab set new_id=new_id+l select new_id from keytab commit tran 以這種方式改寫(xiě)后，只有一個(gè)事務(wù)能得到keytab的獨占鎖定，其他進(jìn)程必須等到第一個(gè)事務(wù)的完成，這樣雖增加了執行時(shí)間，但避免了死鎖。 如果要求在一個(gè)事務(wù)中具有讀取的可重復能力，就要考慮以這種方式來(lái)編寫(xiě)事務(wù)，以獲得資源的獨占鎖定，然后再去讀數據。例如，如果一個(gè)事務(wù)需要檢索出titles表中所有書(shū)的平均價(jià)格，并保證在update被應用前，結果不會(huì )改變，優(yōu)化器就會(huì )分配一個(gè)獨占的表鎖定?？紤]如下的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 在這個(gè)事務(wù)中，重要的是沒(méi)有其他進(jìn)程修改表中任何行的price，或者說(shuō)在事務(wù)結束時(shí)檢索的值與事務(wù)開(kāi)始時(shí)檢索的值不同。這里的where子句看起來(lái)很奇怪，但是不管你相信與否，這是迄今為止優(yōu)化器所遇到的最完美有效的where子句，盡管計算出的結果總是false。當優(yōu)化器處理此查詢(xún)時(shí)，因為它找不到任何有效的SARG，它的查詢(xún)規劃就會(huì )強制使用一個(gè)獨占鎖定來(lái)進(jìn)行表掃描。此事務(wù)執行時(shí)，where子句立即得到一個(gè)false值，于是不會(huì )執行實(shí)際上的掃描，但此進(jìn)程仍得到了一個(gè)獨占的表鎖定。 因為此進(jìn)程現在已有一個(gè)獨占的表鎖，所以可以保證沒(méi)有其他事務(wù)會(huì )修改任何數據行，能進(jìn)行重復讀，且避免了由于holdlock所引起的潛在性死鎖。但是，要避免死鎖，不可能不付出代價(jià)。在使用表鎖定來(lái)盡可能地減少死鎖的同時(shí)，也增加了對表鎖定的爭用。因此，在實(shí)現這種方法之前，你需要權衡一下：避免死鎖是否比允許并發(fā)地對表進(jìn)行訪(fǎng)問(wèn)更重要。 手工加鎖 SQL Server系統中建議讓系統自動(dòng)管理鎖，該系統會(huì )分析用戶(hù)的SQL語(yǔ)句要求，自動(dòng)為該請求加上合適的鎖，而且在鎖的數目太多時(shí)，系統會(huì )自動(dòng)進(jìn)行鎖升級。如前所述，升級的門(mén)限由系統自動(dòng)配置，并不需要用戶(hù)配置。 在實(shí)際應用中，有時(shí)為了應用程序正確運行和保持數據的一致性，必須人為地給數據庫的某個(gè)表加鎖。比如，在某應用程序的一個(gè)事務(wù)操作中，需要根據一編號對幾個(gè)數據表做統計操作，為保證統計數據時(shí)間的一致性和正確性，從統計第一個(gè)表開(kāi)始到全部表結束，其他應用程序或事務(wù)不能再對這幾個(gè)表寫(xiě)入數據，這個(gè)時(shí)候，該應用程序希望在從統計第一個(gè)數據表開(kāi)始或在整個(gè)事務(wù)開(kāi)始時(shí)能夠由程序人為地（顯式地）鎖定這幾個(gè)表，這就需要用到手工加鎖（也稱(chēng)顯式加鎖）技術(shù)。 在SQL Server 的SQL語(yǔ)句（SELECT、INSERT、DELETE、UPDATE）支持顯式加鎖。這4個(gè)語(yǔ)句在顯式加鎖的語(yǔ)法上類(lèi)似，下面僅以SELECT語(yǔ)句為例給出語(yǔ)法： SELECT FROM [ WITH ] 其中， 指需要在該語(yǔ)句執行時(shí)添加在該表上的鎖類(lèi)型。 所指定的鎖類(lèi)型有如下幾種： 1．HOLDLOCK: 在該表上保持共享鎖，直到整個(gè)事務(wù)結束，而不是在語(yǔ)句執行完立即釋放所添加的鎖。 2．NOLOCK：不添加共享鎖和排它鎖，當這個(gè)選項生效后，可能讀到未提交讀的數據或“臟數據”，這個(gè)選項僅僅應用于SELECT語(yǔ)句。 3． PAGLOCK：指定添加頁(yè)面鎖（否則通?？赡芴砑颖礞i）。 4．READCOMMITTED：設置事務(wù)為讀提交隔離性級別。 5．READPAST: 跳過(guò)已經(jīng)加鎖的數據行，這個(gè)選項將使事務(wù)讀取數據時(shí)跳過(guò)那些已經(jīng)被其他事務(wù)鎖定的數據行，而不是阻塞直到其他事務(wù)釋放鎖，READPAST僅僅應用于READ COMMITTED隔離性級別下事務(wù)操作中的SELECT語(yǔ)句操作。 6．READUNCOMMITTED：等同于NOLOCK。 7．REPEATABLEREAD：設置事務(wù)為可重復讀隔離性級別。 8．ROWLOCK：指定使用行級鎖。 9．SERIALIZABLE：設置事務(wù)為可串行的隔離性級別。 10．TABLOCK：指定使用表級鎖，而不是使用行級或頁(yè)面級的鎖，SQL Server在該語(yǔ)句執行完后釋放這個(gè)鎖，而如果同時(shí)指定了HOLDLOCK，該鎖一直保持到這個(gè)事務(wù)結束。 11．TABLOCKX：指定在表上使用排它鎖，這個(gè)鎖可以阻止其他事務(wù)讀或更新這個(gè)表的數據，直到這個(gè)語(yǔ)句或整個(gè)事務(wù)結束。 12． UPDLOCK ：指定在讀表中數據時(shí)設置修改鎖（update lock）而不是設置共享鎖，該鎖一直保持到這個(gè)語(yǔ)句或整個(gè)事務(wù)結束，使用UPDLOCK的作用是允許用戶(hù)先讀取數據（而且不阻塞其他用戶(hù)讀數據），并且保證在后來(lái)再更新數據時(shí)，這一段時(shí)間內這些數據沒(méi)有被其他用戶(hù)修改。 由上可見(jiàn)，在SQL Server中可以靈活多樣地為SQL語(yǔ)句顯式加鎖，若適當使用，我們完全可以完成一些程序的特殊要求，保證數據的一致性和完整性。對于一般使用者而言，了解鎖機制并不意味著(zhù)必須使用它。事實(shí)上，SQL Server建議讓系統自動(dòng)管理數據庫中的鎖，而且一些關(guān)于鎖的設置選項也沒(méi)有提供給用戶(hù)和數據庫管理人員，對于特殊用戶(hù)，通過(guò)給數據庫中的資源顯式加鎖，可以滿(mǎn)足很高的數據一致性和可靠性要求，只是需要特別注意避免死鎖現象的出現。 （計算機世界報 第48期 D21、D22）
本站僅提供存儲服務(wù)，所有內容均由用戶(hù)發(fā)布，如發(fā)現有害或侵權內容，請
點(diǎn)擊舉報。
