動手分析SQL Server中的事務中使用的鎖

序

本文屬于基礎知識的回顧，在日常技術交流和日常作業中經常發現有些同事了解關于資料庫事務的基本知識，會看SQL陳述句的執行計劃，也知道資料庫有X鎖、U鎖和S鎖等各種鎖，但是對于這些鎖在資料庫事務執行期間是如何作業？為何這樣配合才能完成資料庫事務？資料庫是如何對于各種資源加鎖的？等等這類的問題不太了解，那么對于事務的執行肯定不會有深刻的認識，

這類知識雖然從網上搜索可以找到很多，但是大多內容重復，并且只注重理論知識而沒有實踐路徑，就好比池塘中的青蓮只可遠觀而無法靠近仔細觀察，猶如霧里看花水中望月，對于其真實原理總是似懂非懂，

紙上得來終覺淺，絕知此事要躬行，只有親自動手進行分析才能對這些問題有深入的認識，因此本文計劃從資料庫的基礎知識入手，以詳細的實踐分析步驟引導認識資料庫事務的執行程序，以期讀者可以對于事務有更加深刻的理解，

SQL Server使用的鎖及鎖物件

資料庫引擎使用不同的鎖模式鎖定資源，通過不同鎖的組合使用達到不同的資料庫事務隔離級別，

意向鎖又細分為多種型別：

架構鎖細分為兩種型別：

通常開發人員談到資料庫的鎖的時候習慣說資料庫鎖、表鎖或者行鎖，這種描述通常是從被鎖定資源的角度來談論，通過檢索SQL Server2016的檔案發現資料庫上鎖定更多的資源不只是這三種維度，還有11種型別，

了解了資料庫的鎖及其鎖定物件，那么日常使用的select、insert和update陳述句到底是如何應用這些概念呢？

SQL Server執行Select時使用的鎖

首先通過建表腳本創建一個資料庫表：

