SQL Server中CURD语句的锁流程分析

龙 宇 2016-7-27 微信营销 0 0

我只在数据库选项已开启“行版本控制的已提交读”(READ_COMMITTED_SNAPSHOT为ON)中进行了观察。

因此只适用于这种环境的数据库。

该类数据库支持四种不同事务隔离级别,下面分别观察数据库的锁行为,测试代码未涉及锁升级。

请先参阅:

数据库引擎中的锁定

https://msdn.microsoft.com/zh-cn/library/ms190615.aspx

一、环境:数据库开启“行版本控制的已提交读”,要求单一连接(非单用户模式)

DECLARE @DBName nvarchar(128), @sql nvarchar(max);
SET @DBName = DB_NAME();
SET @sql = 'ALTER DATABASE '+@DBName+' SET READ_COMMITTED_SNAPSHOT ON;'
EXEC(@sql);
--查看设置结果
SELECT name,snapshot_isolation_state,IS_READ_COMMITTED_SNAPSHOT_ON
FROM sys.databases
WHERE name = @DBName

二、方法:

使用Apq_ID表进行研究

表定义:

CREATE TABLE [dbo].[Apq_ID]
(
[ID] [bigint] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (256) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Crt] [bigint] NOT NULL CONSTRAINT [DF_Apq_ID_Crt] DEFAULT ((0)),
[Limit] [bigint] NOT NULL CONSTRAINT [DF_Apq_ID_Limit] DEFAULT ((0x7FFFFFFF7FFFFFFF)),
[Init] [bigint] NOT NULL CONSTRAINT [DF_Apq_ID_Init] DEFAULT ((0)),
[Inc] [bigint] NOT NULL CONSTRAINT [DF_Apq_ID_Inc] DEFAULT ((1)),
[State] [int] NOT NULL CONSTRAINT [DF_Apq_ID_State] DEFAULT ((0)),
[_Time] [datetime] NOT NULL CONSTRAINT [DF_Apq_ID__Time] DEFAULT (getdate()),
[_InTime] [datetime] NOT NULL CONSTRAINT [DF_Apq_ID__InTime] DEFAULT (getdate())
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Apq_ID] ADD CONSTRAINT [PK_Apq_ID] PRIMARY KEY NONCLUSTERED ([ID]) WITH (FILLFACTOR=80, PAD_INDEX=ON) ON [PRIMARY]
GO

准备,先插入一些行:

SET IDENTITY_INSERT dbo.Apq_ID ON;
INSERT INTO dbo.Apq_ID(ID, Name) VALUES(1, 'ISOLATION');
-- 跳过了2,2用于后面观察INSERT
INSERT INTO dbo.Apq_ID(ID, Name) VALUES(3, '3');
INSERT INTO dbo.Apq_ID(ID, Name) VALUES(4, '4');
INSERT INTO dbo.Apq_ID(ID, Name) VALUES(5, '5');
INSERT INTO dbo.Apq_ID(ID, Name) VALUES(6, '6');
INSERT INTO dbo.Apq_ID(ID, Name) VALUES(7, '7');
INSERT INTO dbo.Apq_ID(ID, Name) VALUES(8, '8');
INSERT INTO dbo.Apq_ID(ID, Name) VALUES(9, '9');
INSERT INTO dbo.Apq_ID(ID, Name) VALUES(10, '10');
INSERT INTO dbo.Apq_ID(ID, Name) VALUES(11, '11');
INSERT INTO dbo.Apq_ID(ID, Name) VALUES(12, '12');
INSERT INTO dbo.Apq_ID(ID, Name) VALUES(13, '13');
INSERT INTO dbo.Apq_ID(ID, Name) VALUES(14, '14');
INSERT INTO dbo.Apq_ID(ID, Name) VALUES(15, '15');
INSERT INTO dbo.Apq_ID(ID, Name) VALUES(16, '16');
INSERT INTO dbo.Apq_ID(ID, Name) VALUES(17, '17');
INSERT INTO dbo.Apq_ID(ID, Name) VALUES(18, '18');
SET IDENTITY_INSERT dbo.Apq_ID OFF;

