SQL Server 事务与锁

SQL Server 的事务与并发控制要同时理解四件事：

锁负责并发互斥，隔离级别决定读写可见性，行版本减少读写阻塞，事务日志保证提交、回滚和崩溃恢复。

学习目标

学完这一页，你要能解释：

Read Committed、Repeatable Read、Serializable、Snapshot、RCSI 的差异。
共享锁、排他锁、更新锁、意向锁分别解决什么问题。
锁升级为什么出现，为什么会扩大阻塞。
RCSI 为什么能减少读写阻塞，又为什么增加 TempDB 压力。
一条 update 从加锁、写日志、改页到 commit 的全过程。
如何排查阻塞、死锁、长事务、日志暴涨和 TempDB 版本存储。

事务和日志

SQL Server 使用事务日志保证事务可恢复。

mermaid

flowchart TD
    A["业务执行 update"] --> B["定位数据页"]
    B --> C["获取锁"]
    C --> D["写事务日志记录"]
    D --> E["修改 Buffer Pool 中的数据页"]
    E --> F["维护相关索引"]
    F --> G["commit"]
    G --> H["日志刷盘"]
    H --> I["返回成功"]
    I --> J["脏页后续写回数据文件"]

事务日志不是普通业务日志。宕机恢复时，SQL Server 根据日志重做已提交事务、撤销未提交事务。

隔离级别

隔离级别	读现象	并发影响
Read Uncommitted	可能脏读	并发高但读到未提交数据
Read Committed	避免脏读，默认常见	读写可能阻塞
Repeatable Read	避免不可重复读	持有读锁更久
Serializable	避免幻读	范围锁更严格，并发低
Snapshot	事务级快照读	依赖行版本和 TempDB
RCSI	Read Committed 使用语句级版本读	减少读写阻塞，增加 TempDB 压力

默认 Read Committed 下，读可能被写阻塞。

mermaid

sequenceDiagram
    participant A as 事务A
    participant B as 事务B
    A->>A: update dbo.Asset set Status='USED' where Id=1
    A->>A: 未提交，持有排他锁
    B->>B: select * from dbo.Asset where Id=1
    B-->>A: 等待排他锁释放
    A->>A: commit
    B->>B: 继续读取

常见锁

锁	名称	作用
S	Shared Lock	读锁
X	Exclusive Lock	写锁
U	Update Lock	更新锁，减少锁转换死锁
IS/IX	Intent Lock	表示更低层级有共享/排他锁
Sch-S	Schema Stability	查询编译/执行期间保护结构稳定
Sch-M	Schema Modification	DDL 修改结构

意向锁不是多余的。它让 SQL Server 能在表、页、行多层级锁之间快速判断兼容性。

mermaid

flowchart TD
    A["表级 Intent Lock"] --> B["页级 Intent Lock"]
    B --> C["行级 S/X Lock"]
    A --> D["快速判断是否可申请表级锁"]

更新锁 U Lock

两个事务都先读后改时，可能出现锁转换死锁。更新锁用于“我可能要更新”，让竞争更早排队。

sql

begin tran;

select *
from dbo.Asset with (updlock)
where Id = 1;

update dbo.Asset
set Status = 'USED'
where Id = 1;

commit tran;

不要滥用锁提示。锁提示适合明确知道并发模型的场景，否则会降低并发或掩盖索引问题。

锁升级

SQL Server 管理大量细粒度锁需要内存和 CPU。当锁数量很多时，可能尝试升级为表锁。

mermaid

flowchart TD
    A["事务扫描或更新大量行"] --> B["产生大量行锁/页锁"]
    B --> C["锁管理成本升高"]
    C --> D["SQL Server 尝试锁升级"]
    D --> E["表级锁"]
    E --> F["阻塞范围扩大"]

处理方式：

分批更新或删除。
用索引减少扫描范围。
缩短事务。
避免在事务里处理复杂业务逻辑。
必要时评估分区表和归档。

RCSI 和行版本

开启 RCSI：

sql

alter database YourDb
set read_committed_snapshot on
with rollback immediate;

原理：

mermaid

flowchart TD
    A["写事务更新行"] --> B["旧版本写入版本存储"]
    B --> C["新版本留在数据页"]
    D["读语句开始"] --> E["确定语句时间点"]
    E --> F{"当前行是否符合读时间点"}
    F -- "是" --> G["读取当前版本"]
    F -- "否" --> H["从 TempDB 版本存储读旧版本"]

RCSI 的收益和代价：

收益	代价
普通读不容易被写阻塞	TempDB 版本存储增长
接口查询延迟更稳定	长事务会保留旧版本
读写并发提高	TempDB IO 和空间压力上升

RCSI 不是无锁。写写冲突仍然要锁，DDL 仍可能阻塞，长事务仍然危险。

死锁

mermaid

sequenceDiagram
    participant A as 事务A
    participant B as 事务B
    A->>A: 更新 Asset 1
    B->>B: 更新 Asset 2
    A->>B: 再更新 Asset 2，等待
    B->>A: 再更新 Asset 1，等待

解决：

统一资源访问顺序。
缩短事务。
用合适索引减少锁范围。
避免用户交互、远程调用放在事务里。
分析 deadlock graph，定位具体对象和 SQL。
对可重试业务做有限重试。

