序

本文屬于基礎(chǔ)知識(shí)的回顧，在日常技術(shù)交流和日常工作中經(jīng)常發(fā)現(xiàn)有些同事了解關(guān)于數(shù)據(jù)庫(kù)事務(wù)的基本知識(shí)，會(huì)看SQL語(yǔ)句的執(zhí)行計(jì)劃，也知道數(shù)據(jù)庫(kù)有X鎖、U鎖和S鎖等各種鎖，但是對(duì)于這些鎖在數(shù)據(jù)庫(kù)事務(wù)執(zhí)行期間是如何工作？為何這樣配合才能完成數(shù)據(jù)庫(kù)事務(wù)？數(shù)據(jù)庫(kù)是如何對(duì)于各種資源加鎖的？等等這類的問(wèn)題不太了解，那么對(duì)于事務(wù)的執(zhí)行肯定不會(huì)有深刻的認(rèn)識(shí)。

這類知識(shí)雖然從網(wǎng)上搜索可以找到很多，但是大多內(nèi)容重復(fù)，并且只注重理論知識(shí)而沒(méi)有實(shí)踐路徑。就好比池塘中的青蓮只可遠(yuǎn)觀而無(wú)法靠近仔細(xì)觀察，猶如霧里看花水中望月，對(duì)于其真實(shí)原理總是似懂非懂。

紙上得來(lái)終覺淺，絕知此事要躬行，只有親自動(dòng)手進(jìn)行分析才能對(duì)這些問(wèn)題有深入的認(rèn)識(shí)，因此本文計(jì)劃從數(shù)據(jù)庫(kù)的基礎(chǔ)知識(shí)入手，以詳細(xì)的實(shí)踐分析步驟引導(dǎo)認(rèn)識(shí)數(shù)據(jù)庫(kù)事務(wù)的執(zhí)行過(guò)程，以期讀者可以對(duì)于事務(wù)有更加深刻的理解。

SQL Server使用的鎖及鎖對(duì)象

數(shù)據(jù)庫(kù)引擎使用不同的鎖模式鎖定資源，通過(guò)不同鎖的組合使用達(dá)到不同的數(shù)據(jù)庫(kù)事務(wù)隔離級(jí)別。

意向鎖又細(xì)分為多種類型：

架構(gòu)鎖細(xì)分為兩種類型：

通常開發(fā)人員談到數(shù)據(jù)庫(kù)的鎖的時(shí)候習(xí)慣說(shuō)數(shù)據(jù)庫(kù)鎖、表鎖或者行鎖。這種描述通常是從被鎖定資源的角度來(lái)談?wù)?，通過(guò)檢索SQL Server2016的文檔發(fā)現(xiàn)數(shù)據(jù)庫(kù)上鎖定更多的資源不只是這三種維度，還有11種類型。

了解了數(shù)據(jù)庫(kù)的鎖及其鎖定對(duì)象，那么日常使用的select、insert和update語(yǔ)句到底是如何應(yīng)用這些概念呢？

SQL Server執(zhí)行Select時(shí)使用的鎖

首先通過(guò)建表腳本創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)表：

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語(yǔ)句在SQL Server的默認(rèn)事務(wù)隔離級(jí)別（read commited）中執(zhí)行完成后就會(huì)釋放相關(guān)的鎖，而非等到事務(wù)結(jié)束，在這種情況下無(wú)法通過(guò)sp_lock或者sys.dm_tran_locks視圖觀察select語(yǔ)句執(zhí)行過(guò)程中鎖的執(zhí)行情況，因此比較方便的辦法是在查詢語(yǔ)句執(zhí)行之前調(diào)整當(dāng)前會(huì)話的事務(wù)隔離級(jí)別為repeatable read，在這個(gè)隔離級(jí)別中select語(yǔ)句默認(rèn)會(huì)在事故執(zhí)行完成后提交，比較方便分析。

在SQL Server Manager Studio的查詢窗口中執(zhí)行語(yǔ)句：

