掌握SQLServer鎖的相關概念

一.為什麼要引入鎖

 

　　當多個使用者同時對資料庫的並行作業時會帶來以下資料不一致的問題:

　　◆丟失更新

　　A,B兩個使用者讀同一資料並進行修改,其中一個使用者的修改結果破壞了另一個修改的結果,比如訂票系統

　　◆髒讀

　　A使用者修改了資料,隨後B使用者又讀出該資料,但A使用者因為某些原因取消了對資料的修改,資料恢複原值,此時B得到的資料就與資料庫內的資料產生了不一致

　　◆不可重複讀取

　　A使用者讀取資料,隨後B使用者讀出該資料並修改,此時A使用者再讀取資料時發現前後兩次的值不一致

　　並發控制的主要方法是封鎖,鎖就是在一段時間內禁止使用者做某些操作以避免產生資料不一致

　　二鎖的分類

　　◆鎖的類別有兩種分法：

　　1.從資料庫系統的角度來看：分為獨佔鎖（即排它鎖），共用鎖定和更新鎖定

　　MS-SQLServer使用以下資源鎖模式。

　　鎖模式描述

　　共用(S)用於不更改或不更新資料的操作（唯讀操作），如SELECT語句。

　　更新(U)用於可更新的資源中。防止當多個會話在讀取、鎖定以及隨後可能進行的資源更新時發生常見形式的死結。

　　排它(X)用於資料修改操作，例如INSERT、UPDATE或DELETE。確保不會同時同一資源進行多重更新。

　　意圖鎖定用於建立鎖的階層。意圖鎖定的類型為：意圖共用(IS)、意向排它(IX)以及與意向排它共用(SIX)。

　　架構鎖在執行依賴於表架構的操作時使用。架構鎖的類型為：架構修改(Sch-M)和架構穩定性(Sch-S)。

　　大容量更新(BU)向表中大量複製資料並指定了TABLOCK提示時使用。

　　◆共用鎖定

　　共用(S)鎖允許並發事務讀取(SELECT)一個資源。資源上存在共用(S)鎖時，任何其它事務都不能修改資料。一旦已經讀取資料，便立即釋放資源上的共用(S)鎖，除非將交易隔離等級設定為可重複讀或更進階別，或者在事務生存周期內用鎖定提示保留共用(S)鎖。

　　◆更新鎖定

　　更新(U)鎖可以防止通常形式的死結。一般更新模式由一個事務組成，此事務讀取記錄，擷取資源（頁或行）的共用(S)鎖，然後修改行，此操作要求鎖轉換為排它(X)鎖。如果兩個事務獲得了資源上的共用模式鎖，然後試圖同時更新資料，則一個事務嘗試將鎖轉換為排它(X)鎖。共用模式到排它鎖的轉換必須等待一段時間，因為一個事務的排它鎖與其它事務的共用模式鎖不相容；發生鎖等待。第二個事務試圖擷取排它(X)鎖以進行更新。由於兩個事務都要轉換為排它(X)鎖，並且每個事務都等待另一個事務釋放共用模式鎖，因此發生死結。

　　若要避免這種潛在的死結問題，請使用更新(U)鎖。一次只有一個事務可以獲得資源的更新(U)鎖。如果事務修改資源，則更新(U)鎖轉換為排它(X)鎖。否則，鎖轉換為共用鎖定。

　　◆排它鎖

　　排它(X)鎖可以防止並發事務對資源進行訪問。其它事務不能讀取或修改排它(X)鎖鎖定的資料。

　　意圖鎖定

　　意圖鎖定表示SQLServer需要在階層中的某些底層資源上擷取共用(S)鎖或排它(X)鎖。例如，放置在表級的共用意圖鎖定表示事務打算在表中的頁或行上放置共用(S)鎖。在表級設定意圖鎖定可防止另一個事務隨後在包含那一頁的表上擷取排它(X)鎖。意圖鎖定可以提高效能，因為SQLServer僅在表級檢查意圖鎖定來確定事務是否可以安全地擷取該表上的鎖。而無須檢查表中的每行或每頁上的鎖以確定事務是否可以鎖定整個表。

　　意圖鎖定包括意圖共用(IS)、意向排它(IX)以及與意向排它共用(SIX)。

　　鎖模式描述

　　意圖共用(IS)通過在各資源上放置S鎖，表明事務的意向是讀取階層中的部分（而不是全部）底層資源。

　　意向排它(IX)通過在各資源上放置X鎖，表明事務的意向是修改階層中的部分（而不是全部）底層資源。IX是IS的超集。

　　與意向排它共用(SIX)通過在各資源上放置IX鎖，表明事務的意向是讀取階層中的全部底層資源並修改部分（而不是全部）底層資源。允許頂層資源上的並發IS鎖。例如，表的SIX鎖在表上放置一個SIX鎖（允許並發IS鎖），在當前所修改頁上放置IX鎖（在已修改行上放置X鎖）。雖然每個資源在一段時間內只能有一個SIX鎖，以防止其它事務對資源進行更新，但是其它事務可以通過擷取表級的IS鎖來讀取階層中的底層資源。

