SQL Server 鎖

標籤：

鎖是一種防止在某對象執行動作的一個進程與已在該對象上執行的其他進行相衝突的機制。也就是說，如果有其他人在操作某個對象，那麼你舊不能在該對象上進行操作。你能否執行操作取決於其他使用者進行中的操作。

通過鎖可以防止的問題                                                                                         

　　鎖可以解決以下4種主要問題：

• 髒讀
• 非重複性讀取
• 幻讀
• 丟失更新

　　1、髒讀

　　如果一個事務讀取的記錄是另一個未完成事務的一部分，那麼這時就發生了髒讀。如果第一個事務正常完成，那麼就有什麼問題。但是，如果前一個交易回復了呢，那將從資料庫從未發生的事務中擷取了資訊。

　　2、非重複性讀取

　　很容易將非重複性讀取和髒讀混淆。如果一個事務中兩次讀取記錄，而另一個事務在這期間改變了資料，就會發生非重複性讀取。 例如，一個銀行賬戶的餘額是不允許小於0的。如果一個事務讀取了某賬戶的餘額為125元，此時另一事務也讀取了125元，如果兩個事務都計費100元，那麼這時資料庫的餘額就變成了-75元。

　　有兩種方式可以防止這個問題：

• 建立CHECK約束並監控547錯誤
• 將隔離等級設定為REPEATABLEREAD或SERIALIZABLE

　　CHECK約束看上去相當直觀。要知道的是，這是一種被動的而非主動的方法。然而，在很多情況下可能需要使用非重複性讀取，所以這在很多情況下是首選。

　　3、幻讀

　　幻讀發生的機率非常小，只有在非常偶然的情況下才會發生。

　　比如，你想將一張工資表裡所有低於100的人的工資，提高到100元。你可能會執行以下SQL語句：

　　UPDATE tb_Money SET Salary = 100　　WHERE Salary < 100

　　這樣的語句，通常情況下，沒有問題。但是如果，你在UPDATE的過程中，有人恰好有INSERT了一條工資低於100的資料，因為這是一個全新的資料航，所以沒有被鎖定，而且它會被漏過Update。

　　要解決這個問題，唯一的方法是設定交易隔離等級為SERIALIZABLE,在這種情況下，任何對錶的更新都不能放入WHERE子句中，否則他們將被鎖在外面。

　　4、丟失更新

　　丟失更新發生在一個更新成功寫入資料庫後，而又意外地被另一個事務重寫時。這是怎麼發生的呢？如果有兩個事務讀取整個記錄，然後其中一個向記錄寫入了更新資訊，而另一個事務也向該記錄寫入更新資訊，這是就會出現丟失更新。

　　有個例子寫得很好，這裡照敲下來吧。假如你是公司的一位信用分析員，你接到客戶X開啟的電話，說他已達到它的信用額度上限，想申請增加額度，所以你查看了這位客戶的資訊，你發現他的信用額度是5000，並且看他每次都能按時付款。

　　當你在查看的時候，信用部門的另一位員工也讀取了客戶X的記錄，並輸入資訊改變了客戶的地址。它讀取的記錄也顯示信用額度為5000。

　　這時你決定把客戶X的信用額度提高到10000，並且按下了Enter鍵，資料庫現在顯示客戶X的信用額度為10000。

　　Sally現在也更新了客戶X的地址，但是她仍然使用和您一樣的編輯螢幕，也就是說，她更新了整個記錄。還記得她螢幕上顯示的信用額度嗎？是5000.資料庫現在又一次顯示客戶X的信用額度為5000。你的更新丟失了。

　　解決這個問題的方法取決於你讀取某資料和要更新資料的這段時間內，代碼以何種方式識別出另一串連已經更新了該記錄。這個識別的方式取決於你所使用的存取方法。

可以鎖定資源                                                                                                

對於SQL Server來說，有6種可鎖定資源，而且它們形成了一個階層。鎖的層次越高，它的粒度就越粗。按粒度由粗到細排列，這些資源套件括：