set transaction isolation level repeatable read
set statistics profile on 
begin tran
select * from usertable where  id='5E4B68B0-71B8-43FB-B6B4-8E9D43A30589'

在前面的事務(wù)目前是已經(jīng)執(zhí)行未提交的狀態(tài)，此時(shí)可以通過(guò)dm_tran_locks查詢到該語(yǔ)句目前持有的鎖：

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

查詢結(jié)果字段說(shuō)明：

• request_session_id：會(huì)話編號(hào)
• resource_type：被鎖定的資源類型
• request_status：請(qǐng)求的狀態(tài)
• request_mode：鎖類型
• resource_description 資源描述情況
• objectName：對(duì)象名稱

目前select查詢持有的鎖：

• 通過(guò)目前的查詢結(jié)果可以看到在DATABASE上加了S鎖（數(shù)據(jù)庫(kù)名為Test）；
• 在數(shù)據(jù)所屬的頁(yè)上增加了意向共享鎖；
• 表上增加了意向共享鎖；
• 數(shù)據(jù)行上增加了共享鎖；

目前的事務(wù)執(zhí)行過(guò)程中只對(duì)于匹配到的數(shù)據(jù)行進(jìn)行了鎖定，如果插入刪除語(yǔ)句并未涉及到該數(shù)據(jù)行就不會(huì)受到影響，但是如果涉及到這行數(shù)據(jù)那肯定需要等S鎖釋放后才能進(jìn)行。

SQL Server執(zhí)行insert時(shí)使用的鎖

首先在事務(wù)中執(zhí)行insert語(yǔ)句并且不提交(注意將上個(gè)章節(jié)中的事務(wù)提交)：

begin tran

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

insert的時(shí)候默認(rèn)會(huì)有事務(wù)，因此主動(dòng)聲明一個(gè)事務(wù)并只執(zhí)行不提交就可以很容易的查到當(dāng)前會(huì)話持有的鎖。

通過(guò)dm_tran_locks查詢到該語(yǔ)句目前持有的鎖：

該事務(wù)持有的鎖：

• 數(shù)據(jù)庫(kù)層面的共享鎖；
• 數(shù)據(jù)頁(yè)上的意向排他鎖；
• 數(shù)據(jù)表的意向排他鎖；
• 數(shù)據(jù)行的排他鎖；

結(jié)合上文中對(duì)于鎖類型的講解可以很容易理解數(shù)據(jù)庫(kù)增加這些鎖的用意。數(shù)據(jù)庫(kù)層面增加S鎖可以保護(hù)當(dāng)前正在進(jìn)行的事務(wù)的安全，同時(shí)針對(duì)發(fā)生數(shù)據(jù)變化的數(shù)據(jù)頁(yè)和數(shù)據(jù)表增加意向排他鎖可以防止其他事務(wù)對(duì)于數(shù)據(jù)庫(kù)和數(shù)據(jù)頁(yè)進(jìn)行更高層的修改（比如架構(gòu)級(jí)別或者DDL之類的事務(wù)），IX鎖對(duì)于IX和IS是可以并存的，因此可以最大限度上支持同一個(gè)區(qū)域內(nèi)的其他修改和查詢事務(wù)。

SQL Server執(zhí)行update時(shí)使用的鎖

首先在數(shù)據(jù)庫(kù)中執(zhí)行update語(yǔ)句而不提交（注意將上個(gè)章節(jié)中的事務(wù)提交或者回滾）：

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

通過(guò)dm_tran_locks查詢到該語(yǔ)句目前持有的鎖：

該事務(wù)持有的鎖：

• 數(shù)據(jù)庫(kù)層面的共享鎖；
• 數(shù)據(jù)頁(yè)上的意向排他鎖；
• 數(shù)據(jù)表上面的意向排他鎖；
• 數(shù)據(jù)行的排他鎖；

有了insert的經(jīng)驗(yàn)后，理解update語(yǔ)句使用的鎖難度就不大了。其與insert使用的鎖的類型基本一樣，由于本次是使用主鍵進(jìn)行修改，數(shù)據(jù)庫(kù)可以直接定位到需要進(jìn)行變更的數(shù)據(jù)行，因此只需要在對(duì)應(yīng)的行上增加X鎖就可以滿足事務(wù)的需要。