　　◆獨佔鎖：

　　只允許進行鎖定操作的程式使用，其他任何對他的操作均不會被接受。執行資料更新命令時，SQLServer會自動使用獨佔鎖。當對象上有其他鎖存在時，無法對其加獨佔鎖。

　　共用鎖定：共用鎖定鎖定資源可以被其他使用者讀取，但其他使用者無法修改它，在執行Select時，SQLServer會對對象加共用鎖定。

　　◆更新鎖定：

　　當SQLServer準備更新資料時，它首先對資料對象作更新鎖定鎖定，這樣資料將不能被修改，但可以讀取。等到SQLServer確定要進行更新資料操作時，他會自動將更新鎖定換為獨佔鎖，當對象上有其他鎖存在時，無法對其加更新鎖定。

　　2.從程式員的角度看：分為樂觀鎖和悲觀鎖。

　　樂觀鎖：完全依靠資料庫來管理鎖的工作。

　　悲觀鎖：程式員自己管理資料或對象上的鎖處理。

　　MS-SQLSERVER使用鎖在多個同時在資料庫內執行修改的使用者間實現封閉式並行存取控制

　　三鎖的粒度

　　鎖粒度是被封鎖目標的大小,封鎖粒度小則並發性高,但開銷大,封鎖粒度大則並發性低但開銷小

　　SQLServer支援的鎖粒度可以分為為行、頁、鍵、鍵範圍、索引、表或資料庫擷取鎖

　　資源描述

　　RID行標識符。用於單獨鎖定表中的一行。

　　鍵索引中的行鎖。用於保護可串列事務中的鍵範圍。

　　頁8KB(KB)的資料頁或索引頁。

　　擴充盤區相鄰的八個資料頁或索引頁構成的一組。

　　表包括所有資料和索引在內的整個表。

　　DB資料庫。

　　四鎖定時間的長短

　　鎖保持的時間長度為保護所請求層級上的資源所需的時間長度。

　　用於保護讀取操作的共用鎖定的保持時間取決於交易隔離等級。採用READCOMMITTED的預設交易隔離等級時，只在讀取頁的期間內控制共用鎖定。在掃描中，直到在掃描內的下一頁上擷取鎖時才釋放鎖。如果指定HOLDLOCK提示或者將交易隔離等級設定為REPEATABLEREAD或SERIALIZABLE，則直到事務結束才釋放鎖。

　　根據為遊標設定的並發選項，遊標可以擷取共用模式的滾動鎖以保護提取。當需要滾動鎖時，直到下一次提取或關閉遊標（以先發生者為準）時才釋放滾動鎖。但是，如果指定HOLDLOCK，則直到事務結束才釋放滾動鎖。

　　用於保護更新的排它鎖將直到事務結束才釋放。

　　如果一個串連試圖擷取一個鎖，而該鎖與另一個串連所控制的鎖衝突，則試圖擷取鎖的串連將一直阻塞到：

　　將衝突鎖釋放而且串連擷取了所請求的鎖。

　　串連的逾時間隔已到期。預設情況下沒有逾時間隔，但是一些應用程式設定逾時間隔以防止無限期等待

　　五SQLServer中鎖的自訂

　　◆處理死結和設定死結優先順序

　　死結就是多個使用者申請不同封鎖,由於申請者均擁有一部分封鎖權而又等待其他使用者擁有的部分封鎖而引起的無休止的等待

　　可以使用SETDEADLOCK_PRIORITY控制在發生死結情況時會話的反應方式。如果兩個進程都鎖定資料，並且直到其它進程釋放自己的鎖時，每個進程才能釋放自己的鎖，即發生死結情況。

　　◆2處理逾時和設定鎖逾時期間。

　　@@LOCK_TIMEOUT返回當前會話的當前鎖逾時設定，單位為毫秒

　　SETLOCK_TIMEOUT設定允許應用程式設定語句等待阻塞資源的最長時間。當語句等待的時間大於LOCK_TIMEOUT設定時，系統將自動取消阻塞的語句，並給應用程式返回"已超過了鎖請求逾時時段"的1222號錯誤資訊

　　樣本

　　下例將鎖逾時期限設定為1,800毫秒。

　　SETLOCK_TIMEOUT1800

　　◆設定交易隔離等級。

　　◆對SELECT、INSERT、UPDATE和DELETE語句使用表級鎖定提示。

　　◆配置索引的鎖定粒度

　　可以使用sp_indexoption系統預存程序來設定用於索引的鎖定粒度

　　六查看鎖的資訊

　　1執行EXECSP_LOCK報告有關鎖的資訊