• 資料庫：鎖定整個資料庫。這通常發生在整個資料庫模式改變的時候。
• 表：鎖定整個表。這包含了與該表相關聯的所有資料相關的對象，包括實際的資料行(每一行)以及與該表相關聯的所有索引中的鍵。
• 區段：鎖定整個區段。因為一個區段由8頁組成，所以區段鎖定是指鎖定控制了區段、控制了該區段內8個資料或索引頁以及這8頁中的所有資料航。
• 頁：鎖定該頁中的所有資料或索引鍵。
• 鍵：在索引中的特定鍵或一系間上有鎖。相同索引頁中的其他鍵不受影響。
• 行或行標識符：雖然從技術上將，鎖是放在行標識符上的，但是本質上，它鎖定了整個資料行。

鎖定擴大和鎖對效能的影響                                                                                     

　　升級是指能夠認識到維持一個較細的粒度(例如，行鎖而不是頁鎖)，只在被鎖定的項數較少時有意義。而隨著越來越多的項目被鎖定，維護這些鎖的系統開銷實際上會影響效能。這會導致所持續更長的時間。

　　當維持鎖的數量達到一定限度時，則鎖定擴大為下一個更高的層次，並且將不需要再如此緊密地管理低層次的鎖(釋放資源，而且有助於提升速度)。

　　注意，升級是基於鎖的數量，而不是使用者的數量。這裡的重點是，可以通過執行大量的更新來單獨地鎖定表-行鎖可以升級為頁鎖，頁鎖可以升級為表鎖。這意味著可能將所有其他使用者鎖在該表之外。如果查詢使用了多個表，則它很可能將每個人鎖在這些表之外。

鎖定模式                                                                                                          

　　除了需要考慮鎖定資源層次以外，還要考慮查詢將要獲得的鎖定模式，就像需要對不同的資源進行鎖定一樣，也有不同的鎖定模式。一些模式是互斥的。一些模式什麼都不做，只修改其他的模式。模式是否可以一起使用取決於他們是否是相容的。

　　1、共用鎖定

　　這是最基本的一種鎖。共用鎖定用於只需要讀取資料的時候，也就是說，共用鎖定鎖定時，不會進行改變內容的操作，其他使用者允許讀取。

　　共用鎖定能和其他共用鎖定相容。雖然共用鎖定不介意其他鎖的存在，但是有些鎖並不能和共用鎖定共存。

　　共用鎖定告訴其他鎖，某使用者已經在那邊了，它們並不提供很多的功能，但是不能忽略它們。然而，共用鎖定能做的是防止使用者執行髒讀。

　　2、排它鎖

　　排它鎖顧名思義，排它鎖不與其他任何鎖相容。如果有任何其他其他鎖存在，則不能使用獨佔鎖定，而且當獨佔鎖定仍然起作用時，他們不允許在資源之上建立任何形式的新鎖。這可以防止兩個人同時更新、刪除或執行任何操作。

　　3、更新鎖定

　　更新鎖定是共用鎖定和獨佔鎖定的混合。更新鎖定是一種特殊的預留位置。為了能執行UPDATE，需要驗證WHERE子句來指出想要更新的具體的資料行。這意味著只需要一個共用鎖定，直到真正地進行物理更新。在物理更新期間，需要一個獨佔鎖定。

• 第一個階段指出了滿足WHERE子句條件的內容，這是更新查詢的一部分，該查詢有一個更新鎖定。
• 第二個階段是如果決定執行更新，那麼鎖將升級為獨佔鎖定。否則，將把鎖轉換為共用鎖定。

　　這樣做的好處是它防止了死結。死結本身不是一種鎖定類型，而是一種已經形成矛盾的狀況，兩個鎖在互相等待，多個鎖形成一個環在等待前面的事務清除資源。

　　如果沒有更新鎖定，死結會一直出現。兩個更新查詢會在共用模式下運行。Query A完成了它的查詢工作並準備進行物理更新。它想升級為獨佔鎖定，但是不可以這麼做，因為Query B正在完成查詢。除非Query B需要進行物理更新，否則它會完成查詢。為了做到這點，Query B必須升級為獨佔鎖定，但是不能這麼做，因為Query A正在等待。這樣就造成了僵局。

