SQL Server 事务日志与 Write-Ahead Logging
你知道为什么 SQL Server 能保证「事务提交后数据不丢失」吗？
很多人会说：「因为有 redo log 嘛。」
但 SQL Server 的日志机制和 MySQL 的 redo log 有何不同？什么是 Write-Ahead Logging？为什么日志文件满了系统会卡死？
今天，我们来深入理解 SQL Server 的事务日志体系。
WAL 机制：日志优先原则
Write-Ahead Logging 的定义
Write-Ahead Logging（WAL） = 在数据页写入磁盘之前，对应的日志记录必须先写入磁盘。
这是数据库保证事务 ACID 中「持久性（Durability）」的核心机制。
SQL Server 的 WAL 实现
┌─────────────────────────────────────────────────────────────┐
│ SQL Server WAL 执行流程 │
│ │
│ 事务 UPDATE Orders SET status='shipped' WHERE order_id=1001 │
│ │
│ 步骤 1：修改数据页 │
│ ┌────────────────────────────────────────────────────────┐ │
│ │ 缓冲池中的数据页被修改 │ │
│ │ 状态：pending → shipped │ │
│ │ 标记为脏页（Dirty Page） │ │
│ └────────────────────────────────────────────────────────┘ │
│ ↓ │
│ 步骤 2：写入日志（WAL 核心！） │
│ ┌────────────────────────────────────────────────────────┐ │
│ │ 日志记录写入日志缓冲区： │ │
│ │ - Before Image：status = 'pending' │ │
│ │ - After Image：status = 'shipped' │ │
│ │ - 日志缓冲区刷写到磁盘（LDF 文件） │ │
│ │ ✓ 日志落盘完成 │ │
│ └────────────────────────────────────────────────────────┘ │
│ ↓ │
│ 步骤 3：提交事务 │
│ ┌────────────────────────────────────────────────────────┐ │
│ │ COMMIT 日志写入并落盘 │ │
│ │ ✓ COMMIT 落盘 = 事务完成 │ │
│ └────────────────────────────────────────────────────────┘ │
│ ↓ │
│ 步骤 4：异步刷写数据页 │
│ ┌────────────────────────────────────────────────────────┐ │
│ │ 后台检查点（Checkpoint）异步将脏页写入磁盘 │ │
│ │ 数据页落盘可以延迟，不影响事务持久性 │ │
│ └────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘为什么 WAL 能保证持久性？
场景：事务提交后立即断电
- COMMIT 日志已落盘 ✓
- 数据页还在内存，没落盘 ✓
- 数据库重启 → 从日志恢复
- 发现 LSN 有 COMMIT 记录 → 重做（REDO）该事务
场景：事务执行中断电
- 只有部分日志，没有 COMMIT
- 数据库重启 → 从日志恢复
- 发现 LSN 是未提交事务 → 回滚（UNDO）该事务
结论：只要日志在，数据就在。
日志序列号（LSN）
LSN 的结构
LSN（Log Sequence Number）是日志记录的「身份证」，6 字节整数，单调递增。
sql
-- 查看当前 LSN
SELECT
current_lsn,
first_lsn,
last_lsn
FROM sys.dm_tran_database_transactions
WHERE database_id = DB_ID();LSN 的作用：
- 定位：通过 LSN 可以精确定位到某条日志记录
- 连续性：日志在逻辑上连续，用于恢复
- 顺序：保证 WAL 的「日志优先」原则
LSN 与恢复
日志序列：
LSN 0001: BEGIN TRAN TX_1001
LSN 0002: UPDATE Orders SET status='shipped' WHERE order_id=1001
LSN 0003: COMMIT TRAN TX_1001
LSN 0004: BEGIN TRAN TX_1002
LSN 0005: UPDATE Products SET stock=stock-1 WHERE product_id=200
（断电！）
恢复过程：
1. 检查点 LSN = 0001（假设）
2. 从 0001 开始 REDO：找到所有已提交事务，重做
- TX_1001 已提交 → 重做 UPDATE
- TX_1002 未提交 → 回滚 UPDATE
3. 恢复完成，数据库一致检查点（Checkpoint）
检查点的作用
检查点 = 将脏页刷写到磁盘的标记点，用于缩短崩溃恢复时间。
┌─────────────────────────────────────────────────────────────┐
│ 检查点机制 │
│ │
│ 磁盘日志文件： │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ LSN 001: BEGIN TRAN │ │
│ │ LSN 002: UPDATE ... │ │
│ │ LSN 003: COMMIT │ │
│ │ LSN 004: UPDATE ... │ │
│ │ LSN 005: UPDATE ... │ │
│ │ ↑ │ │
│ │ 检查点 LSN = 005 │ │
│ │ 表示：LSN 005 之前的脏页 已写入磁盘 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 恢复时，只需从检查点 LSN 开始恢复 │
│ 而不需要从日志开头开始 │
└─────────────────────────────────────────────────────────────┘检查点触发时机
| 触发条件 | 说明 |
|---|---|
| 间隔时间 | 默认约 1 分钟 |
| 日志空间阈值 | 日志 70% 满时 |
| 手动触发 | 执行 CHECKPOINT |
| 备份前 | 数据库备份前自动触发 |
| 实例关闭 | 正常关闭时触发 |
sql
-- 手动触发检查点
CHECKPOINT;
-- 查看检查点信息
SELECT
database_name,
last_checkpoint_recovery_lsn,
last_checkpoint_lsn,
checkpoint_lsn
FROM sys.dm_db_recovery_status;恢复模式与日志管理
三种恢复模式
| 恢复模式 | 日志备份 | 适用场景 | 日志截断 |
|---|---|---|---|
| FULL | 需要 | 重要业务，需要 PITR | 备份后 |
| BULK_LOGGED | 需要 | 大批量导入 | 备份后 |
| SIMPLE | 不需要 | 开发/测试 | 检查点时 |
SIMPLE 恢复模式
日志序列：LSN 001 → 002 → 003 → 004 → 005 → 006
↑
检查点
│
日志可截断- 检查点后，已提交事务的日志可以被覆盖
- 日志不会无限增长
- 代价：只能恢复到上次备份，无法 Point-in-Time 恢复
FULL 恢复模式
日志序列：LSN 001 → 002 → 003 → 004 → 005 → 006 → 007 → 008 → 009
│
├─ 日志备份 1
│
└── 010 → 011 → 012 → 013
│
├─ 日志备份 2
│
└── 日志继续增长...关键点：
- 日志必须通过备份才能截断
- 不备份日志 → 日志无限增长 → 磁盘满
- 可以恢复到任意时间点（PITR）
日志备份与截断
sql
-- 完整备份
BACKUP DATABASE SalesDB TO DISK = 'D:\Backup\SalesDB_Full.bak'
WITH COMPRESSION, CHECKSUM;
-- 日志备份（FULL 模式下必须）
BACKUP LOG SalesDB TO DISK = 'D:\Backup\SalesDB_Log.trn'
WITH COMPRESSION, CHECKSUM;
-- 恢复到某个时间点
RESTORE LOG SalesDB FROM DISK = 'D:\Backup\SalesDB_Log.trn'
WITH STOPAT = '2024-03-15 14:30:00';日志文件管理
日志文件结构
SQL Server 日志文件由多个 VLF（Virtual Log File） 组成：
┌─────────────────────────────────────────────────────────────┐
│ LDF 日志文件 │
│ │
│ ┌─────────┬─────────┬─────────┬─────────┬─────────┐ │
│ │ VLF 1 │ VLF 2 │ VLF 3 │ VLF 4 │ VLF 5 │ │
│ │ (活动) │ (活动) │ (活动) │ (可用) │ (可用) │ │
│ └─────────┴─────────┴─────────┴─────────┴─────────┘ │
│ ↑ ↑ ↑ │
│ 活动日志 活动日志 活动日志 │
│ └───────────┴───────────┘ │
│ VLF 1-3：不能截断 │
│ VLF 4-5：可以重用 │
└─────────────────────────────────────────────────────────────┘查看 VLF 状态
sql
-- 查看 VLF 信息
DBCC LOGINFO('SalesDB');
-- Status = 0：可重用
-- Status = 2：活动（已使用且不能截断）
-- 查看日志使用情况
DBCC SQLPERF(LOGSPACE);
-- Database Name Log Size (MB) Log Space Used (%) Status
-- SalesDB 500.00 45.23 0日志增长问题
常见原因：
- 长时间运行的事务（日志不能截断）
- 日志未备份（FULL 模式）
- 复制未同步
- 镜像配置问题
解决方案：
sql
-- 1. 检查长时间运行的事务
SELECT
session_id,
transaction_id,
transaction_begin_time,
database_id,
database_transaction_log_record_count
FROM sys.dm_tran_database_transactions
WHERE database_id = DB_ID('SalesDB')
ORDER BY transaction_begin_time;
-- 2. 备份日志（FULL 模式）
BACKUP LOG SalesDB TO DISK = 'D:\Backup\SalesDB_Log.trn' WITH TRUNCATE_ONLY;
-- TRUNCATE_ONLY 只截断，不备份
-- 3. 收缩日志文件
DBCC SHRINKFILE('SalesDB_Log', 100); -- 收缩到 100MB日志与崩溃恢复
崩溃恢复过程
数据库重启 → 崩溃恢复
阶段 1：分析阶段
┌─────────────────────────────────────────────────────────────┐
│ 读取最后一个检查点的信息 │
│ 确定需要恢复的日志范围 │
└─────────────────────────────────────────────────────────────┘
↓
阶段 2：重做阶段（REDO）
┌─────────────────────────────────────────────────────────────┐
│ 从检查点开始，扫描所有日志记录 │
│ 对每个已提交事务，执行重做 │
│ 将数据页恢复到最新状态 │
└─────────────────────────────────────────────────────────────┘
↓
阶段 3：回滚阶段（UNDO）
┌─────────────────────────────────────────────────────────────┐
│ 对未提交事务，执行回滚 │
│ 使用 Before Image 恢复到修改前状态 │
└─────────────────────────────────────────────────────────────┘
↓
✓
数据库恢复完成，在线快速恢复（Fast Recovery）
SQL Server 2005+ 支持快速恢复：
传统恢复：
分析 → 重做 → 回滚 → 在线
↓
需要等待所有回滚完成
快速恢复：
分析 → 重做 → 在线 → 回滚（后台继续）
↓
用户可以立即连接访问
↓
未提交事务在后台继续回滚与 MySQL 的对比
| 特性 | SQL Server | MySQL (InnoDB) |
|---|---|---|
| 日志类型 | 统一日志（LDF） | redo log + undo log |
| WAL 实现 | 日志先写 | 日志先写 |
| 崩溃恢复 | 分析→重做→回滚 | 启动时恢复 |
| 恢复模式 | FULL/SIMPLE/BULK_LOGGED | innodb_flush_log_at_trx_commit |
| 日志备份 | 支持 | 需要配合 binlog |
面试追问方向
- 什么是 Write-Ahead Logging？SQL Server 如何实现 WAL？
- 检查点和崩溃恢复有什么关系？
- FULL 恢复模式下，日志无限增长的原因是什么？
- 什么是 VLF？如何优化 VLF 数量？
- 快速恢复（Fast Recovery）是什么？有什么好处？
- SQL Server 的日志机制和 MySQL 的 redo log 有什么不同？
下一步
理解了 WAL 和事务日志，我们来看 SQL Server 隔离级别：脏读、不可重复读、幻读、快照隔离，学习并发控制的核心机制。