USE [Test]
GO
/****** Object:  Table [dbo].[UserTable]    Script Date: 2022/6/29 20:08:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserTable](
	[id] [varchar](36) NOT NULL,
	[name] [varchar](256) NULL,
	[code] [varchar](256) NULL,
	[createtime] [datetime] NULL,
	[lastmodifytime] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

USE [Test]
GO
INSERT [dbo].[UserTable] ([id], [name], [code], [createtime], [lastmodifytime]) VALUES (N'5E4B68B0-71B8-43FB-B6B4-8E9D43A30589', N'test1', N'123456', CAST(N'2022-06-29T18:02:21.517' AS DateTime), CAST(N'2022-06-29T18:02:21.517' AS DateTime))
GO

由于Select陳述句在SQL Server的默認事務隔離級別（read commited）中執行完成后就會釋放相關的鎖，而非等到事務結束，在這種情況下無法通過sp_lock或者sys.dm_tran_locks視圖觀察select陳述句執行程序中鎖的執行情況，因此比較方便的辦法是在查詢陳述句執行之前調整當前會話的事務隔離級別為repeatable read，在這個隔離級別中select陳述句默認會在事故執行完成后提交，比較方便分析，

在SQL Server Manager Studio的查詢視窗中執行陳述句：

set transaction isolation level repeatable read

set statistics profile on 

begin tran
select * from usertable where  id='5E4B68B0-71B8-43FB-B6B4-8E9D43A30589'

在前面的事務目前是已經執行未提交的狀態，此時可以通過dm_tran_locks查詢到該陳述句目前持有的鎖：

select request_session_id,resource_type,request_status,request_mode,resource_description,
case resource_type
when 'Page' then OBJECT_NAME(p.object_id)
when 'object' then OBJECT_NAME(lock.resource_associated_entity_id)
when 'database' then (select name from master..SysDatabases where dbid=resource_database_id)
when 'key' then object_name(p.object_id)
end 
as objectName from sys.dm_tran_locks lock
left join sys.partitions p 
on p.hobt_id=lock.resource_associated_entity_id
order by lock.request_session_id

查詢結果欄位說明：

• request_session_id：會話編號
• resource_type：被鎖定的資源型別
• request_status：請求的狀態
• request_mode：鎖型別
• resource_description 資源描述情況
• objectName：物件名稱

目前select查詢持有的鎖：

1. 通過目前的查詢結果可以看到在DATABASE上加了S鎖（資料庫名為Test）；
2. 在資料所屬的頁上增加了意向共享鎖；
3. 表上增加了意向共享鎖；
4. 資料行上增加了共享鎖；

目前的事務執行程序中只對于匹配到的資料行進行了鎖定，如果插入洗掉陳述句并未涉及到該資料行就不會受到影響，但是如果涉及到這行資料那肯定需要等S鎖釋放后才能進行，

SQL Server執行insert時使用的鎖

首先在事務中執行insert陳述句并且不提交(注意將上個章節中的事務提交)：

begin tran

insert into UserTable (id,code,name,createtime,lastmodifytime)
values(newid(),'test2','測驗用戶2',getdate(),getdate())

insert的時候默認會有事務，因此主動宣告一個事務并只執行不提交就可以很容易的查到當前會話持有的鎖，

通過dm_tran_locks查詢到該陳述句目前持有的鎖：

該事務持有的鎖：

1. 資料庫層面的共享鎖；
2. 資料頁上的意向排他鎖；
3. 資料表的意向排他鎖；
4. 資料行的排他鎖；

結合上文中對于鎖型別的講解可以很容易理解資料庫增加這些鎖的用意，資料庫層面增加S鎖可以保護當前正在進行的事務的安全，同時針對發生資料變化的資料頁和資料表增加意向排他鎖可以防止其他事務對于資料庫和資料頁進行更高層的修改（比如架構級別或者DDL之類的事務），IX鎖對于IX和IS是可以并存的，因此可以最大限度上支持同一個區域內的其他修改和查詢事務，

SQL Server執行update時使用的鎖

首先在資料庫中執行update陳述句而不提交（注意將上個章節中的事務提交或者回滾）：

begin tran 

update UserTable set lastmodifytime=GETDATE()  where id ='06757850-68D6-416C-B3D1-FD3B29BAD4BB'

通過dm_tran_locks查詢到該陳述句目前持有的鎖：

該事務持有的鎖：

1. 資料庫層面的共享鎖；
2. 資料頁上的意向排他鎖；
3. 資料表上面的意向排他鎖；
4. 資料行的排他鎖；

有了insert的經驗后，理解update陳述句使用的鎖難度就不大了，其與insert使用的鎖的型別基本一樣，由于本次是使用主鍵進行修改，資料庫可以直接定位到需要進行變更的資料行，因此只需要在對應的行上增加X鎖就可以滿足事務的需要，

日常使用的時候很少直接通過id更新資料，往往基于一些非聚集索引更新資料，在這種情況下資料庫對于鎖的使用會有什么不一樣呢？首先針對測驗的資料表增加兩個索引:

create nonclustered index idx_UserTable_Name on UserTable(name)
create nonclustered index idx_UserTable_LastModifyTime on UserTable(lastmodifytime)

然后將update陳述句修改為根據name更新資料：

begin tran 

set statistics profile on 

update UserTable set lastmodifytime=GETDATE()  where name like '%test%'

該陳述句對應的鎖的情況統計：

可以發現通過非聚集索引更新資料的時候，資料庫需要檢查的內容明顯增加，并且增加IX鎖的資料也多了不少，只看這個表格可能不太好理解，這些key對應的X鎖為什么要增加，以及是使用的哪個索引呢？為了了解更多的資訊，上文中查詢事務鎖的陳述句需要進行一些改動，增加對于索引的關聯查詢：

with indexs 
as (
SELECT  索引名稱 = a.name ,
        表名 = c.name ,
        索引欄位名 = d.name ,
        a.indid
FROM    sysindexes a
        JOIN sysindexkeys b ON a.id = b.id
                               AND a.indid = b.indid
        JOIN sysobjects c ON b.id = c.id
        JOIN syscolumns d ON b.id = d.id
                             AND b.colid = d.colid
WHERE   a.indid NOT IN ( 0, 255 )  
AND   c.name='UserTable' --查指定表 
)
select request_session_id,resource_type,request_status,request_mode,resource_description,
case resource_type
when 'Page' then OBJECT_NAME(p.object_id)
when 'object' then OBJECT_NAME(lock.resource_associated_entity_id)
when 'database' then (select name from master..SysDatabases where dbid=resource_database_id)
when 'key' then object_name(p.object_id)
end 
as objectName,index_id,i.索引名稱 from sys.dm_tran_locks lock
left join sys.partitions p on p.hobt_id=lock.resource_associated_entity_id
left join indexs  i on i.indid=index_id
order by lock.request_session_id

通過關聯查詢索引資訊，得到了更豐富的內容：

從上表中可以看出在更新資料的時候，由于涉及到多行的非聚集索引上面的資料，因此對于該索引涉及到的資料行都增加了X鎖，涉及到的資料頁也比之前更多了，型別為X鎖，同時索引名稱為PK__Test1__3213E83F133024F3的有兩行，因為本次事務匹配到了兩行資料；型別為X鎖，同時索引名稱為idx_UserTable_LastModifyTime的一共有四行，為什么是四行呢？因為有兩個舊的資料需要洗掉，同時新增了兩個新的資料，所以是四行，其他的非聚集索引的資料并沒有修改，所以本次不需要申請X鎖，

總結

資料庫中的各種事務隔離級別都是通過對于不同鎖的綜合運用實作的，對于鎖的認識可以從兩個角度進行：鎖模式和鎖物件，哪怕是一個簡單的select陳述句都會有默認的某種鎖以保護資料的正確性，需要注意不同的資料組合情況、不同的事務隔離級別下SQL陳述句的執行程序可能是不一樣的，因此其使用的鎖也會千變萬化，本文所列舉的只是一些很簡單的情況，但是規則類似，分析路徑也是基本一致的，有興趣的可以自己嘗試下日常作業中陳述句的執行程序中使用的鎖，這對于理解資料庫作業原理，有針對性的對于SQL陳述句調優都有一定幫助（注意不要在生產環境執行這類分析），

參考檔案

1. SQL Server, Locks object
2. 事務鎖定和行版本控制指南
3. Microsoft SQL Server企業級平臺管理實踐

標籤：其他

上一篇：【StoneDB】產品FAQ

下一篇：開源公開課丨大資料調度系統Taier任務調度介紹