　　而更新鎖定阻止建立其他的更新鎖定。第二個事務只要嘗試取得一個更新鎖定，它們就會進入等待狀態，直到逾時為止-將不會授予這個鎖。如果第一個鎖在鎖逾時之前清除的話，則鎖定會授予給新的要求者，並且這個處理會繼續下去。如果不清楚，則會發生錯誤。

　　更新鎖定只與共用鎖定以及意圖共用鎖相相容。

　　4、意圖鎖定

　　意圖鎖定是什麼意思呢？就是說，加入你鎖定了某一行，那麼同時也加了表的意圖鎖定(不允許其他人通過表鎖來妨礙你)。

　　意圖鎖定是真正的預留位置，它用來處理對象層次問題的。假設一下如下情況：已對某一行建立了鎖，但是有人想在頁上或區段上建立所，或者是修改表。你肯定不想讓另一個事務通過達到更高的層次來妨礙你。 　　如果沒有意圖鎖定，那麼較高層次的對象將不會知道在較低層次上有鎖。意圖鎖定可改進效能，因為SQL Server只需要在表層次上檢查意圖鎖定(而不需要檢查表上的每個行鎖或者頁鎖)，以此來決定事務是否可以安全地鎖定整個表。

　　意圖鎖定分為以下3種不同的類型：

• 意圖共用鎖：該意圖鎖定指已經或者將要在階層的一些較低點處建立共用鎖定。
• 意向獨佔鎖定：它與意圖共用鎖一樣，但是將會在低層項上設定獨佔鎖定。
• 共用意向獨佔鎖定：它指已經或將會在對象階層下面建立共用鎖定，但目的是為了修改資料，所以它會在某個時刻成為意向排它鎖。

　　5、模式鎖

　　模式鎖分為以下兩種。

• 　　模式修改鎖：對對象進行模式改變。在Sch-M鎖期間，不能對對象進行查詢或其他CREATE、ALTER或DROP語句的操作。
• 　　模式穩定鎖鎖定：它和共用鎖定很相似;這個鎖的唯一目的是方式模式修改鎖，因為在該對象上已有其他查詢(或CREATE、ALTER、DROP語句)的鎖。它與其他所有的鎖定相相容。

　　6、批次更新鎖

　　批次更新鎖(BU)只是一種略有不同的表鎖定變體形式。批次更新鎖允許並行載入資料。也就是說，對於其他任何普通操作(T-SQL)都會將表鎖定，但可以同時執行多個BULK INSERT或bcp操作。

鎖的相容性

　　鎖的資源鎖定模式的相容性表格，現有鎖以列顯示，要相容的鎖以行顯示。

鎖的類型	意圖共用鎖(IS)	共用鎖定(S)	更新鎖定(U)	意向獨佔鎖定(IX)	共用意向排它鎖(SIX)	獨佔鎖定(X)
意圖共用鎖(IS)	是	是	是	是	是	否
共用鎖定(S)	是	是	是	否	否	否
更新鎖定(U)	是	是	否	否	否	否
意向獨佔鎖定(IX)	是	否	否	是	否	否
共用意向排它鎖(SIX)	是	否	否	否	否	否
獨佔鎖定(X)	否	否	否	否	否	否

　　另外:

• 　　Sch-S與出Sch-M以外的所有鎖定模式相相容。
• 　　Sch-M和所有的鎖定模式不相容。
• 　　BU只與模式穩定性鎖以及其他的批次更新鎖相相容。

　　有時想要在查詢中或在整個事務中對鎖定有更多的控制。可以通過使用最佳化器提示(optimizer hints)來實現這一點。

　　最佳化器提示明確告訴SQL Server將一個鎖定擴大為特有的層次。這些提示資訊包含在將要影響的表的名稱之後。

　　最佳化器提示是一個進階主題，有經驗的SQL Server開發人員會經常使用它，並且他們相當重視它。

　　使用Management Studio確定鎖