日常使用的時(shí)候很少直接通過(guò)id更新數(shù)據(jù)，往往基于一些非聚集索引更新數(shù)據(jù)，在這種情況下數(shù)據(jù)庫(kù)對(duì)于鎖的使用會(huì)有什么不一樣呢？首先針對(duì)測(cè)試的數(shù)據(jù)表增加兩個(gè)索引:

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

然后將update語(yǔ)句修改為根據(jù)name更新數(shù)據(jù)：

begin tran 
set statistics profile on 
update UserTable set lastmodifytime=GETDATE()  where name like '%test%'

該語(yǔ)句對(duì)應(yīng)的鎖的情況統(tǒng)計(jì)：

可以發(fā)現(xiàn)通過(guò)非聚集索引更新數(shù)據(jù)的時(shí)候，數(shù)據(jù)庫(kù)需要檢查的內(nèi)容明顯增加，并且增加IX鎖的數(shù)據(jù)也多了不少。只看這個(gè)表格可能不太好理解，這些key對(duì)應(yīng)的X鎖為什么要增加，以及是使用的哪個(gè)索引呢？

為了了解更多的信息，上文中查詢事務(wù)鎖的語(yǔ)句需要進(jìn)行一些改動(dòng)，增加對(duì)于索引的關(guān)聯(lián)查詢：

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

通過(guò)關(guān)聯(lián)查詢索引信息，得到了更豐富的內(nèi)容：

從上表中可以看出在更新數(shù)據(jù)的時(shí)候，由于涉及到多行的非聚集索引上面的數(shù)據(jù)，因此對(duì)于該索引涉及到的數(shù)據(jù)行都增加了X鎖，涉及到的數(shù)據(jù)頁(yè)也比之前更多了。類型為X鎖，同時(shí)索引名稱為PK__Test1__3213E83F133024F3的有兩行，因?yàn)楸敬问聞?wù)匹配到了兩行數(shù)據(jù)；類型為X鎖，同時(shí)索引名稱為idx_UserTable_LastModifyTime的一共有四行。為什么是四行呢？因?yàn)橛袃蓚€(gè)舊的數(shù)據(jù)需要?jiǎng)h除，同時(shí)新增了兩個(gè)新的數(shù)據(jù)，所以是四行。其他的非聚集索引的數(shù)據(jù)并沒(méi)有修改，所以本次不需要申請(qǐng)X鎖。

總結(jié)

數(shù)據(jù)庫(kù)中的各種事務(wù)隔離級(jí)別都是通過(guò)對(duì)于不同鎖的綜合運(yùn)用實(shí)現(xiàn)的。對(duì)于鎖的認(rèn)識(shí)可以從兩個(gè)角度進(jìn)行：鎖模式和鎖對(duì)象。哪怕是一個(gè)簡(jiǎn)單的select語(yǔ)句都會(huì)有默認(rèn)的某種鎖以保護(hù)數(shù)據(jù)的正確性。需要注意不同的數(shù)據(jù)組合情況、不同的事務(wù)隔離級(jí)別下SQL語(yǔ)句的執(zhí)行過(guò)程可能是不一樣的，因此其使用的鎖也會(huì)千變?nèi)f化，本文所列舉的只是一些很簡(jiǎn)單的情況，但是規(guī)則類似，分析路徑也是基本一致的，有興趣的可以自己嘗試下日常工作中語(yǔ)句的執(zhí)行過(guò)程中使用的鎖，這對(duì)于理解數(shù)據(jù)庫(kù)工作原理，有針對(duì)性的對(duì)于SQL語(yǔ)句調(diào)優(yōu)都有一定幫助（注意不要在生產(chǎn)環(huán)境執(zhí)行這類分析）。

到此這篇關(guān)于一文分析SQL Server中事務(wù)使用的鎖的文章就介紹到這了,更多相關(guān)SQL Server事務(wù)鎖內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論