罗罗编程学习记录
本页内容 展开查看

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 版本存储。

原理流程

  1. 更新行前,旧版本复制到版本存储。
  2. 读请求沿版本链找到符合隔离级别的版本。
  3. 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_* 表示锁等待。

原理流程

  1. sys.dm_os_waiting_tasks 筛 blocking_session_id IS NOT NULL。
  2. 关联阻塞会话的 request 与 SQL 文本。
  3. 查看源头会话状态、事务时长与持锁对象。

代码 / 命令

-- 阻塞链概览
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 错误。
  • 死锁图:包含进程、资源、等待边与持有边,可从扩展事件/系统视图获取。

原理流程

  1. T1 持 A 等 B,T2 持 B 等 A,形成环。
  2. 监视器发现环,按代价(回滚成本)选一方牺牲。
  3. 牺牲者事务回滚,另一方继续;应用需捕获 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:数据库内单调递增的二进制版本号,行被更新时自动变化。
  • 乐观思路:读时不加长锁,提交时校验版本;冲突少的场景减少阻塞。

原理流程

  1. 读取行时记下 rowversion 值。
  2. 提交更新:WHERE Id = @id AND RowVer = @read_version。
  3. 影响行数为 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 缩小单次扫描范围。

原理流程

  1. 语句扫描大量行,行锁数量累积。
  2. 达到阈值后升级表级 X/S 锁,释放细粒度锁。
  3. 等待该表的其他会话被阻塞。

代码 / 命令

-- 查看锁数量与升级相关等待/计数
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,落表后做趋势分析。

参考来源