　　查看鎖的最好方式是使用Management Studio。通過使用Activity Monitor，Management Studio會以兩種方式顯示鎖-通過processId或object。

　　為了使用Management Studio顯示鎖，只要導航到<Server>的Activity Monitor節點，其中的<Server>是監控其活動的伺服器的頂級節點。

　　展開感興趣的節點(Overview部分預設展開)，可以通過捲軸查看大量度量值-包括當前系統中有效鎖。

　　

　　顯示介面如下： 　　

設定隔離等級

　　事務和鎖之間的聯絡是很緊密的。預設情況下，一旦建立了任何與資料修改相關的鎖，該鎖定就會在整個事務期間存在。如果有一個大型事務，就意味著將在很長一段時間內阻止其他進程訪問鎖定的對象。這明顯是有問題的。

事務有5種隔離等級：

• READ COMMITTED
• READ UNCOMMITTED
• REPEATABLE READ
• SERIALIZABLE
• SNAPSHOT

　　在這些隔離等級之間進行切換的文法也相當直觀：

　　SET TRANSACTION ISOLATION LEVEL < READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE | SNAPSHOT >

　　對隔離等級的修改只會影響到當前的串連-所以不必擔心會影響到其他的使用者。其他使用者也影響不了你。

　　1、READ COMMITTED

　　預設情況就是這個，通過READ COMMITTED，任何建立的共用鎖定將在建立它們的陳述式完成後自動釋放。也就是說，如果啟動了一個事務，運行了一些語句，然後運行SELECT語句，再運行一些其他的語句，那麼當SELECT陳述式完成的時候，與SELECT語句相關聯的鎖就會釋放 - SQL Server並不會等到事務結束。

　　巨集指令查詢(UPDATE、DELETE、INSERT)有點不同。如果事務執行了修改資料的查詢，則這些鎖將會在事務期間保持有效。

　　通過設定READ COMMITTED這一預設隔離等級，可以確定有足夠的資料完整性來防止髒讀。然而，仍會發生非重複性讀取和幻讀。

　　2、READ UNCOMMITTED

　　READ UNCOMMITTED是所有隔離等級中最危險的，但是它在速度方面有最好的效能。 　　設定隔離等級為READ UNCOMMITTED將告訴SQL Server不要設定任何鎖，也不要事先任何鎖。 　　鎖既是你的保護者，同時也是你的敵人。鎖可以防止資料完整性問題，但是鎖也經常妨礙或阻止你訪問需要的資料。由於此鎖存在髒讀的危險，因此此鎖只能應用於並非十分精確的環境中。

　　3、REPEATABLE READ

　　REPEATABLE READ會稍微地將隔離等級升級，並提供一個額外的並發保護層，這不僅能防止髒讀，而且能防止非重複性讀取。 防止非重複性讀取是很大的優勢，但是直到事務結束還保持共用鎖定會阻止使用者訪問對象，因此會影響效率。推薦使用其他的資料完整性選項，例如CHECK約束，而不是採用這個選擇。 　　與REPEATABLE READ隔離等級等價的最佳化器提示是REPEATABLEREAD(除了一個空格，兩者並無不同)。

　　4、SERIALIZABLE

　　SERIALIZABLE是堡壘級的隔離等級。除了丟失更新以外，它防止所有形式的並發問題。甚至能防止幻讀。

　　如果設定隔離等級為SERIALIZABLE，就意味著對事物使用的表進行的任何UPDATE、DELETE、INSERT操作絕對不滿足該事務中任何語句的WHERE子句的條件。從本質上說，如果使用者想執行一些事務感興趣的事情，那麼必須等到該事務完成的時候。

　　SERIALIZABLE隔離等級也可以通過查詢中使用SERIALIZABLE或HOLDLOCK最佳化器提示類比。再次申明，類似於READ UNCOMMITTED和NOLOCK，前者不需要每次都設定，而後者需要把隔離等級設定回來。

　　5、SNAPSHOT