启动两个连接并记录SPID:

我这里是 56 和 104

对锁申请过程的监控是通过SQL Profiler查看的。过滤设置为只查看这两个连接。

SQL Server中CURD语句的锁流程分析

SQL Server中CURD语句的锁流程分析

本文没有对SQL Profiler监控到的锁申请和释放过程截图,仅用语言描述。

Locks 事件类别

https://msdn.microsoft.com/zh-cn/library/ms177493(v=sql.105).aspx

注意选择符合自己的版本浏览,相同列的同一数值在不同版本可能含义不同。

三、过程:

1、SELECT (S锁)

两个连接均执行以下语句:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; --未提交读
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; --已提交读
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; --可重复读
SET TRANSACTION ISOLATION LEVEL SNAPSHOT; --快照(“行版本控制的已提交读”数据库中不支持)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; --序列化
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;--依次改为上面支持的4种不同级别进行测试,下同
BEGIN TRAN
DECLARE @name nvarchar(256)
SELECT @name = Name
FROM Apq_ID
WHERE ID = 1;--使用索引
-- 测试结束时回滚事务
--ROLLBACK TRAN

可以看到任何隔离级别两个连接均能执行成功,没有阻塞发生。

这时先不要回滚事务,从另外的连接中查看锁的情况:

SELECT OBJECT_ID('dbo.Apq_ID');--记录下来,我这里是1829581556,只是为了方便在结果集中看到哪些是与该表相关的锁
-- 查看锁
EXEC sp_lock 56, 104; --我这里分别是56和104

对比:

READUNCOMMITTED READCOMMITTEDREAD_COMMITTED_SNAPSHOT为ON REPEATABLEREAD SERIALIZABLE
SQL Server中CURD语句的锁流程分析
SQL Server中CURD语句的锁流程分析
同左
SQL Server中CURD语句的锁流程分析
SQL Server中CURD语句的锁流程分析
同左 申请:最小粒度为HBT级S锁(基本上可以理解为表或索引的定义)
释放: 语句 结束释放(所以图上看不到)
定义级锁,“短”生命周期
也就是通常说的“不加锁”,其实只是没有对数据加锁 同左 申请:最小粒度为行级S锁
释放: 事务 结束释放
行级锁,“长”生命周期 这个用例中同左。
如果是表扫描,则最小粒度将变为表级S锁

1.相同点:锁申请均是粒度从大到小。最小粒度的IS锁转换为S锁。(数据库级锁均为S锁)

释放锁时均是按粒度从小到大。

2.不同点:不同隔离级别下申请锁的最小粒度不同,锁的生命周期不同。

S = 共享。 授予持有锁的会话对资源的共享访问权限。

2、UPDATE (U锁和X锁)

两个连接均执行以下语句:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN
DECLARE @Limit bigint, @Inc bigint, @End bigint, @Next bigint;
-- 尝试分配ID
UPDATE Apq_ID
SET _Time = getdate()
,@Limit = Limit
,@Inc = Inc
,@Next = Inc + Crt
,@End = Crt = Inc + Crt
WHERE ID = 1;--直接使用上面查到的1
SELECT @Limit, @Inc, @Next, @End;
-- 测试结束时回滚事务
--ROLLBACK TRAN

这次可以发现均只能有一个连接能执行完成,另一个连接则由于无法获取到锁而无处于等待状态。

同上,先不要回滚,我们看此时的锁(我这里是先执行117,因此它能执行完成):

-- 查看锁
EXEC sp_lock 65, 117; --我这里分别是65和117

对比:

READUNCOMMITTED READCOMMITTED READ_COMMITTED_SNAPSHOT为ON REPEATABLEREAD SERIALIZABLE
SQL Server中CURD语句的锁流程分析

