【SQL SERVER】鎖機制-有解無憂

鎖定是 SQL Server 資料庫引擎用來同步多個用戶同時對同一個資料塊的訪問的一種機制，

基本概念
利用SQL Server Profiler觀察鎖
死鎖產生的原因及避免
總結

基本概念

資料庫引擎隔離級別

隔離級別	定義
未提交的讀取	隔離事務的最低級別，只能保證不讀取物理上損壞的資料，在此級別上，允許臟讀，因此一個事務可能看見其他事務所做的尚未提交的更改
已提交的讀取	允許事務讀取另一個事務以前讀取（未修改）的資料，而不必等待第一個事務完成， 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

參考文章

事務鎖定和行版本控制指南 - SQL Server | Microsoft Docs

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/5556.html

標籤：SQL Server

上一篇：常用SQL Server進行性能優化陳述句

下一篇：【SQL SERVER】索引