阻塞排查

阻塞不是死锁。阻塞可能一直等，死锁会被 SQL Server 检测并选择一个牺牲者。

排查思路：

mermaid

flowchart TD
    A["接口卡住"] --> B["查当前请求和等待"]
    B --> C["找 blocking_session_id"]
    C --> D["定位阻塞 SQL 和事务"]
    D --> E{"是否长事务/未提交"}
    E -- "是" --> F["修事务边界，必要时结束会话"]
    E -- "否" --> G["看扫描范围、锁升级、DDL、索引"]
    G --> H["优化 SQL、索引、批处理策略"]

常见 DMV 方向：

sql

select session_id,
       blocking_session_id,
       wait_type,
       wait_time,
       wait_resource,
       status
from sys.dm_exec_requests
where blocking_session_id <> 0;

结合 SQL 文本：

sql

select r.session_id,
       r.blocking_session_id,
       r.wait_type,
       t.text
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) t
where r.blocking_session_id <> 0;

日志暴涨

日志暴涨常见原因：

原因	说明
大事务	一次更新/删除大量数据
Full 恢复模式未做日志备份	日志不能截断
Always On/复制延迟	日志需要保留给副本
长事务未提交	活跃日志无法截断
索引重建/大批量导入	产生大量日志

处理方向：

查恢复模式和日志备份。
查是否有长事务。
查 AG/复制是否延迟。
大批量任务分批。
不要直接删除 LDF 文件。

商业项目事务边界

场景	推荐做法	不这样会怎样
资产领取	条件更新 + 行数判断	并发覆盖
批量导入	分批提交	日志暴涨、锁升级
报表查询	读库/汇总表/BI	阻塞核心交易
审计流水	主表和流水同事务	审计不一致
远程通知	事务外异步发送	锁被远程接口拖住

可运行 Demo：阻塞与条件更新

建表：

sql

drop table if exists dbo.Asset;

create table dbo.Asset (
  Id bigint identity(1,1) not null primary key,
  AssetNo varchar(64) not null unique,
  Status varchar(20) not null,
  UpdatedAt datetime2 not null default sysdatetime()
);

insert into dbo.Asset(AssetNo, Status)
values ('A001', 'IDLE');

会话 A：

sql

begin tran;

update dbo.Asset
set Status = 'REPAIR',
    UpdatedAt = sysdatetime()
where AssetNo = 'A001';

-- 暂不提交

会话 B：

sql

begin tran;

update dbo.Asset
set Status = 'USED',
    UpdatedAt = sysdatetime()
where AssetNo = 'A001'
  and Status = 'IDLE';

commit tran;

观察：

B 等待 A 的排他锁。
A 提交后，B 重新判断 Status = 'IDLE'。
如果状态已变，B 更新 0 行。
应用层根据影响行数返回并发冲突。

常见坑

坑	后果	正确做法
事务里做远程调用	锁时间不可控	本地提交后异步通知
大批量一次提交	日志暴涨、锁升级	分批提交
打开 RCSI 不监控 TempDB	版本存储压垮 TempDB	监控版本存储和长事务
死锁只做重试	问题反复	统一访问顺序、补索引
删除 LDF 文件	数据库损坏风险	查日志截断原因
以为读永远不阻塞	默认 RC 下读写可能阻塞	理解隔离级别或评估 RCSI

面试标准回答

text

SQL Server 通过锁、隔离级别、行版本和事务日志保证并发一致性。默认 Read Committed 避免脏读，但读写可能互相阻塞；RCSI 可以让 Read Committed 的普通读使用版本存储，减少读写阻塞，但会增加 TempDB 压力。常见锁有共享锁、排他锁、更新锁和意向锁；大量行锁可能升级为表锁，降低锁管理成本但扩大阻塞范围。更新数据时会获取锁、写事务日志、修改 Buffer Pool 中的数据页并维护索引，commit 时日志保证事务可恢复。排查阻塞看 blocking_session_id、等待类型、事务边界、索引和锁升级；死锁要看 deadlock graph 并统一资源访问顺序。

追问：

RCSI 和 Snapshot 有什么区别？
锁升级为什么会导致并发下降？
事务日志为什么不能随便删？
死锁和阻塞有什么区别？

关联知识点

知识点	说明
SQL Server 从零到生产级掌握	主学习线
索引与优化器	聚集/非聚集索引、执行计划
索引、锁与行版本原理	Key Lookup、锁升级、RCSI 深入
性能排查	阻塞、TempDB、日志和参数嗅探

JDK 8/11/17/21 LTS演进

线程池

SQL Server 事务与锁 ​

学习目标 ​

事务和日志 ​

隔离级别 ​

常见锁 ​

更新锁 U Lock ​

锁升级 ​

RCSI 和行版本 ​

死锁 ​

阻塞排查 ​

日志暴涨 ​

商业项目事务边界 ​

可运行 Demo：阻塞与条件更新 ​

常见坑 ​

面试标准回答 ​

关联知识点 ​

SQL Server 事务与锁

学习目标

事务和日志

隔离级别

常见锁

更新锁 U Lock

锁升级

RCSI 和行版本

死锁

阻塞排查

日志暴涨

商业项目事务边界

可运行 Demo：阻塞与条件更新

常见坑

面试标准回答

关联知识点