SQL Server中CURD语句的锁流程分析

SQL Server中CURD语句的锁流程分析

SQL Server中CURD语句的锁流程分析
117回滚后再查看
SQL Server中CURD语句的锁流程分析

SQL Server中CURD语句的锁流程分析

SQL Server中CURD语句的锁流程分析

SQL Server中CURD语句的锁流程分析
申请:最小粒度为行级U锁,有实际写入的行变为X锁
(索引同理,RID对应于Key)
释放: 语句 结束释放U锁, 事务 结束释放X锁 申请:最小粒度为行级U锁,有实际写入的行变为X锁
(索引同理,RID对应于Key)
释放: 事务 结束释放U锁和X锁

1.相同点:锁申请均是粒度从大到小,最小粒度锁为行级U锁,其余为IU锁。(数据库级锁均为S锁)

语句执行时实际写入的行才变为X锁,其所在的更大粒度则是变为IX锁。(索引同理)

释放锁时均是按粒度从小到大,X锁 都是

事务

结束释放。 注意:如果是表扫描,则对将要扫描的行都是先加U锁,再判断是否满足条件,不满足时会立即释放。

2.不同点:U锁的生命周期不同。

3.与SELECT对比:没有看到不同的最小粒度。

SELECT与UPDATE小结:

对不同的隔离级别,大致可以这样想:

前两个级别,几乎都一样;

S锁的最小粒度:前两个相同,后两个稍有不同。

U锁的生命周期:前两个和后两个不同;

X锁生命周期:都是事务结束释放。

3、INSERT (X锁)

两个连接(这次是56和104)均执行以下语句:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN
-- 使用固定ID,让其冲突
SET IDENTITY_INSERT dbo.Apq_ID ON;
INSERT INTO dbo.Apq_ID(ID, Name) VALUES(2, 'ISOLATION_INSERT');
SET IDENTITY_INSERT dbo.Apq_ID OFF;
-- 测试结束时回滚事务
--ROLLBACK TRAN

这次可以发现均只能有一个连接能执行完成,另一个连接则由于无法获取到锁而无处于等待状态。

同上,先不要回滚,我们看此时的锁(我这里是先执行56,因此它能执行完成):

-- 查看锁
EXEC sp_lock 56, 104; --我这里分别是56和104

结果:

都一样
SQL Server中CURD语句的锁流程分析
56回滚后再查看
SQL Server中CURD语句的锁流程分析
申请:最小粒度为行级X锁,先行RID后索引Key。释放:事务结束释放X锁 可以看出INSERT语句是先写入行数据并加X锁(此时并没有考虑索引),然后写入索引数据(此时才会冲突而无法获取到X锁)

4、DELETE (U锁和X锁)

两个连接(这次是56和104)均执行以下语句:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN
-- 使用固定ID,让其冲突
DELETE dbo.Apq_ID WHERE ID = 1;
-- 测试结束时回滚事务
--ROLLBACK TRAN

这次可以发现均只能有一个连接能执行完成,另一个连接则由于无法获取到锁而无处于等待状态。

同上,先不要回滚,我们看此时的锁(我这里是先执行56,因此它能执行完成):

-- 查看锁
EXEC sp_lock 56, 104; --我这里分别是56和104

对比:

