动手分析SQL Server中的事务中使用的锁

2022-09-05 中年农码工

序

本文属于基础知识的回顾，在日常技术交流和日常工作中经常发现有些同事了解关于数据库事务的基本知识，会看SQL语句的执行计划，也知道数据库有X锁、U锁和S锁等各种锁，但是对于这些锁在数据库事务执行期间是如何工作？为何这样配合才能完成数据库事务？数据库是如何对于各种资源加锁的？等等这类的问题不太了解，那么对于事务的执行肯定不会有深刻的认识。

这类知识虽然从网上搜索可以找到很多，但是大多内容重复，并且只注重理论知识而没有实践路径。就好比池塘中的青莲只可远观而无法靠近仔细观察，犹如雾里看花水中望月，对于其真实原理总是似懂非懂。

纸上得来终觉浅，绝知此事要躬行，只有亲自动手进行分析才能对这些问题有深入的认识，因此本文计划从数据库的基础知识入手，以详细的实践分析步骤引导认识数据库事务的执行过程，以期读者可以对于事务有更加深刻的理解。

SQL Server使用的锁及锁对象

数据库引擎使用不同的锁模式锁定资源，通过不同锁的组合使用达到不同的数据库事务隔离级别。

锁模式	编号	效果说明
共享锁	S	共享锁，通常用于不修改数据也不希望数据被修改的场景
更新锁	U	用于可更新的资源，防止这类资源在读取、锁定以及随后可能进行的资源更新时出现死锁
排他锁	X	用于修改数据的操作，例如insert、update和delete，防止对同一个资源进行多重修改
意向锁		包括意向共享、意向更新和意向排他三种，用于保护较低级别的锁并提升性能
架构锁		用于执行依赖表结构的操作时使用，包括架构修改 (Sch-M) 和架构稳定性 (Sch-S)
大容量更新	BU	在将数据大容量复制到表中且指定了 TABLOCK 提示时使用
键范围		当使用可序列化事务隔离级别时保护查询读取的行的范围。

意向锁又细分为多种类型：

锁模式	编号	效果说明
意向共享	IS	保护针对底层资源的共享锁
意向排他	IX	保护针对底层资源的排他锁是，IS的超集
共享意向排他	SIX	保护针对低层资源请求或获取的意向排他锁以意向共享锁
意向更新	IU	保护针对底层资源的更新锁
共享意向更新	SIU	S锁和IU锁的组合，作为分别获取并同时具备两种锁的组合效果
更新意向排他	UIX	U锁和IX锁的组合，作为分别获取并同时具备两种锁的组合效果

架构锁细分为两种类型：

锁模式	编号	效果说明
架构修改锁	Sch-M	DDL执行期间使用架构修改锁，该锁会阻止对于表的所有访问
架构稳定锁	Sch-S	该锁不会影响S、U以及X锁的执行，但是会阻止DDL的执行

通常开发人员谈到数据库的锁的时候习惯说数据库锁、表锁或者行锁。这种描述通常是从被锁定资源的角度来谈论，通过检索SQL Server2016的文档发现数据库上锁定更多的资源不只是这三种维度，还有11种类型。

锁对象	关于锁对象的说明
AllocUnit	分配单元
Application	应用程序专用的资源
Database	整个数据库
Extent	一组连续的8个页
File	数据库文件
Heap/B-tree	堆或者B树
Key	索引上的某一行
Metadata	元数据
Object	表、存储过程、视图等包括所有的数据和索引
OIB	用于联机索引构建时的锁
Page	数据库上8KB页
RID	堆上的某一行
RowGroup	列存储索引行组的时候使用的锁
Xact	事务的锁定资源

了解了数据库的锁及其锁定对象，那么日常使用的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
62	DATABASE	GRANT	S		Test
62	PAGE	GRANT	IS	0.236111111	UserTable
62	OBJECT	GRANT	IS		UserTable
62	KEY	GRANT	S	(0ee48b5e6942)	UserTable

查询结果字段说明：

request_session_id：会话编号
resource_type：被锁定的资源类型
request_status：请求的状态
request_mode：锁类型
resource_description 资源描述情况
objectName：对象名称

目前select查询持有的锁：

通过目前的查询结果可以看到在DATABASE上加了S锁（数据库名为Test）；
在数据所属的页上增加了意向共享锁；
表上增加了意向共享锁；
数据行上增加了共享锁；

目前的事务执行过程中只对于匹配到的数据行进行了锁定，如果插入删除语句并未涉及到该数据行就不会受到影响，但是如果涉及到这行数据那肯定需要等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查询到该语句目前持有的锁：

