SQL Server Optimized Locking 实战：Transaction ID (TID) 锁定内部机制解析

示例工程数据库教程后端【免费下载链接】sql-server-samplesAzure Data SQL Samples - Official Microsoft GitHub Repository containing code samples for SQL Server, Azure SQL, Azure Synapse, and Azure SQL Edge项目地址https://gitcode.com/gh_mirrors/sq/sql-server-samples点击查看免费下载导读本文基于 sql-server-samples 仓库中的 Optimized Locking 示例深入讲解 SQL Server 2025 与 Azure SQL Database 中 Optimized Locking 特性的底层核心——Transaction IDTID事务 ID锁定机制。你将学会如何通过sys.dm_tran_locks动态管理视图读取XACT类型的 TID 锁资源、理解 TID 在数据页中的存储方式并掌握完整的环境搭建与验证脚本从而真正看懂 Optimized Locking 在减少锁内存、抑制锁升级、提升并发方面的运行原理。Optimized Locking 是什么Optimized Locking 是数据库引擎的一项事务锁定改进特性其目标有三减少锁管理消耗的内存降低锁升级Lock Escalation现象的发生频率提升工作负载的并发能力尤其适合高事务量的 OLTP 场景。仓库在 samples/features/readme.md 中对它的定位是Optimized Locking provides an improved transaction locking mechanism that reduces lock memory consumption and increases concurrency for workloads with high transaction volumes. Available in Azure SQL and SQL Server 2025 or higher, it uses Transaction ID (TID) locking and Lock After Qualification (LAQ) to minimize lock escalation and blocking.从这句话可以提炼出两个关键信息第一该特性适用于Azure SQL Database 与 SQL Server 2025 及以上版本第二它的实现建立在两大机制之上即Transaction ID (TID) Locking事务 ID 锁定与Lock After Qualification (LAQ资格判定后加锁)。本示例聚焦于前者的内部细节。依赖项Optimized Locking 并不是凭空引入的全新概念它依赖于数据库中早已存在的两项技术依赖技术依赖程度作用Accelerated Database RecoveryADR加速数据库恢复必需前置条件提供持久化版本存储PVS与版本链是启用 Optimized Locking 的前提Read Committed Snapshot IsolationRCSI已提交读快照隔离非严格必需但强烈建议让 Optimized Locking 发挥全部收益换言之不开启 ADR 就无法启用 Optimized Locking而不开启 RCSIOptimized Locking 依然可以工作但并发收益会打折扣。仓库中另有 加速数据库恢复示例恢复演示可以帮助你进一步理解 ADR 的快速回滚与日志截断原理它是理解 TID 版本链机制的重要前置知识。什么是 Transaction IDTIDTransaction IDTID是一个全局唯一的交易标识符用来标识数据库中的每一次事务。其产生与存储规则如下当基于行版本控制的隔离级别生效时例如 RCSI 下运行或者ADR 被启用时数据库中的每一行在内部都会携带一个事务标识符这个 TID 被物理存储在磁盘上每行附加的 14 字节之中——这 14 字节正是 RCSI 或 ADR 等特性启用后为每行新增的系统开销每个修改某行的事务都会用自己的 TID 给该行“打标签”因此数据库中的每一行都标记着最后一次修改它的那个事务的 TID后续任何对该行的修改都会把 TID 更新为当前修改事务的 TID。理解这一机制是读懂后续实验中XACT锁资源的关键Optimized Locking 正是利用行内这个 TID让引擎能以“事务维度”对行进行锁定与资格判定而不是像传统锁定那样为大量行逐一建立锁结构。环境准备示例适用对象与前置条件在动手实验之前先确认环境适用版本SQL Server 2025或更高版本或 Azure SQL Database编程语言T-SQL工作负载本示例不依赖任何特定业务负载仅需一个可用的数据库实例唯一软件前置条件一个 SQL Server 2025 或 Azure SQL Database 实例。运行本示例完整 Setup仓库为示例提供了完整的建库与配置脚本 create-configure-optimizedlocking-db.sql建议按以下顺序执行从sql-scripts文件夹下载 T-SQL 脚本 create-configure-optimizedlocking-db.sql确认你的 SQL Server 实例中不存在名为OptimizedLocking的数据库脚本会直接CREATE DATABASE同名数据库会导致执行失败在实例上执行该脚本执行后按本文“示例详情”一节中的命令进行验证。脚本逐段解读数据库是如何被配置的该脚本不仅创建数据库还一次性完成了 Optimized Locking 所需的所有数据库级配置值得逐段拆解USE [master]; GO CREATE DATABASE [OptimizedLocking]; GO创建数据库本体。接着是一系列关键数据库选项ALTER DATABASE [OptimizedLocking] SET COMPATIBILITY_LEVEL 170; ALTER DATABASE [OptimizedLocking] SET RECOVERY SIMPLE; ALTER DATABASE [OptimizedLocking] SET PAGE_VERIFY CHECKSUM; ALTER DATABASE [OptimizedLocking] SET ACCELERATED_DATABASE_RECOVERY ON; ALTER DATABASE [OptimizedLocking] SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE [OptimizedLocking] SET OPTIMIZED_LOCKING ON; GO各选项的作用与注意事项如下配置项值说明COMPATIBILITY_LEVEL170即 SQL Server 2025 的兼容级别Optimized Locking 需要该级别及以上RECOVERYSIMPLE简化恢复模式便于演示不影响本示例结论PAGE_VERIFYCHECKSUM页校验保证页完整性检测ACCELERATED_DATABASE_RECOVERYON必需开启 ADR为 Optimized Locking 提供行版本机制支撑READ_COMMITTED_SNAPSHOTON强烈建议开启 RCSI让 Optimized Locking 获得完整并发收益OPTIMIZED_LOCKINGON核心开关启用 Optimized Locking 特性脚本最后还会查询sys.databases确认配置生效状态SELECT [name] AS DatabaseName ,is_accelerated_database_recovery_on AS [ADR Enabled] ,is_read_committed_snapshot_on AS [RCSI Enabled] ,is_optimized_locking_on AS [Optimized Locking Enabled] FROM sys.databases WHERE [name] NOptimizedLocking;这里用到的三个系统列——is_accelerated_database_recovery_on、is_read_committed_snapshot_on、is_optimized_locking_on——是验证三项特性是否真正开启的最直接证据。脚本执行成功后SSMS 会输出OptimizedLocking database created and configured successfully.的提示。示例详情在数据页中读取 TID实验围绕表dbo.TelemetryPacket展开其建表语句如下USE [OptimizedLocking] GO CREATE TABLE dbo.TelemetryPacket ( PacketID INT IDENTITY(1, 1) ,Device CHAR(8000) DEFAULT (Something) ); GO表结构的设计是有讲究的Device列使用CHAR(8000)使得每一行数据恰好占满一个数据页8 KB。这样设计的目的是当后续实验中我们观察行锁定与 TID 行为时锁的粒度与数据页的对应关系一目了然不会被同一页多行数据干扰。实验一INSERT 场景下的 TID 锁定向dbo.TelemetryPacket表插入三行默认值数据。注意三个INSERT被放在同一个事务中执行BEGIN TRANSACTION; INSERT INTO dbo.TelemetryPacket DEFAULT VALUES; INSERT INTO dbo.TelemetryPacket DEFAULT VALUES; INSERT INTO dbo.TelemetryPacket DEFAULT VALUES; SELECT l.resource_description ,l.resource_associated_entity_id ,l.resource_lock_partition ,l.request_mode ,l.request_type ,l.request_status ,l.request_owner_type FROM sys.dm_tran_locks AS l WHERE (l.request_session_id SPID) AND (l.resource_type XACT); COMMIT;关键点在于在提交事务之前我们查询sys.dm_tran_locks动态管理视图并过滤resource_type XACT。在传统 SQL Server 中锁资源的resource_type常见值为KEY、RID、PAGE、TABLE等而启用 Optimized Locking 后sys.dm_tran_locks会暴露一种新的资源类型XACT——这正是 TID 锁在 DMV 中的呈现形态。示例执行后resource_description列报告XACT的值为10:1147:0。其含义拆解如下10数据库 ID 或者锁分区的相关编号1147TID事务 ID即插入这几行的事务标识符0锁分区或子资源序号。文中明确给出结论TID1147代表了插入这几行的事务的标识符。如果该事务最终被确认提交成功这个 TID 就会写入所在行的数据页即每行附加的 14 字节存储区之后对行的每一次修改都会把行内 TID 更新为新事务的 TID。实验二UPDATE 场景下的 TID 锁定接下来对PacketID 2的那一行执行更新同样在提交前再次查询 DMVBEGIN TRANSACTION; UPDATE t SET t.Device Something updated FROM dbo.TelemetryPacket AS t WHERE t.PacketID 2; SELECT l.resource_description ,l.resource_associated_entity_id ,l.resource_lock_partition ,l.request_mode ,l.request_type ,l.request_status ,l.request_owner_type FROM sys.dm_tran_locks AS l WHERE (l.request_session_id SPID) AND (l.resource_type XACT); COMMIT;实验结果与 INSERT 场景一致对于UPDATE命令resource_description列同样展示出正在修改该行的事务的 TID。如果事务确认提交该 TID 将被写入行本身的数据页而在事务未提交期间TID 锁会保护该行防止其他事务以冲突模式访问它。查询结果列的含义两个实验中反复出现的 DMV 列其含义汇总如下便于你在实际环境中解读输出列名含义resource_description资源描述XACT 类型下即为 TID 值形如10:1147:0resource_associated_entity_id与锁资源关联的实体 ID如表、分区等resource_lock_partition锁分区编号request_mode请求的锁模式如 X、S、U 等request_type请求类型本示例中为XACTrequest_status请求状态GRANT/WAIT/CONVERT 等request_owner_type锁所有者类型如 TRANSACTION、CURSOR 等request_session_id SPID的作用是只查看当前会话持有的锁避免其他并发会话的锁干扰观察结果。从机制到原理TID 锁定如何发挥作用将实验现象与机制本身对照可以得出以下结论链行内 TID 是版本机制的产物ADR必需与 RCSI建议启用后每行多出 14 字节用于存放最近一次修改该行的事务 ID锁从“逐行”变为“按事务”传统锁定需要为每个受影响的行建立锁结构行数越多锁内存消耗越大越容易触发锁升级Optimized Locking 通过 TID 锁定将并发控制的粒度落在事务维度的 TID 上配合 LAQ资格判定后加锁机制在行真正满足查询条件之前不轻易加锁DMV 可见性TID 锁以XACT资源类型暴露在 sys.dm_tran_locks 中resource_description直接给出 TID 值这正是本示例让你亲手“看见”的内部机制提交后落盘事务提交确认后TID 才写入数据页这也是为何示例强调“在 COMMIT 之前查询 DMV”——事务进行中TID 锁尚未释放是观察它的最佳窗口。注意事项与免责声明示例中的代码并非构建可扩展企业级应用的最佳实践指南它只是为了展示 TID 锁定的内部行为而刻意简化的教学代码OptimizedLocking数据库若已存在脚本会直接报错请在独立实例或专用环境中执行本示例展示的是 SQL Server 2025 / Azure SQL Database 的行为低版本 SQL Server 不支持OPTIMIZED_LOCKING数据库选项与XACT锁资源类型。相关资源Optimized Locking 官方文档示例 README 的 Related Links 指向optimized-locking特性文档本示例的配置脚本create-configure-optimizedlocking-db.sql仓库特性总览samples/features/readme.mdADR 前置知识加速数据库恢复示例 与 恢复过程演示。赞分享示例工程数据库教程后端【免费下载链接】sql-server-samplesAzure Data SQL Samples - Official Microsoft GitHub Repository containing code samples for SQL Server, Azure SQL, Azure Synapse, and Azure SQL Edge项目地址https://gitcode.com/gh_mirrors/sq/sql-server-samples点击查看免费下载相关推荐Teleport 锁定机制Locking深度解析基于 RFD 9 的访问限制与安全加固实战指南Teleport 锁定机制Locking深度解析基于 RFD 9 的访问限制与安全加固实战指南 导读 当安全团队需要在维护窗口期锁定整个集群、立即终止已持网络安全认证鉴权运维后端复刻老乡鸡农家小炒肉的灵魂调料小炒肉调料成分拆解与实战用量指南CookLikeHOC复刻老乡鸡农家小炒肉的灵魂调料小炒肉调料成分拆解与实战用量指南CookLikeHOC 本指南以 CookLikeHOC 仓库中的 配料/小炒肉调料.md示例工程数据库教程后端3 步装好 drawio-desktop免费编辑 VSDX 图表文件3 步装好 drawio desktop免费编辑 VSDX 图表文件 你以为编辑 Visio 的 .vsdx 文件必须买 Visio 授权绘图工具不是付费就桌面应用图形学上一篇告别Mac高温死机用m-cli 3步掌控硬件温度下一篇OpenCore Legacy Patcher完整指南让旧Mac焕发新生的终极方案创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考