READUNCOMMITTED READCOMMITTEDREAD_COMMITTED_SNAPSHOT为ON REPEATABLEREAD SERIALIZABLE
SQL Server中CURD语句的锁流程分析
SQL Server中CURD语句的锁流程分析
同左
SQL Server中CURD语句的锁流程分析
SQL Server中CURD语句的锁流程分析
同左 117回滚后再查看
SQL Server中CURD语句的锁流程分析
SQL Server中CURD语句的锁流程分析
同左
SQL Server中CURD语句的锁流程分析
SQL Server中CURD语句的锁流程分析
同在 申请:最小粒度为行级U锁,先索引Key后行RID。
语句:有实际删除的行变为X锁
(索引同理,RID对应于Key)
释放: 语句 结束释放U锁, 事务 结束释放X锁(这一点没有想到如何证明,因为没有想到让U和X同时存在的办法) 同左 申请:最小粒度为行级U锁,先索引Key后行RID。
语句:有实际删除的行变为X锁
(索引同理,RID对应于Key)
释放: 事务 结束释放U锁和X锁 同左 1.相同点:基本一样。2.不同点:U锁的生命周期不同。(未能观察到)

四、全局总结

对四个不同隔离级别,不同的只是锁的最小粒度和生命周期。其中可序列化隔离级别的最小粒度与是否为表扫描相关。

级别 READUNCOMMITTED READCOMMITTEDREAD_COMMITTED_SNAPSHOT为ON REPEATABLEREAD SERIALIZABLE 锁模式 最小粒度 生命周期 最小粒度 生命周期 最小粒度 生命周期 最小粒度 生命周期 S HBT 语句结束 HBT(未开启行版本则是RID/Key?)
括号中的内容是猜的,懒得测了 语句结束 RID/Key 事务结束 RID/Key
[表扫描时为TAB] 事务结束 读取行数据(可能是修改后尚未提交的) 读取最后一次已提交的行(可能是“版本化”数据) HBT级S锁不被 数据锁 阻塞 U RID/Key 语句结束 RID/Key 语句结束 RID/Key 事务结束 RID/Key
[表扫描时为TAB] 事务结束 X RID/Key 事务结束 RID/Key 事务结束 RID/Key 事务结束 RID/Key
[表扫描时为TAB] 事务结束

因为开启了“行版本”,所以对产生实际修改的行,修改前的值会存到tempdb中“版本化”。

事务中多次修改只会保存修改开始时的那个版本(修改前)。

版本化数据由后台线程维护,自动在可以删除时删除。

HBT(MSDN中有的地方也叫HoBT)可以理解为表或索引的结构定义(非数据)。(个人理解,仅供参考)

表(索引)扫描过程中,对将要扫描的行RID(索引Key)都是先加相同模式的锁(S或U),再读取数据进行判断是否满足条件, 不满足 时会立即释放(不等语句结束)。

以下为个人观点,不一定正确,仅供参考 :

A:锁分类

按锁关联的数据类型:索引锁,数据锁。

按锁模式分:S、U、X、BU(本文未测试)

按锁类型分:意向锁、真实锁

锁粒度大致量化:

级别量化 粒度 说明 阻塞其它连接的CURD操作 1 行级 行RID/索引Key 有可能 2 页级 表/索引的页 PAG 有可能 3 表级(表分区)、索引 整个表的数据,或整个索引的数据 TAB 有可能 4 定义级、元数据 表、索引等的结构定义HBT(在CURD操作中,都是IS锁/S锁) 无 5 架构级 Schema 无 6 数据库级 DB 无

B:锁流程分析

通过以上观察,结合锁升级考虑,可以看出SQL Server锁的申请释放流程大致可分申请阶段、执行阶段与释放阶段:

1.申请阶段 :

所有锁申请中,遇到 冲突则等待

流程:根据粒度大小,从大到小申请 : 数据库S锁,架构IS锁(CURD操作总是IS锁,因为并不更改架构),......(各中间粒度的 意向锁) ,直至最小粒度时转换为申请 真实锁 。

这是一个 向下递归过程 ,非意向锁即为出口(即最小粒度)。相同粒度级别的不同数据类型锁, 先索引后数据 且 深度优先 。

这里可能发生锁升级,即当该语句申请的锁数量达到一定程度,锁的最小锁粒度会变成表级。

触发锁升级时,实际上相当于不再申请页级锁和行级锁,而是直接把表级锁当最小粒度进行转换,成功后再释放所有比表级更小粒度的锁。