request_session_id	resource_type	request_status	request_mode	resource_description	objectName
70	DATABASE	GRANT	S		Test
70	PAGE	GRANT	IX	1:280	UserTable
70	OBJECT	GRANT	IX		UserTable
70	KEY	GRANT	X	(c75ad92ba798)	UserTable

该事务持有的锁：

数据库层面的共享锁；
数据页上的意向排他锁；
数据表的意向排他锁；
数据行的排他锁；

结合上文中对于锁类型的讲解可以很容易理解数据库增加这些锁的用意。数据库层面增加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查询到该语句目前持有的锁：

request_session_id	resource_type	request_status	request_mode	resource_description	objectName
52	DATABASE	GRANT	S		Test
52	PAGE	GRANT	IX	1:280	UserTable
52	OBJECT	GRANT	IX		UserTable
52	KEY	GRANT	X	(ead909dc80bf)	UserTable

该事务持有的锁：

数据库层面的共享锁；
数据页上的意向排他锁；
数据表上面的意向排他锁；
数据行的排他锁；

有了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%'

该语句对应的锁的情况统计：

request_session_id	resource_type	request_status	request_mode	resource_description	objectName
52	DATABASE	GRANT	S		Test
52	PAGE	GRANT	IX	1:280	UserTable
52	PAGE	GRANT	IX	1:368	UserTable
52	KEY	GRANT	X	(ba4eae1b81ad)	UserTable
52	KEY	GRANT	X	(500c265deab6)	UserTable
52	KEY	GRANT	X	(a1a185fdb4ae)	UserTable
52	OBJECT	GRANT	IX		UserTable
52	KEY	GRANT	X	(ff4928fe375a)	UserTable
52	KEY	GRANT	X	(0ee48b5e6942)	UserTable

可以发现通过非聚集索引更新数据的时候，数据库需要检查的内容明显增加，并且增加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

通过关联查询索引信息，得到了更丰富的内容：

request_session_id	resource_type	request_status	request_mode	resource_description	objectName	index_id	索引名称
52	DATABASE	GRANT	S		Test	NULL	NULL
52	PAGE	GRANT	IX	1:280	UserTable	1	PK__Test1__3213E83F133024F3
52	PAGE	GRANT	IX	1:368	UserTable	4	idx_UserTable_LastModifyTime
52	KEY	GRANT	X	(ba4eae1b81ad)	UserTable	4	idx_UserTable_LastModifyTime
52	KEY	GRANT	X	(500c265deab6)	UserTable	4	idx_UserTable_LastModifyTime
52	KEY	GRANT	X	(a1a185fdb4ae)	UserTable	1	PK__Test1__3213E83F133024F3
52	OBJECT	GRANT	IX		UserTable	NULL	NULL
52	KEY	GRANT	X	(ff4928fe375a)	UserTable	4	idx_UserTable_LastModifyTime
52	KEY	GRANT	X	(0ee48b5e6942)	UserTable	1	PK__Test1__3213E83F133024F3
52	KEY	GRANT	X	(150ba0b85c41)	UserTable	4	idx_UserTable_LastModifyTime

从上表中可以看出在更新数据的时候，由于涉及到多行的非聚集索引上面的数据，因此对于该索引涉及到的数据行都增加了X锁，涉及到的数据页也比之前更多了。类型为X锁，同时索引名称为PK__Test1__3213E83F133024F3的有两行，因为本次事务匹配到了两行数据；类型为X锁，同时索引名称为idx_UserTable_LastModifyTime的一共有四行。为什么是四行呢？因为有两个旧的数据需要删除，同时新增了两个新的数据，所以是四行。其他的非聚集索引的数据并没有修改，所以本次不需要申请X锁。

总结

数据库中的各种事务隔离级别都是通过对于不同锁的综合运用实现的。对于锁的认识可以从两个角度进行：锁模式和锁对象。哪怕是一个简单的select语句都会有默认的某种锁以保护数据的正确性。需要注意不同的数据组合情况、不同的事务隔离级别下SQL语句的执行过程可能是不一样的，因此其使用的锁也会千变万化，本文所列举的只是一些很简单的情况，但是规则类似，分析路径也是基本一致的，有兴趣的可以自己尝试下日常工作中语句的执行过程中使用的锁，这对于理解数据库工作原理，有针对性的对于SQL语句调优都有一定帮助（注意不要在生产环境执行这类分析）。

参考文档

SQL Server, Locks object
事务锁定和行版本控制指南
Microsoft SQL Server企业级平台管理实践