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

博客园 - Amwpfiqvy

如何查看执行计划 SQL Server 堆表与栈表的对比(大表) 树表分级统计 - Amwpfiqvy SQL:查询购买了所有指定商品的人 - Amwpfiqvy 在SQL Server中使用正则表达式查看SQL Server性能时常用的性能计数器 SQL Server中行列转换 Pivot UnPivot Apq本地工具集 Apq.aspx - Amwpfiqvy 第一个：_Config.js JScript.Encode.js - Amwpfiqvy Apq.Threading.js - Amwpfiqvy - 博客园 Script/_Config.js Apq.Text.js Apq.js - Amwpfiqvy - 博客园 prototype.js Apq.aspx - Amwpfiqvy - 博客园查看数据库所有用户表及其列信息利用JScript的Literal Syntax特性用字符串表示对象

Amwpfiqvy · 2015-05-28 · via 博客园 - Amwpfiqvy

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

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

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

请先参阅：

数据库引擎中的锁定

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

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 Profiler监控到的锁申请和释放过程截图，仅用语言描述。

Locks 事件类别

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

三、过程：

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

对比：

READ UNCOMMITTED READ COMMITTED
READ_COMMITTED_SNAPSHOT为ON REPEATABLE READ SERIALIZABLE

同左

同左申请：最小粒度为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

对比：

READ UNCOMMITTED READ COMMITTEDREAD_COMMITTED_SNAPSHOT为ON REPEATABLE READ SERIALIZABLE

117回滚后再查看

申请：最小粒度为行级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

结果：

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

对比：

READ UNCOMMITTED READ COMMITTED
READ_COMMITTED_SNAPSHOT为ON REPEATABLE READ SERIALIZABLE

同左

同左 117回滚后再查看

同左

同在申请：最小粒度为行级U锁，先索引Key后行RID。
语句：有实际删除的行变为X锁
(索引同理，RID对应于Key)
释放：语句结束释放U锁，事务结束释放X锁(这一点没有想到如何证明，因为没有想到让U和X同时存在的办法) 同左申请：最小粒度为行级U锁，先索引Key后行RID。
语句：有实际删除的行变为X锁
(索引同理，RID对应于Key)
释放：事务结束释放U锁和X锁同左 1.相同点：基本一样。
2.不同点：U锁的生命周期不同。(未能观察到)

四、全局总结

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

级别 READ UNCOMMITTED READ COMMITTED
READ_COMMITTED_SNAPSHOT为ON REPEATABLE READ 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锁，因为并不更改架构)，......(各中间粒度的意向锁)，直至最小粒度时转换为申请真实锁。

这是一个向下递归过程，非意向锁即为出口(即最小粒度)。相同粒度级别的不同数据类型锁，URD是先索引后数据且深度优先，C(INSERT)则是反过来，因此是取决于执行计划是先读写索引还是先读写表。

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

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

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

2.执行阶段：

执行语句操作，这里有实际更改的最小粒度U锁会转换为X锁。若最小粒度为行级，则其所在的页级IU锁转换为IX锁。

　　同时行版本开启与否还会影响这里的行为。

3.释放阶段：

根据该锁在当前隔离级别定义的生命周期，按与申请相反的顺序，粒度从小到大，逐级释放。(主要是S锁和U锁的释放不同，有的是语句结束时，有的是事务结束时;X锁的释放一定是最外层事务结束时)

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

C：表提示

关于查询中的表提示(UPDLOCK、XLOCK等)：实际上只是改变了申请阶段的真实锁模式(或锁的最小粒度)，并不改变整个锁流程及所申请的锁在各隔离级别下的生命周期。

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

READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON：事务中多个语句读取同一个数据行，不保证值相同，即所谓“在每条语句启动前提交的所有数据”，语句级。

ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON：事务中多个语句读取同一个数据行，保证值相同，即所谓“在每个事务启动前提交的所有数据”，事务级。

五、资料：

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

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操作，因为它们只是(意向)共享锁，但仍有可能阻塞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：锁被另一个持有锁（模式相冲突）的进程阻塞。

此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。原文来自 — 版权归原作者所有。

推荐订阅源

博客园 - Amwpfiqvy