这也就形成了“数据库引擎不会将行锁或键范围锁升级到页锁,直接升级到表锁”的现象。

关于锁升级,参考MSDN: https://msdn.microsoft.com/zh-cn/library/ms184286.aspx

2.执行阶段:

执行语句操作。(行版本开启与否会影响这里的行为)

3.释放阶段:

根据 该锁在当前隔离级别定义的生命周期 ,先数据后索引,粒度从小到大,逐级释放。(主要是S锁和U锁的释放不同,有的是语句结束时,有的是事务结束时;X锁的释放一定是最外层事务结束时)

特别地,对于意向锁,当其下面更小粒度的锁全部释放时,该意向锁也被释放。这可以看作是一个向上的递归过程,非意向锁是递归出口(即数据库级),这也是为什么数据库级锁只会是实际锁的原因。

C:表提示

关于查询中的表提示(UPDLOCK、XLOCK等):实际上只是改变了申请阶段的真实锁类型(或锁的最小粒度),并不改变整个锁流程及锁的生命周期。

另:READ COMMITTED级别下两个行版本的区别:

READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON:事务中多个语句读取同一个S锁行,不保证值相同,即所谓“只读取最后一次已提交的版本”, 语句级 。

ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON:事务中多个语句读取同一个S锁行,保证值相同,即所谓“只读取事务开始时的版本”, 事务级 。

这个差别应该也是通过锁实现,猜测是通过S锁的生命周期在后者为“事务结束“实现的。

五、资料:

了解基于行版本控制的隔离级别

https://msdn.microsoft.com/zh-cn/library/ms189050(v=sql.105).aspx

sp_lock 结果集部分说明(MSDN):

IndId 持有锁的索引的标识号。

Type 锁的类型:

锁类型 粒度 粒度级别(个人看法) RID = 表中单个行的锁,由行标识符 (RID) 标识。 数据行 RID 1 KEY = 索引内保护可串行事务中一系列键的锁。 索引键 Key 1 PAG = 数据页或索引页的锁。 页 PAG 2 EXT = 对某区的锁。 区 EXT (max类的列) 1 TAB = 整个表(包括所有数据和索引)的锁。 表 TAB 3

上面的锁按锁模式定义可能阻塞其它连接的CURD操作。

下面的锁则不会阻塞其它连接的CURD操作,但仍有可能阻塞CURD以外的其它操作,如更改架构,表结构,索引定义等。

DB = 数据库的锁。 库 DB 6 FIL = 数据库文件的锁。 文件 FIL 5 APP = 指定的应用程序资源的锁。 5 MD = 元数据或目录信息的锁。 4 HBT = 堆或 B 树索引的锁。在 SQL Server 中此信息不完整。 4 AU = 分配单元的锁。在 SQL Server 中此信息不完整。

Resource 标识被锁定资源的值。 值的格式取决于 Type 列标识的资源类型:

RID:格式为 fileid:pagenumber:rid 的标识符,其中 fileid 标识包含页的文件,pagenumber 标识包含行的页,rid 标识页上的特定行。 fileid 与 sys.database_files 目录视图中的 file_id 列相匹配。

KEY:数据库引擎内部使用的十六进制数。

Mode 所请求的锁模式。 可以是:

U = 更新。 指示对最终可能更新的资源获取的更新锁。 用于防止一种常见的死锁,这种死锁在多个会话锁定资源以便稍后对资源进行更新时发生。

X = 排他。 授予持有锁的会话对资源的独占访问权限。

Status 锁的请求状态:

GRANT:已获取锁。

WAIT:锁被另一个持有锁(模式相冲突)的进程阻塞。

 

原文地址:https://www.77169.com/classical/HTML/200739.shtm

转载请注明来自华盟网,本文标题:《SQL Server中CURD语句的锁流程分析》

喜欢 (0) 发布评论