05.4.1.3 锁、隔离级别与行版本
所属:全栈开发面试题库 / 05 数据库与存储 / 05.4 SQL Server / 05.4.1 架构与存储引擎
覆盖范围
- 锁粒度:行、页、区、表、数据库;锁升级
- 锁模式:S、U、X、IS、IU、IX、Sch-S、Sch-M、BU 与兼容性
- 隔离级别:RU/RC/RR/Serializable/Snapshot/RCSI 与并发现象
- 行版本机制:版本存储、版本链、tempdb 承载
- 阻塞、死锁检测、死锁图与排查命令
- 乐观并发、长事务、与 MySQL / PostgreSQL 并发模型对照
高频题速览
| 编号 | 题目 | 难度 | 频率 |
|---|---|---|---|
| 05.4.1.3-004 | 隔离级别与并发现象 | 进阶 | ★★★★★ |
| 05.4.1.3-006 | RCSI 与快照隔离的区别 | 高级 | ★★★★★ |
| 05.4.1.3-007 | 行版本链与版本存储 | 高级 | ★★★★☆ |
| 05.4.1.3-008 | 阻塞排查 DMV | 高级 | ★★★★★ |
| 05.4.1.3-009 | 死锁检测与死锁图 | 高级 | ★★★★☆ |
| 05.4.1.3-011 | 更新锁 U 的作用 | 进阶 | ★★★★☆ |
| 05.4.1.3-016 | 锁升级阈值与干预 | 高级 | ★★★★☆ |
| 05.4.1.3-017 | 丢失更新与超卖 | 进阶 | ★★★★★ |
锁与行版本模型
事务访问数据
├─ 加锁:粒度(行/页/表/库)× 模式(S/U/X/IS/IU/IX...)
│ └─ 兼容性矩阵判断是否等待;等待形成阻塞链
└─ 行版本(开启快照/RCSI 后)
└─ 更新前旧版本写入 tempdb 版本存储,读请求沿版本链读一致快照
隔离级别:RU → RC(默认) → RR → Serializable;快照隔离 / RCSI 走行版本
05.4.1.3-001 SQL Server 有哪些锁粒度?
频率 ★★★★☆ | 难度 进阶
一句话结论
锁粒度从细到粗包括行键/ RID 锁、页锁、区锁、表锁与数据库锁;细锁并发高但锁开销大,粗锁简单但易阻塞。
概念解释
- 行锁(Key/RID):锁定单行,并发粒度最细。
- 页锁:锁定一个 8KB 页。
- 区锁:锁定 64KB 区。
- 表锁:锁定整张表。
- 数据库锁:如架构稳定性锁、数据库级共享锁。
原理流程
- 引擎默认按成本在细粒度与粗粒度间选择;单语句持锁数量过多时触发锁升级到表锁(见 05.4.1.3-016)。
代码 / 命令
-- 查看当前持锁与等待
SELECT request_session_id, resource_type, resource_associated_entity_id,
request_mode, request_status, request_owner_type
FROM sys.dm_tran_locks
ORDER BY request_session_id;
常见误区
- 误区:行锁一定比表锁好。正解:大范围扫描持大量行锁时,表锁开销更低;粒度选择取决于访问范围。
- 误区:页锁在现代版本被废弃。正解:页锁仍存在,由引擎按需使用。
追问
- 问:意向锁属于哪个粒度层?答:意向锁是表级标记,表明下层有行/页锁,见 05.4.1.3-012。
- 问:锁粒度能由用户强制指定吗?答:可用表提示(如
XLOCK、TABLOCK)指定,但需谨慎评估。
05.4.1.3-002 常见锁模式有哪些?
频率 ★★★★☆ | 难度 进阶
一句话结论
核心模式为共享 S、排他 X、更新 U,加上意向类 IS/IU/IX,以及架构锁 Sch-S/Sch-M 与批量更新 BU。
概念解释
| 模式 | 含义 | 典型场景 |
|---|---|---|
| S(Shared) | 共享读锁 | SELECT(默认 RC 下读完即释放) |
| X(Exclusive) | 排他写锁 | INSERT/UPDATE/DELETE,阻止他人读写 |
| U(Update) | 更新意向锁 | 先扫描待更新行,避免 S→X 转换死锁 |
| IS/IU/IX | 意向共享/更新/排他 | 表级标记,告知下层存在对应锁 |
| Sch-S | 架构稳定锁 | 查询编译期间保护表结构 |
| Sch-M | 架构修改锁 | DDL 期间独占,阻塞所有访问 |
| BU | 批量更新锁 | 批量导入协调并发批量操作 |
原理流程
- 读默认申请 S,写申请 X;U 锁在更新扫描阶段持有,定位目标行后升级为 X。
代码 / 命令
-- 显式申请更新锁(示例,用于理解 U 锁)
SELECT Id FROM dbo.Orders WITH (UPDLOCK) WHERE Status = 0;
常见误区
- 误区:
SELECT在默认隔离级别下一直持有 S 锁到事务结束。正解:READ COMMITTED 下读完成即释放 S 锁。 - 误区:U 锁就是写锁。正解:U 是转换前置锁,最终写仍升级为 X。
追问
- 问:Sch-M 为什么容易造成长时间阻塞?答:DDL 需要等待现有访问结束,期间新请求排队,长事务会拖住 DDL。
- 问:BU 锁和 X 锁兼容吗?答:BU 与其他批量/写操作互斥,具体兼容性按矩阵核对。
05.4.1.3-003 锁兼容性矩阵怎么理解?
频率 ★★★☆☆ | 难度 高级
一句话结论
兼容性判断:已持锁模式与新申请模式是否冲突;S 与 S 兼容,X 与所有模式互斥,U 与 S/U 不兼容、与 X 互斥。
概念解释
- 兼容:多个事务可同时持有,如并发读(S/S)。
- 不兼容:后申请者等待,形成阻塞。
- 意向锁之间相互兼容,用于快速判断表内是否存在细粒度冲突。
原理流程
已持 \ 申请 S U X
S 兼容 不兼容 不兼容
U 不兼容 不兼容 不兼容
X 不兼容 不兼容 不兼容
代码 / 命令
-- 阻塞对:等待任务与其阻塞会话
SELECT w.session_id AS waiting_session, b.session_id AS blocking_session,
w.wait_type, w.wait_duration_ms, t.text AS blocking_sql
FROM sys.dm_os_waiting_tasks w
JOIN sys.dm_exec_requests b ON b.session_id = w.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(b.sql_handle) t
WHERE w.blocking_session_id IS NOT NULL;
常见误区
- 误区:两个 S 锁也会互相阻塞。正解:S/S 兼容,并发读不互斥。
- 误区:意向锁会直接阻塞读。正解:意向锁是标记,冲突判断落到行/页实际模式。
追问
- 问:X 锁为什么与 S 不兼容?答:避免读到未提交的中间状态(脏读)。
- 问:兼容性矩阵在哪里可查?答:Microsoft Learn "锁兼容性" 章节,按模式逐格核对。
05.4.1.3-004 SQL Server 有哪些隔离级别?各解决什么现象?
频率 ★★★★★ | 难度 进阶
一句话结论
常规四级:Read Uncommitted、Read Committed(默认)、Repeatable Read、Serializable;另有快照隔离(Snapshot)与 RCSI 两种行版本隔离级别。
概念解释
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 机制 |
|---|---|---|---|---|
| Read Uncommitted | 可能 | 可能 | 可能 | 不加读锁,可读未提交 |
| Read Committed(默认) | 避免 | 可能 | 可能 | 读时短期 S 锁 |
| Repeatable Read | 避免 | 避免 | 可能 | 事务内持锁更久 |
| Serializable | 避免 | 避免 | 避免 | 范围锁,最严格 |
| Snapshot | 避免 | 避免 | 避免 | 行版本读快照,写仍加锁 |
| RCSI(RC 行版本化) | 避免 | 可能 | 可能 | RC 基础上读走行版本 |
原理流程
- 锁级别越高并发越低;行版本机制让读不阻塞写、写不阻塞读,代价是 tempdb 版本存储开销。
代码 / 命令
-- 设置会话隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
-- 开启 RCSI(数据库级,需单用户/独占设置窗口)
ALTER DATABASE DemoDB SET READ_COMMITTED_SNAPSHOT ON;
常见误区
- 误区:RCSI 就是快照隔离。正解:RCSI 只把 READ COMMITTED 的读改为行版本;快照隔离是独立级别,事务内全程读启动快照。
- 误区:Serializable 一定没有幻读问题但代价可忽略。正解:范围锁显著降低并发,需按业务必要性使用。
追问
- 问:默认隔离级别是什么?答:READ COMMITTED;开启 RCSI 后其读行为变为行版本化。
- 问:隔离级别越高越好吗?答:一致性与并发此消彼长,按业务一致性要求选择。
05.4.1.3-005 脏读、不可重复读、幻读在 SQL Server 分别是什么表现?
频率 ★★★★☆ | 难度 进阶
一句话结论
脏读是读到未提交数据;不可重复读是同一事务内两次读同一行结果不同;幻读是两次范围查询行数不同(新增/删除)。
概念解释
- 脏读:另一事务未提交的修改被读到,回滚后读到的是不存在的数据。
- 不可重复读:行被其他事务提交更新,事务内重读值变化。
- 幻读:其他事务插入/删除满足条件的行,范围查询结果集行数变化。
原理流程
T1 读行 A=1 ── 等待期间 T2 更新 A=2 并提交 ── T1 再读 A=2(不可重复读)
T1 查 status=1 的行集 ── T2 插入 status=1 行并提交 ── T1 再查多出一行(幻读)
代码 / 命令
-- 复现不可重复读:两个会话对照执行
-- 会话 1
BEGIN TRANSACTION;
SELECT Amount FROM dbo.Orders WHERE Id = 1; -- 第一次读
WAITFOR DELAY '00:00:10';
SELECT Amount FROM dbo.Orders WHERE Id = 1; -- 第二次读
COMMIT;
-- 会话 2:在等待窗口内更新并提交
UPDATE dbo.Orders SET Amount = 999 WHERE Id = 1;
COMMIT;
常见误区
- 误区:不可重复读和幻读是一回事。正解:前者是同行值变了,后者是结果集行数变了。
- 误区:RR 级别完全避免幻读。正解:SQL Server RR 仍可能出现幻读,Serializable 才用范围锁避免。
追问
- 问:快照隔离如何避免不可重复读?答:事务内读自己启动时的版本链,不看其他事务后续提交。
- 问:幻读对哪些业务危险?答:范围统计、库存预占、按条件批量处理类业务。
05.4.1.3-006 RCSI 和快照隔离(Snapshot Isolation)有什么区别?
频率 ★★★★★ | 难度 高级
一句话结论
RCSI 把 READ COMMITTED 的读改为行版本化,每条语句读自己语句开始时的提交版本;快照隔离是独立级别,事务内所有语句读事务启动时的一致快照。
概念解释
- RCSI(Read Committed Snapshot Isolation):数据库级开关;读不阻塞写、写不阻塞读;语句级快照,语句间可看到新提交。
- 快照隔离(Snapshot Isolation):需先
ALLOW_SNAPSHOT_ISOLATION ON,事务级快照;写冲突时后提交事务可能被中止(更新冲突)。 - 两者版本都存 tempdb 版本存储。
原理流程
- 更新行前,旧版本复制到版本存储。
- 读请求沿版本链找到符合隔离级别的版本。
- RCSI:按语句开始时点选版本;快照隔离:按事务开始时点选版本。
代码 / 命令
-- RCSI:数据库级开启
ALTER DATABASE DemoDB SET READ_COMMITTED_SNAPSHOT ON;
-- 快照隔离:先允许,会话再使用
ALTER DATABASE DemoDB SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
常见误区
- 误区:开了 RCSI 就不需要锁了。正解:写仍加 X 锁保护修改;只是读不再依赖 S 锁阻塞写。
- 误区:快照隔离不会写冲突。正解:两事务更新同一行,后提交者可能收到更新冲突错误并需重试。
追问
- 问:版本存储放在哪里?答:tempdb;长事务会阻止旧版本清理,膨胀 tempdb。
- 问:RCSI 对应用透明吗?答:对查询行为透明,只需数据库级开启;快照隔离需会话显式设置级别。
05.4.1.3-007 行版本链和版本存储是怎么维护的?
频率 ★★★★☆ | 难度 高级
一句话结论
开启行版本后,每次更新产生旧版本副本存入 tempdb 版本存储,各行通过版本链串联;读请求按所需时点沿链回溯,purge 线程清理不再被引用的版本。
概念解释
- 版本存储(Version Store):tempdb 中保存旧行版本的区域。
- 版本链:当前行指向前一版本,依次串联历史。
- 清理:没有更早事务需要这些版本后,版本被回收;长事务会拖住清理。
原理流程
UPDATE 前:当前行 v1
UPDATE 时:v1 复制进版本存储 → 当前行变为 v2(X 锁保护)
读快照:沿 v2 → v1 找到可见版本
清理:无事务需要 v1 后回收
代码 / 命令
-- 版本存储占用(性能计数器,单位 KB)
SELECT counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Version Store%';
-- 查看当前版本存储中的行版本(按数据库/事务关联)
SELECT TOP 20 database_id, rowset_reference_id,
length_in_bytes, version_sequence_num
FROM sys.dm_tran_version_store
ORDER BY length_in_bytes DESC;
常见误区
- 误区:行版本让更新不产生日志。正解:修改仍写事务日志,版本副本额外占用 tempdb。
- 误区:旧版本会随提交立即删除。正解:必须等所有可能读取它的事务结束后才能清理。
追问
- 问:什么会导致版本存储膨胀?答:长事务、未提交事务、批量更新后仍有旧快照引用。
- 问:RCSI 与快照隔离共用版本存储吗?答:共用 tempdb 版本存储机制,只是可见性时点不同。
05.4.1.3-008 如何定位阻塞(blocking)?
频率 ★★★★★ | 难度 高级
一句话结论
用 sys.dm_os_waiting_tasks 找等待任务与阻塞会话,再关联 sys.dm_exec_requests、会话信息与 SQL 文本还原阻塞链。
概念解释
- 阻塞会话(blocking session):持锁不释放、导致他人等待的会话。
- 阻塞链:A 堵 B、B 堵 C,需沿
blocking_session_id向上追溯到源头。 - 等待类型
LOCK_MUTEX/LCK_M_*表示锁等待。
原理流程
sys.dm_os_waiting_tasks筛blocking_session_id IS NOT NULL。- 关联阻塞会话的 request 与 SQL 文本。
- 查看源头会话状态、事务时长与持锁对象。
代码 / 命令
-- 阻塞链概览
SELECT w.session_id AS waiting_session,
w.blocking_session_id,
w.wait_type, w.wait_duration_ms,
r.status AS blocker_status,
t.text AS blocker_sql
FROM sys.dm_os_waiting_tasks w
LEFT JOIN sys.dm_exec_requests r ON r.session_id = w.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE w.blocking_session_id IS NOT NULL
ORDER BY w.wait_duration_ms DESC;
-- 查看会话打开事务时长与最后语句
SELECT s.session_id, s.login_name, s.login_time,
DATEDIFF(SECOND, t.transaction_begin_time, GETDATE()) AS trx_sec
FROM sys.dm_tran_session_transactions st
JOIN sys.dm_tran_active_transactions t ON t.transaction_id = st.transaction_id
JOIN sys.dm_exec_sessions s ON s.session_id = st.session_id
WHERE s.session_id > 50;
常见误区
- 误区:阻塞会话就是执行最慢的语句。正解:源头可能是一个长时间开着事务、不活跃的会话。
- 误区:
sp_who2的 BlkBy 直接给出根因。正解:它只记录直接阻塞者,链上多级需向上追溯。
追问
- 问:怎么找到阻塞源头持有的锁?答:用
sys.dm_tran_locks按会话 ID 关联锁资源与模式。 - 问:阻塞一定是坏事吗?答:短阻塞是并发控制的正常现象;长时间阻塞才需要干预。
05.4.1.3-009 死锁是怎么检测的?死锁图怎么看?
频率 ★★★★☆ | 难度 高级
一句话结论
引擎锁监视器定期检测等待环,发现循环等待后选择代价较低的事务作为牺牲者回滚;死锁图(XML Deadlock Graph)记录双方会话、语句与持/等锁。
概念解释
- 死锁:两个事务互相持有对方所需锁,循环等待,都无法继续。
- 牺牲者(Victim):被中止回滚的一方,收到 1205 错误。
- 死锁图:包含进程、资源、等待边与持有边,可从扩展事件/系统视图获取。
原理流程
- T1 持 A 等 B,T2 持 B 等 A,形成环。
- 监视器发现环,按代价(回滚成本)选一方牺牲。
- 牺牲者事务回滚,另一方继续;应用需捕获 1205 并重试。
代码 / 命令
-- 启用死锁跟踪:扩展事件捕获 xml_deadlock_report(推荐)
-- 查看历史死锁报告(示例,事件会话名按实际配置)
SELECT XEventData.value('(event/@timestamp)[1]', 'datetime2(3)') AS ts,
XEventData.query('event/data/value/deadlock') AS deadlock_graph
FROM (SELECT CAST(target_data AS XML) AS x FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE s.name = N'system_health') AS t
CROSS APPLY x.nodes('//event[@name="xml_deadlock_report"]') AS X(XEventData);
常见误区
- 误区:死锁需要人工杀进程解除。正解:引擎自动检测并选牺牲者;人工杀只用于长时间阻塞。
- 误区:加更多锁能避免死锁。正解:死锁多由加锁顺序不一致导致,统一访问顺序更有效。
追问
- 问:牺牲者按什么标准选?答:通常选回滚日志量/成本较低的事务。
- 问:应用收到 1205 怎么处理?答:幂等重试该事务;业务层保证可安全重放。
05.4.1.3-010 阻塞链排查的完整命令顺序是什么?
频率 ★★★★☆ | 难度 进阶
一句话结论
先找等待会话与阻塞者,再看阻塞者语句、事务时长与持锁对象,最后判断是长事务、慢语句还是锁等待。
概念解释
- 排查顺序:等待任务 → 阻塞会话 → SQL 文本 → 事务/会话状态 → 锁资源 → 计划与耗时。
原理流程
sys.dm_os_waiting_tasks → 谁在等、被谁堵
sys.dm_exec_requests → 阻塞者当前语句、状态、等待
sys.dm_exec_sql_text → 具体 SQL
sys.dm_tran_locks → 持/等的锁资源
sys.dm_exec_sessions → 登录时间、事务空闲时长
代码 / 命令
-- 一站式阻塞概览(核心 DMV 关联)
SELECT w.session_id AS spid_waiting, w.blocking_session_id AS spid_blocking,
w.wait_type, w.wait_duration_ms,
block_sess.status, block_sess.open_transaction_count,
block_txt.text AS blocking_text
FROM sys.dm_os_waiting_tasks w
LEFT JOIN sys.dm_exec_sessions block_sess ON block_sess.session_id = w.blocking_session_id
LEFT JOIN sys.dm_exec_requests block_req ON block_req.session_id = w.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(block_req.sql_handle) block_txt
WHERE w.blocking_session_id IS NOT NULL;
常见误区
- 误区:只看当前执行语句就下结论。正解:阻塞者可能事务内还有更早的语句持锁,需查整个事务。
- 误区:等待时长越长越紧急。正解:先看阻塞源头是否活跃、事务是否长期挂起。
追问
- 问:怎么区分阻塞是锁等待还是其他资源等待?答:看
wait_type:LCK_M_*为锁等待,其余为 IO/内存/闩锁等。 - 问:空闲会话为什么能堵人?答:事务未提交,持有的锁一直不释放,会话本身可能在等应用交互。
05.4.1.3-011 更新锁 U 解决什么问题?
频率 ★★★★☆ | 难度 进阶
一句话结论
U 锁用于更新扫描阶段:同一时刻只允许一个事务持 U 锁推进到 X 锁,避免两个 S 事务都升级 X 时互相等待的转换死锁。
概念解释
- 无 U 锁时:两事务先各拿 S 锁读行,再都想升级 X,互相等对方释放 S,形成转换死锁。
- U 锁互斥:扫描阶段即持 U,其他事务不能再拿 U/X,串行化升级过程。
原理流程
UPDATE 扫描:申请 U 锁(U/U 不兼容)
定位目标行:U → X
非目标行:扫描结束释放 U
代码 / 命令
-- UPDLOCK 提示在读取时即申请 U 锁,常用于排队更新场景
BEGIN TRANSACTION;
SELECT Id FROM dbo.Stock WITH (UPDLOCK, HOLDLOCK) WHERE Sku = @sku;
-- 后续更新同一行
UPDATE dbo.Stock SET Qty = Qty - 1 WHERE Sku = @sku;
COMMIT;
常见误区
- 误区:U 锁和 X 锁一样阻止所有读。正解:U 主要约束更新方,与 S 的兼容性按矩阵核对;U 不等同于排他。
- 误区:普通 SELECT 会拿 U 锁。正解:普通读拿 S;更新语句扫描才用 U。
追问
- 问:U 锁什么时候升级为 X?答:确认要修改目标行时升级;未命中目标则释放。
- 问:
UPDLOCK适合超卖场景吗?答:配合事务与条件更新可串行化排队,详见 05.4.1.3-017。
05.4.1.3-012 意向锁是怎么工作的?
频率 ★★★☆☆ | 难度 进阶
一句话结论
意向锁在表级声明"本表下层有行/页锁",让表级锁请求无需逐行检查即可判断冲突,提高锁判断效率。
概念解释
- IS:表内有 S 锁;IU:表内有 U 锁;IX:表内有 X 锁。
- 意向锁之间兼容;表级 S/X 与意向锁按矩阵判断。
原理流程
- 申请行 X 锁前,先在表上拿 IX 锁。
- 另一事务申请表 S 锁时,看到 IX 即知道表内有写操作,避免逐行排查。
代码 / 命令
-- sys.dm_tran_locks 中 resource_type = OBJECT 的记录即表级意向锁
SELECT resource_associated_entity_id, request_mode, request_type, request_status
FROM sys.dm_tran_locks WHERE resource_type = 'OBJECT';
常见误区
- 误区:意向锁会直接阻塞其他事务读表。正解:意向锁是标记,真正冲突由行/页锁模式决定。
- 误区:意向锁是用户显式申请的。正解:引擎在申请细粒度锁时自动叠加。
追问
- 问:IX 和表 X 锁兼容吗?答:不兼容;表排他锁会等待表内所有细粒度锁释放。
- 问:意向锁粒度可以选吗?答:由引擎按实际细粒度锁自动获取。
05.4.1.3-013 乐观并发控制怎么实现?
频率 ★★★☆☆ | 难度 高级
一句话结论
用 rowversion(旧称 timestamp)字节数组或更新时间戳做版本标记,更新时比较版本,不一致说明已被他人修改,应用放弃或重试。
概念解释
- rowversion:数据库内单调递增的二进制版本号,行被更新时自动变化。
- 乐观思路:读时不加长锁,提交时校验版本;冲突少的场景减少阻塞。
原理流程
- 读取行时记下
rowversion值。 - 提交更新:
WHERE Id = @id AND RowVer = @read_version。 - 影响行数为 0 → 版本已变,重读后重试或提示冲突。
代码 / 命令
ALTER TABLE dbo.Product ADD RowVer ROWVERSION;
-- 提交时带版本校验
UPDATE dbo.Product
SET Name = @name
WHERE Id = @id AND RowVer = @read_version;
-- 检查 @@ROWCOUNT:0 表示版本冲突
常见误区
- 误区:
timestamp类型是时间日期。正解:SQL Server 的timestamp即rowversion,是版本字节数组,与日期无关。 - 误区:乐观锁能防止脏读。正解:它防丢失更新/覆盖提交,不替代隔离级别解决一致性问题。
追问
- 问:乐观锁和悲观锁怎么选?答:冲突少用乐观锁避免长阻塞;竞争激烈用悲观锁(
UPDLOCK)减少重试。 - 问:版本冲突后应用怎么处理?答:重新读取最新值、合并修改后重试,或提示用户刷新。
05.4.1.3-014 长事务为什么危险?
频率 ★★★★☆ | 难度 进阶
一句话结论
长事务长时间持有锁、阻止版本清理与日志截断,放大阻塞与 tempdb/日志膨胀;事务内应只做必要操作,不混入交互与慢速任务。
概念解释
- 持锁时间长:X 锁到提交才释放,期间阻塞读写。
- 阻止版本/purge 清理:旧版本被长事务引用,版本存储膨胀。
- 阻塞日志截断/DDL:日志链与架构变更被拖住。
原理流程
BEGIN TRANSACTION
├─ 执行更新(持 X 锁)
├─ 调用外部接口 / 等待用户输入 / 跑慢速查询 ← 事务敞开
└─ COMMIT ← 锁与版本直到此处才释放
代码 / 命令
-- 查找长时间运行的事务
SELECT s.session_id, s.login_name, s.host_name,
DATEDIFF(SECOND, t.transaction_begin_time, GETDATE()) AS trx_sec,
t.transaction_state_desc
FROM sys.dm_tran_active_transactions t
JOIN sys.dm_tran_session_transactions st ON st.transaction_id = t.transaction_id
JOIN sys.dm_exec_sessions s ON s.session_id = st.session_id
ORDER BY trx_sec DESC;
常见误区
- 误区:事务里加更多提示就能解决长事务阻塞。正解:根因是事务边界过大,应收窄事务、把外部调用移出事务。
- 误区:只读事务不占资源。正解:快照/RC 下读也会持有快照视图,阻止旧版本清理。
追问
- 问:事务内可以等待用户确认吗?答:不可以;先提交事务拿到结果,决策后再开新事务。
- 问:长事务会导致主从/副本延迟吗?答:持锁与版本放大间接影响回放与性能,需结合监控判断。
05.4.1.3-015 SQL Server 隔离级别与 MySQL、PostgreSQL 怎么对照?
频率 ★★★★☆ | 难度 高级
一句话结论
三库都定义了四级隔离现象,但实现路线不同:SQL Server 锁 + 可选行版本;MySQL InnoDB 用 MVCC + 锁;PG 用 MVCC(快照)+ 可选 SSI 防写偏序。
概念解释
| 维度 | SQL Server | MySQL InnoDB | PostgreSQL |
|---|---|---|---|
| 默认级别 | Read Committed | Repeatable Read | Read Committed |
| 读不阻塞写 | RCSI / 快照隔离 | MVCC 读版本 | MVCC 读版本 |
| 幻读防护 | Serializable 范围锁 | RR 下间隙锁 | Serializable + SSI |
| 行版本存放 | tempdb 版本存储 | undo log | 堆元组 + MVCC 版本 |
原理流程
- 对照主线:读一致性靠锁还是版本;版本方案都要处理旧版本回收(purge/版本清理/VACUUM)。
代码 / 命令
-- SQL Server 查看当前会话隔离级别
DBCC USEROPTIONS; -- 其中 isolation level 一行
常见误区
- 误区:默认 RR 的 MySQL 与 SQL Server RR 行为完全一致。正解:InnoDB 靠 MVCC + 间隙锁,SQL Server RR 靠持锁,幻读防护机制不同。
- 误区:PG 的 Serializable 就是严格两阶段锁。正解:PG 用 SSI(可序列化快照隔离)检测写偏序中止事务。
追问
- 问:SQL Server 哪个级别最接近 InnoDB RR 的读一致性体验?答:快照隔离(事务级一致快照),但写冲突处理不同。
- 问:三者旧版本回收机制分别是什么?答:SQL Server 版本存储清理、InnoDB purge undo、PG VACUUM。
05.4.1.3-016 锁升级是什么?阈值是多少?怎么干预?
频率 ★★★★☆ | 难度 高级
一句话结论
单语句在单对象上持有的细粒度锁超过阈值(默认约 5000 个行/页锁)时,引擎升级为表锁,减少锁内存开销但降低并发。
概念解释
- 升级动因:大量细粒度锁占用内存与管理成本,升级成一把表锁。
- 副作用:其他事务被整表阻塞,并发下降。
- 干预:可通过跟踪标记控制升级行为,或改写 SQL 缩小单次扫描范围。
原理流程
- 语句扫描大量行,行锁数量累积。
- 达到阈值后升级表级 X/S 锁,释放细粒度锁。
- 等待该表的其他会话被阻塞。
代码 / 命令
-- 查看锁数量与升级相关等待/计数
SELECT resource_type, COUNT(*) AS lock_count, request_mode
FROM sys.dm_tran_locks
GROUP BY resource_type, request_mode;
-- 跟踪标记 1224 禁用锁升级、1225 按开销决定(行为需按版本文档核对,待确认)
-- DBCC TRACEON (1224, -1);
常见误区
- 误区:锁升级一定是故障。正解:它是引擎的内存保护机制;问题在于大扫描引发的并发下降。
- 误区:升级后的表锁会随语句结束自动释放。正解:锁随事务提交/语句结束释放,需结合事务边界判断。
追问
- 问:怎么减少无谓升级?答:缩小语句影响范围、走索引避免全表扫描、拆分批量操作。
- 问:禁用锁升级有什么风险?答:细粒度锁内存占用上升,极端情况下锁资源耗尽。
05.4.1.3-017 库存扣减如何防超卖?常见并发问题有哪些?
频率 ★★★★★ | 难度 进阶
一句话结论
防超卖的核心是把"查库存再扣减"合并为带条件的原子更新,或用显式悲观锁排队;避免先读后写的丢失更新。
概念解释
- 丢失更新:两事务读到同一库存值,各自扣减后提交,后提交覆盖前者,扣减丢失。
- 条件更新:
UPDATE ... SET Qty = Qty - 1 WHERE Sku = ? AND Qty > 0,数据库保证原子。 - 悲观锁:
UPDLOCK读取排队,串行化竞争。
原理流程
反例:SELECT Qty → 应用判断 >0 → UPDATE Qty=Qty-1 (两步之间可被插入覆盖)
正例:UPDATE Stock SET Qty = Qty - 1 WHERE Sku=@s AND Qty > 0
检查 @@ROWCOUNT = 1 才视为扣减成功
代码 / 命令
-- 原子条件扣减
UPDATE dbo.Stock
SET Qty = Qty - 1
WHERE Sku = @sku AND Qty > 0;
IF @@ROWCOUNT = 0
THROW 50001, N'库存不足', 1;
-- 悲观锁排队写法
BEGIN TRANSACTION;
SELECT Qty FROM dbo.Stock WITH (UPDLOCK, HOLDLOCK) WHERE Sku = @sku;
UPDATE dbo.Stock SET Qty = Qty - 1 WHERE Sku = @sku;
COMMIT;
常见误区
- 误区:应用层加分布式锁就不需要数据库条件更新。正解:数据库条件更新是最后一道防线,避免锁服务故障导致超卖。
- 误区:
SELECT ... FOR UPDATE语法在 SQL Server 直接可用。正解:SQL Server 用UPDLOCK/HOLDLOCK提示,无 PG/MySQL 同款语法。
追问
- 问:高并发扣减还能怎么优化?答:队列串行化、分桶库存、Redis 预扣 + 异步落库,按一致性要求选型。
- 问:条件更新防超卖的前提是什么?答:库存列更新在单条语句内完成,不拆分到应用层两步。
05.4.1.3-018 常用锁与阻塞排查命令清单有哪些?
频率 ★★★☆☆ | 难度 进阶
一句话结论
排查围绕三类视图:sys.dm_tran_locks(锁)、sys.dm_os_waiting_tasks(等待)、sys.dm_exec_requests/sessions(会话与语句),配合锁超时设置。
概念解释
- 锁视图:谁持有/等待什么资源。
- 等待视图:等待类型、时长、阻塞链。
- 会话视图:语句文本、事务时长、状态。
原理流程
SET LOCK_TIMEOUT 控制等锁上限
sys.dm_tran_locks 锁资源与模式
sys.dm_os_waiting_tasks 等待链与 wait_type
sys.dm_exec_requests 当前请求与计划
sys.dm_exec_sql_text 语句文本
sys.dm_exec_sessions 会话与事务时长
代码 / 命令
-- 锁等待超时(毫秒):-1 无限等待
SET LOCK_TIMEOUT 5000;
-- 当前所有锁
SELECT session_id, resource_type, resource_description, request_mode, request_status
FROM sys.dm_tran_locks ORDER BY session_id;
-- 锁等待排行
SELECT wait_type, COUNT(*) AS waits, SUM(wait_duration_ms) AS total_wait_ms
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL
GROUP BY wait_type ORDER BY total_wait_ms DESC;
常见误区
- 误区:
LOCK_TIMEOUT能解决死锁。正解:它只限制等锁时长,超时后报错;死锁由引擎检测中止。 - 误区:排查只看锁视图就够了。正解:必须结合等待类型与语句文本,区分锁等待、IO 等待与闩锁等待。
追问
- 问:
LCK_M_X等待表示什么?答:会话在等待排他锁,通常被写事务阻塞。 - 问:怎么持续采集阻塞历史?答:用扩展事件会话捕获
blocked_process_report,落表后做趋势分析。
参考来源
- Microsoft Learn:事务锁定与行版本控制指南:https://learn.microsoft.com/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide
- Microsoft Learn:隔离级别:https://learn.microsoft.com/sql/t-sql/statements/set-transaction-isolation-level-transact-sql
- Microsoft Learn:行版本隔离级别:https://learn.microsoft.com/sql/relational-databases/performance/row-versioning-resources
- Microsoft Learn:死锁:https://learn.microsoft.com/sql/relational-databases/engine-database/deadlocks