【SQL SERVER】鎖機制
- 2020 年 4 月 2 日
- 筆記
鎖定是 SQL Server 數據庫引擎用來同步多個用戶同時對同一個數據塊的訪問的一種機制。
基本概念
數據庫引擎隔離級別
| 隔離級別 | 定義 |
|---|---|
| 未提交的讀取 | 隔離事務的最低級別，只能保證不讀取物理上損壞的數據。 在此級別上，允許臟讀，因此一個事務可能看見其他事務所做的尚未提交的更改 |
| 已提交的讀取 | 允許事務讀取另一個事務以前讀取（未修改）的數據，而不必等待第一個事務完成。 SQL Server 數據庫引擎保留寫鎖（在所選數據上獲取）直到事務結束，但是一執行 SELECT 操作就釋放讀鎖。 這是SQL Server 數據庫引擎默認級別 |
| 可重複的讀取 | SQL Server 數據庫引擎保留在所選數據上獲取的讀鎖和寫鎖，直到事務結束。 但是，因為不管理範圍鎖，可能發生虛擬讀取 |
| 可序列化 | 隔離事務的最高級別，事務之間完全隔離。 SQL Server 數據庫引擎保留在所選數據上獲取的讀鎖和寫鎖，在事務結束時釋放它們。 SELECT 操作使用分範圍的 WHERE 子句時獲取範圍鎖，主要為了避免虛擬讀取 |
鎖粒度
| 資源 | 說明 |
|---|---|
| RID | 用於鎖定堆中的單個行的行標識符，也就是常說的行鎖 |
| KEY | 索引中用於保護可序列化事務中的鍵範圍的行鎖 |
| PAGE | 數據庫中的 8 KB 頁，例如數據頁或索引頁，也就常說的業級鎖 |
| EXTENT | 一組連續的八頁，例如數據頁或索引頁 |
| HoBT | 堆或 B 樹。 用於保護沒有聚集索引的表中的 B 樹（索引）或堆數據頁的鎖 |
| TABLE | 包括所有數據和索引的整個表 |
| FILE | 數據庫文件 |
| APPLICATION | 應用程序專用的資源 |
| METADATA | 元數據鎖 |
| ALLOCATION_UNIT | 分配單元 |
| DATABASE | 整個數據庫 |
鎖類型
| 鎖 | 說明 |
|---|---|
| 共享 (S) | 用於不更改或不更新數據的讀取操作，如 SELECT 語句 |
| 更新 (U) | 用於可更新的資源中。 防止當多個會話在讀取、鎖定以及隨後可能進行的資源更新時發生常見形式的死鎖 |
| 排他 (X) | 用於數據修改操作，例如 INSERT、UPDATE 或 DELETE。 確保不會同時對同一資源進行多重更新 |
| 意向 (I) | 用於建立鎖的層次結構。 意向鎖包含三種類型：意向共享 (IS)、意向排他 (IX) 和意向排他共享 (SIX) |
| 架構 (Sch-) | 在執行依賴於表架構的操作時使用。 架構鎖包含兩種類型：架構修改 (Sch-M) 和架構穩定性 (Sch-S) |
| 大容量更新 (BU) | 在將數據大容量複製到表中且指定了 TABLOCK 提示時使用 |
| 鍵範圍 (Range) | 當使用可序列化事務隔離級別時保護查詢讀取的行的範圍。 確保再次運行查詢時其他事務無法插入符合可序列化事務的查詢的行 |
利用SQL Server Profiler觀察鎖
1. 準備數據10條數據
create table DataTable(Id int identity(1,1), [Name] varchar(50), [Address] varchar(200), CreateTime datetime2) insert into DataTable select 'Wilson','廣東省廣州市',GETDATE() union all select 'Alice','北京市朝陽區',GETDATE() union all select 'Miksovsky','吉林省松原市',GETDATE() union all select 'Hines','甘肅省蘭州市',GETDATE() union all select 'Kane','遼寧省瀋陽市',GETDATE() union all select 'Gode','湖北省荊州市',GETDATE() union all select 'Chen','湖南省岳陽市',GETDATE() union all select 'Trenary','福建省廈門市',GETDATE() union all select 'Achong','廣西省玉林市',GETDATE() union all select 'Nixon','江西省景德鎮',GETDATE()
View Code
2. 打開SQL Server Profiler選中鎖事件，勾選type和mode，建議取消不需要觀察的列，然後用列篩選器過濾要觀察的DB
3. 查詢數據
可以看到在頁面級別加上意向共享鎖，因為我們數據只有一頁
4. 更新一條數據
1. 表上加上意向排它鎖（IX），可以用select OBJECT_NAME(581577110) 查看objectid代表的東西
2. 頁級別加上意向更新鎖（IU），告訴SQL Server引擎這裡有更新鎖
3. 獲取第一行的更新鎖（U），這裡條件匹配
4. 頁級別升級為意向排他鎖（IX）, 告訴SQL Server引擎這裡有排他鎖
5. 第一個行更新鎖 升級為排它鎖（X）
6. 釋放鎖
7. 隨條掃描後面的記錄，只是條件不符合，也就不會升級鎖級別
可以看到是全表掃描，因為沒聚集索引（堆表），我們也沒做一個主鍵，下面將Id添加主鍵然後再更新試試
alter table DataTable add constraint PK_DataTable primary key(Id asc)
可以看出，直接在表，頁級別加上意向排它鎖（IX），然後在鍵上加上排它鎖（X）
因為這裡我們用主鍵更新，而且SQL Server主鍵默認是聚集索引，如果指定是非聚集索引主鍵，這裡也會經歷更新鎖 到 排他鎖，有興趣的可以自行驗證
5. 刪除一條數據
這次我們沒用主鍵刪除，過程和更新的第一種情況差不多，就不列了。
因為加了聚集索引，索引定位器執行聚集索引Key的hash，要驗證是否那條記錄，可以在刪除前加上%%lockres%%去查
死鎖產生的原因及避免
死鎖產生的原因
微軟文檔是這樣說
在兩個或多個任務中，如果每個任務鎖定了其他任務試圖鎖定的資源，此時會造成這些任務永久阻塞，從而出現死鎖
我理解就是有2個事務循環依賴對方的資源導致產生死鎖。
例如
1. 事務A 獲取 Row1 資源
2. 事務B 獲取 Row2 資源
3. 事務A獲取Row2資源，由於這時Row2是被事務B佔有，所以必須等事務B完成
4. 事務B獲取Row1資源，由於這時Row1是被事務A佔有，所以必須等事務A完成
SQL Server處理死鎖策略
1. 定期檢查陷入死鎖的任務
2. 若檢查到循環依賴
3. 選擇其中一個作為犧牲品，然後終止事務，然另外一個得以完成
模擬死鎖
分別在兩個不同的會話執行下面語句
begin tran; update DataTable set Address = '上海市' where Id = 2; --延遲5秒執行 WAITFOR DELAY '00:00:05'; update DataTable set Address = '上海市' where Id = 3; commit;
begin tran; update DataTable set Address = '上海市' where Id = 3; --延遲5秒執行 WAITFOR DELAY '00:00:05'; update DataTable set Address = '上海市' where Id = 2; commit;
執行一段時間，其中一個會出現下面錯誤
SQL Server Profiler 捕獲死鎖分析
打開Locks事件的死鎖圖形
重新執行上面語句，模擬死鎖，Profiler捕獲到死鎖
可以看出
1. 進程56 請求的Key 的排它鎖 被進程 54 佔有
2. 進程54 請求的Key 的排他鎖 被進程 56 佔有
3. 形成了循環依賴
我們這裡的Sql比較簡單，而且沒有用參數化執行，所以我們指定是哪一行被鎖，線上的通常不能直接看到哪一行被鎖
我們可以通過xml查看等待的資源，在xml裏面有process-list 下面有多個process，process節點上面有個waitresource屬性，這個指出每個進程等待的資源
鎖類型：db_id : hobt_id : (hashvalue)
KEY: 6:72057594043760640 (61a06abd401c)
通過%%lockres%% 查到被鎖資源
select %%lockres%%,* from DataTable where %%lockres%% = '(98ec012aa510)'
鎖類型不一樣，得到的會不一樣，根據各自的格式用db_name / object_name / dbcc去查到當前被鎖的資源，有時候需要利用DBCC查詢Page存儲頁面，可以參考上一篇文章【SQL SERVER】數據內部存儲結構簡單探索
避免死鎖
首先需要說明死鎖不能完全避免，但遵守特定的編碼慣例可以將發生死鎖的機會降至最低
1. 按同一順序訪問對象，一個獲取鎖，另外一個就必須等待
2. 避免事務中的用戶交互 ，這樣導致事務時間過長，容易造成死鎖
3. 保持事務簡短並處於一個批處理中，道理和2一樣，盡量讓事務運行時間短。
4. 使用較低的隔離級別，這個看能不能接受臟讀，幻讀等副作用
總結
1. 鎖機制保證並發情況下的數據訪問。
2. 開發中應該盡量利用索引檢索數據，特別是UPDATE/DELETE這種需要排它鎖，應該利用唯一聚集索引字段更新（通常是主鍵）
3. 規範使用事務能減少死鎖發生
轉發請標明出處：https://www.cnblogs.com/WilsonPan/p/12618849.html