　　SNAPSHOT是最新的一種隔離等級，非常想READ COMMITTED和READ UNCOMMITTED的組合。要注意的是，SNAPSHOT預設是停用-只有為資料庫開啟了ALLOW_SNAPSHOT_ISOLATION特殊選項時，SNAPSHOT才可用。 　　和READ UNCOMMITED一樣，SNAPSHOT並不建立任何鎖，也不實現人和所。兩者的主要區別是它們識別資料庫中不同時段發生的更改。資料庫中的更改，不管何時或是否提交，都會被運行READ UNCOMMITTED隔離等級的查詢看到。而使用SNAPSHOT，只能看到在SNAPSHOT事務開始之前提交的更改。從SNAPSHOT事務一開始執行，所有查看到的資料就和在時間開始時提交的一樣。

處理死結                                                                                                            

　　死結的錯誤號碼是1205。

　　如果一個鎖由於另一個鎖佔有資源而不能完成應該做的清除資源工作，就會導致死結；反之亦然。當發生死結時，需要其中的一方贏得這場鬥爭，所以SQL Server選擇一個死結犧牲者，對死結犧牲者的事務進行復原，並且通過1205錯誤來通知發生了死結。另外一個事務將繼續正常地運行。

　　1、判斷死結的方式

　　每隔5秒鐘，SQL Server就會檢查所有當前的事務，瞭解他們在等待什麼還未被授予的鎖。然後再一次重新檢查所有開啟的鎖請求的狀態，如果先前請求中有一個還沒有被授予，則它會遞迴地檢查所有開啟的事務，尋找鎖定請求的迴圈鏈。如果SQL Server找到這樣的村換連，則將會選擇一個或更多的死結犧牲者。

　　2、選擇死結犧牲者的方式

　　預設情況下，基於相關事務的"代價"，選擇死結犧牲者。SQL Server將會選擇復原代價最低的事務。在一定程度上，可以使用SQL Server中的DEADLOCK_PRIORITY SET選項來重寫它。

　　3、避免死結

避免死結的常用規則

按相同的順序使用對象
• 使事務儘可能簡短並且在一個批處理中。
• 儘可能使用最低的交易隔離等級。
• 在同一事務中不允許無限度的中斷。
• 在控制環境中，使用綁定串連。

　　1、按相同的順序使用對象

　　例如有兩個表：Suppliers和Products。假設有兩個進程將使用這兩個表。進程1接受庫存輸入，用手頭新的產品總量更新Products表，接下來用已經購買的產品總量來更新Suppliers表。進程2記錄銷售資料，它在Supperlier表中更新銷售產品的總量，然後在Product中減少庫存數量。

　　如果同時運行這兩個進程，那麼就有可能碰到麻煩。進程1試圖擷取Product表上的一個獨佔鎖定。進程2將在Suppliers表上擷取一個獨佔鎖定。然後進程1將試圖擷取Suppliers表上的一個鎖，但是進程1必須等到進程2清除了現有的鎖。同時進程2也在等待進程1清除現有鎖。

　　上面的例子是，兩個進程用相反的順序，鎖住兩個表，這樣就很容易發生死結。

　　如果我們將進程2改成首先在Products減少庫存數量，接著在Suppliers表中更新銷售產品的總數量。兩個進程以相同的順序訪問兩張表，這樣就能夠減少死結的發生。

　　2、使事務儘可能簡短

　　保持事務的簡短將不會讓你付出任何的代價。在事務中放入想要的內容，把不需要的內容拿出來，就這麼簡單。它的原理並不複雜-事務開啟的時間越長，它觸及的內容就越多，那麼其他一些進程想要得到正在使用的一個或者多個對象的可能性就越大。如果要保持事務簡短，那麼就將最小化可能引起死結的對象的數量，還將減少鎖定對象的時間。原理就如此簡單。

　　3、儘可能使用最低的交易隔離等級

　　使用較低的隔離等級和較高的隔離等級相比，共用鎖定持續的時間更短，因此會減少鎖的競爭。

　　4、不要採用允許無限中斷的事務

　　當開始執行某種開放式進程時間，不要建立將一直佔有資源的鎖。通常，指的是使用者互動，但它也可能是允許無限等待的任何進程。

SQL Server 鎖