　　2查詢分析器中按Ctrl+2可以看到鎖的資訊

　　七使用注意事項

　　如何避免死結

　　1使用事務時，盡量縮短事務的邏輯處理過程，及早提交或復原事務；

　　2設定死結逾時參數為合理範圍，如：3分鐘-10分種；超過時間，自動放棄本次操作，避免進程懸掛；

　　3最佳化程式，檢查並避免死結現象出現；

　　4.對所有的指令碼和SP都要仔細測試，在正是版本之前。

　　5所有的SP都要有錯誤處理（通過@error）

　　6一般不要修改SQLSERVER事務的預設層級。不推薦強行加鎖

　　解決問題如何對行表資料庫加鎖

　　八幾個有關鎖的問題

　　1如何鎖一個表的某一行

　　SETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED

　　SELECT*FROMtableROWLOCKWHEREid=1

　　2鎖定資料庫的一個表

　　SELECT*FROMtableWITH(HOLDLOCK)

　　加鎖語句：

　　sybase:

　　update表setcol1=col1where1=0;

　　MSSQL:

　　selectcol1from表(tablockx)where1=0;

　　oracle:

　　LOCKTABLE表INEXCLUSIVEMODE；

　　加鎖後其它人不可操作，直到加鎖使用者解鎖，用commit或rollback解鎖

　　幾個例子協助大家加深印象

　　設table1(A,B,C)

　　ABC

　　a1b1c1

　　a2b2c2

　　a3b3c3

　　◆排它鎖

　　建立兩個串連

　　在第一個串連中執行以下語句

　　begintran

　　updatetable1

　　setA=''aa''

　　whereB=''b2''

　　waitfordelay''00:00:30''--等待30秒

　　committran

　　在第二個串連中執行以下語句

　　begintran

　　select*fromtable1

　　whereB=''b2''

　　committran

　　若同時執行上述兩個語句，則select查詢必須等待update執行完畢才能執行即要等待30秒

　　◆共用鎖定

　　在第一個串連中執行以下語句

　　begintran

　　select*fromtable1holdlock-holdlock人為加鎖

　　whereB=''b2''

　　waitfordelay''00:00:30''--等待30秒

　　committran

　　◆共用鎖定

　　在第一個串連中執行以下語句

　　begintran

　　select*fromtable1holdlock-holdlock人為加鎖

　　whereB=''b2''

　　waitfordelay''00:00:30''--等待30秒

　　committran

　　在第二個串連中執行以下語句

　　begintran

　　selectA,Cfromtable1

　　whereB=''b2''

　　updatetable1

　　setA=''aa''

　　whereB=''b2''

　　committran

　　若同時執行上述兩個語句，則第二個串連中的select查詢可以執行

　　而update必須等待第一個事務釋放共用鎖定轉為排它鎖後才能執行即要等待30秒

　　◆死結

　　增設table2(D,E)

　　DE

　　d1e1

　　d2e2

　　在第一個串連中執行以下語句

　　begintran

　　updatetable1

　　setA=''aa''

　　whereB=''b2''

　　waitfordelay''00:00:30''

　　updatetable2

　　setD=''d5''

　　whereE=''e1''

　　committran

　　在第二個串連中執行以下語句

　　begintran

　　updatetable2

　　setD=''d5''

　　whereE=''e1''

　　waitfordelay''00:00:10''

　　updatetable1

　　setA=''aa''

　　whereB=''b2''

　　committran

　　同時執行，系統會檢測出死結，並中止進程

　　補充一點:

　　SQLServer2000支援的表級鎖定提示

　　HOLDLOCK持有共用鎖定，直到整個事務完成，應該在被鎖對象不需要時立即釋放，等於SERIALIZABLE交易隔離等級

　　NOLOCK語句執行時不發出共用鎖定，允許髒讀，等於READUNCOMMITTED交易隔離等級

　　PAGLOCK在使用一個表鎖的地方用多個頁鎖

　　READPAST讓SQLServer跳過任何鎖定行，執行事務，適用於READUNCOMMITTED交易隔離等級只跳過RID鎖，不跳過頁，地區和表鎖

　　ROWLOCK強制使用行鎖

　　TABLOCKX強制使用獨佔表級鎖，這個鎖在事務期間阻止任何其他事務使用這個表

　　UPLOCK強制在讀表時使用更新而不用共用鎖定

　　應用程式鎖:

　　應用程式鎖就是產生用戶端程式碼的鎖，而不是sqlserver本身產生的鎖

　　處理應用程式鎖的兩個過程

　　sp_getapplock鎖定應用程式資源

　　sp_releaseapplock為應用程式資源解鎖

　　注意:鎖定資料庫的一個表的區別

　　SELECT*FROMtableWITH(HOLDLOCK)其他事務可以讀取表，但不能更新刪除

　　SELECT*FROMtableWITH(TABLOCKX)其他事務不能讀取表,更新和刪除