SQL Server中的鎖型別及用法

weixin_34127717發表於2017-11-22
SQLServer型別
一. 為什麼要引入鎖 

多個使用者同時對資料庫的併發操作時會帶來以下資料不一致的問題: 

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

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

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

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

二 鎖的分類 

鎖的類別有兩種分法： 

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

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

鎖模式 描述 
共享 (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) 鎖鎖定的資料。 

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

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

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

獨佔鎖：只允許進行鎖定操作的程式使用，其他任何對他的操作均不會被接受。執行資料更新命令時，SQL Server會自動使用獨佔鎖。當物件上有其他鎖存在時，無法對其加獨佔鎖。 
共享鎖：共享鎖鎖定的資源可以被其他使用者讀取，但其他使用者無法修改它，在執行Select時，SQL Server會對物件加共享鎖。 
更新鎖：當SQL Server準備更新資料時，它首先對資料物件作更新鎖鎖定，這樣資料將不能被修改，但可以讀取。等到SQL Server確定要進行更新資料操作時，他會自動將更新鎖換為獨佔鎖，當物件上有其他鎖存在時，無法對其加更新鎖。 

2. 從程式設計師的角度看：分為樂觀鎖和悲觀鎖。 
樂觀鎖：完全依靠資料庫來管理鎖的工作。 
悲觀鎖：程式設計師自己管理資料或物件上的鎖處理。 

MS-SQLSERVER 使用鎖在多個同時在資料庫內執行修改的使用者間實現悲觀併發控制 

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

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

資源 描述 
RID 行識別符號。用於單獨鎖定表中的一行。 
鍵 索引中的行鎖。用於保護可序列事務中的鍵範圍。 
頁 8 千位元組 (KB) 的資料頁或索引頁。 
擴充套件盤區 相鄰的八個資料頁或索引頁構成的一組。 
表 包括所有資料和索引在內的整個表。 
DB 資料庫。 

四 鎖定時間的長短 

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

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

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

用於保護更新的排它鎖將直到事務結束才釋放。 
如果一個連線試圖獲取一個鎖，而該鎖與另一個連線所控制的鎖衝突，則試圖獲取鎖的連線將一直阻塞到： 

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

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

五 SQL Server 中鎖的自定義 

1 處理死鎖和設定死鎖優先順序 

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

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

2 處理超時和設定鎖超時持續時間。 

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

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

示例 
下例將鎖超時期限設定為 1,800 毫秒。 
SET LOCK_TIMEOUT 1800 

3) 設定事務隔離級別。 

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

5) 配置索引的鎖定粒度 
可以使用 sp_indexoption 系統儲存過程來設定用於索引的鎖定粒度 

六 檢視鎖的資訊 

1 執行 EXEC SP_LOCK 報告有關鎖的資訊 
2 查詢分析器中按Ctrl+2可以看到鎖的資訊 

七 使用注意事項 

如何避免死鎖 
1 使用事務時，儘量縮短事務的邏輯處理過程，及早提交或回滾事務； 
2 設定死鎖超時引數為合理範圍，如：3分鐘-10分種；超過時間，自動放棄本次操作，避免程式懸掛； 
3 優化程式，檢查並避免死鎖現象出現； 
4 .對所有的指令碼和SP都要仔細測試，在正是版本之前。 
5 所有的SP都要有錯誤處理（通過@error） 
6 一般不要修改SQL SERVER事務的預設級別。不推薦強行加鎖 

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

八 幾個有關鎖的問題 

1 如何鎖一個表的某一行 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

SELECT * FROM table ROWLOCK WHERE id = 1 

2 鎖定資料庫的一個表 

SELECT * FROM table WITH (HOLDLOCK) 

加鎖語句： 
sybase: 
update 表 set col1=col1 where 1=0 ; 
MSSQL: 
select col1 from 表 (tablockx) where 1=0 ; 
oracle: 
LOCK TABLE 表 IN EXCLUSIVE MODE ； 
加鎖後其它人不可操作，直到加鎖使用者解鎖，用commit或rollback解鎖 


幾個例子幫助大家加深印象 
設table1(A,B,C) 
A B C 
a1 b1 c1 
a2 b2 c2 
a3 b3 c3 

1）排它鎖 
新建兩個連線 
在第一個連線中執行以下語句 
begin tran 
update table1 
set A='aa' 
where B='b2' 
waitfor delay '00:00:30' --等待30秒 
commit tran 
在第二個連線中執行以下語句 
begin tran 
select * from table1 
where B='b2' 
commit tran 

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

2）共享鎖 
在第一個連線中執行以下語句 
begin tran 
select * from table1 holdlock -holdlock人為加鎖 
where B='b2' 
waitfor delay '00:00:30' --等待30秒 
commit tran 

在第二個連線中執行以下語句 
begin tran 
select A,C from table1 
where B='b2' 
update table1 
set A='aa' 
where B='b2' 
commit tran 

若同時執行上述兩個語句，則第二個連線中的select查詢可以執行 
而update必須等待第一個事務釋放共享鎖轉為排它鎖後才能執行 即要等待30秒 

3）死鎖 
增設table2(D,E) 
D E 
d1 e1 
d2 e2 
在第一個連線中執行以下語句 
begin tran 
update table1 
set A='aa' 
where B='b2' 
waitfor delay '00:00:30' 
update table2 
set D='d5' 
where E='e1' 
commit tran 

在第二個連線中執行以下語句 
begin tran 
update table2 
set D='d5' 
where E='e1' 
waitfor delay '00:00:10' 
update table1 
set A='aa' 
where B='b2' 
commit tran 

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

補充一點: 
Sql Server2000支援的表級鎖定提示 

HOLDLOCK 持有共享鎖，直到整個事務完成，應該在被鎖物件不需要時立即釋放，等於SERIALIZABLE事務隔離級別 

NOLOCK 語句執行時不發出共享鎖，允許髒讀 ，等於 READ UNCOMMITTED事務隔離級別 

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

READPAST 讓sql server跳過任何鎖定行，執行事務，適用於READ UNCOMMITTED事務隔離級別只跳過RID鎖，不跳過頁，區域和表鎖 

ROWLOCK 強制使用行鎖 

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

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

應用程式鎖: 
應用程式鎖就是客戶端程式碼生成的鎖，而不是sql server本身生成的鎖 

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

sp_getapplock 鎖定應用程式資源 

sp_releaseapplock 為應用程式資源解鎖 

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

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

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









本文轉自 h2appy  51CTO部落格，原文連結：http://blog.51cto.com/h2appy/772049，如需轉載請自行聯絡原作者

相關文章