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操作。

鎖的相容性

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

另外:

• 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鎖的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。