Jersey's Blog

外观

第七章 事务与数据安全 ​

7.1 事务的基本用法 ​

什么是事务 ​

事务（Transaction）就是把多条 SQL 语句打包成一个原子操作——要么全部成功，要么全部回滚，不会出现"执行了一半"的状态。

典型场景：转账

  用户 A 余额 - 100 元
  用户 B 余额 + 100 元

  如果第一条执行了，第二条失败了 → 钱凭空消失了！
  事务保证：两条语句要么一起成功，要么一起撤销。

基本语法 ​

sql
-- 开始事务
BEGIN TRANSACTION;  -- 也可以简写为 BEGIN;

-- 执行一组操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 一切正常，提交
COMMIT;
sql
-- 出现异常，回滚
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 发现余额不足或出错了...
ROLLBACK;  -- 撤销所有操作，数据恢复原样

SQLite 的自动事务 ​

SQLite 有一个重要特性：每条独立的 SQL 语句自动包裹在一个事务中。

你写的：   INSERT INTO users VALUES (1, '张三', 25);

SQLite 实际执行的：
  BEGIN;
  INSERT INTO users VALUES (1, '张三', 25);
  COMMIT;

每条语句都是原子的——即使你不显式写 BEGIN/COMMIT。

事务的性能优势 ​

批量操作时，手动包裹事务可以大幅提升性能：

sql
-- ❌ 慢：1000 条 INSERT = 1000 次事务（每次都要 fsync 磁盘）
INSERT INTO logs VALUES (1, 'event_a');
INSERT INTO logs VALUES (2, 'event_b');
... -- 重复 1000 次

-- ✅ 快：1000 条 INSERT 包在一个事务里（只 fsync 一次）
BEGIN;
INSERT INTO logs VALUES (1, 'event_a');
INSERT INTO logs VALUES (2, 'event_b');
... -- 重复 1000 次
COMMIT;
性能对比（1000 条 INSERT）：

  无显式事务：每条 INSERT 都是独立事务
  → 1000 次 fsync → 约 10~30 秒（机械硬盘）

  有显式事务：所有 INSERT 打包成一个事务
  → 1 次 fsync → 约 0.05 秒

  差距可达 100~500 倍！

实用建议：批量写入数据时，一定要用 BEGIN / COMMIT 包裹。这是 SQLite 性能优化中最简单也最有效的一招。

SAVEPOINT —— 事务中的存档点 ​

SQLite 支持在事务内部设置"存档点"，可以只回滚到某个存档点，而不是回滚整个事务：

sql
BEGIN;

INSERT INTO users VALUES (1, '张三', 25);

SAVEPOINT sp1;  -- 设置存档点

INSERT INTO users VALUES (2, '李四', 30);
-- 发现这条有问题，只回滚到 sp1
ROLLBACK TO sp1;

-- 继续操作
INSERT INTO users VALUES (3, '王五', 28);

COMMIT;
-- 结果：只有 id=1 和 id=3 被插入，id=2 被回滚了

7.2 ACID 在 SQLite 中的体现 ​

ACID 是数据库事务的四大特性。来看 SQLite 是如何实现每一个特性的：

Atomicity（原子性）—— 要么全做，要么全不做 ​

sql
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 如果这里断电了...
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
断电时的处理：

  ┌────────────────────────────────────────────┐
  │  如果 COMMIT 之前发生故障：                  │
  │  → 下次打开数据库时，自动回滚未完成的事务    │
  │  → 数据恢复到 BEGIN 之前的状态               │
  │  → 不会出现 "A 扣了钱但 B 没收到" 的情况    │
  └────────────────────────────────────────────┘

SQLite 通过日志文件（回滚日志或 WAL）来保证原子性。

Consistency（一致性）—— 数据始终满足约束 ​

sql
-- 有 CHECK 约束：balance >= 0
CREATE TABLE accounts (
    id      INTEGER PRIMARY KEY,
    balance REAL CHECK (balance >= 0)
);

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 如果余额变成负数 → CHECK 约束失败 → 语句报错
-- 整个事务可以 ROLLBACK，数据保持一致

Isolation（隔离性）—— 并发事务互不干扰 ​

SQLite 的隔离级别：

日志模式隔离级别行为
DELETE（默认）SERIALIZABLE最严格，事务串行执行
WALSERIALIZABLE（读写之间）读写可以并发，但写还是串行

SQLite 的隔离性比大多数数据库更简单粗暴——因为它是数据库级别的锁，而不是行级锁。这意味着同一时间只有一个写事务。

Durability（持久性）—— 提交后数据不丢失 ​

COMMIT 执行时：

  1. 数据写入日志文件（回滚日志或 WAL）
  2. 调用 fsync() 确保数据落盘到磁盘
  3. 返回成功

  只要 COMMIT 返回了，数据就安全了，
  即使紧接着断电、操作系统崩溃也不会丢。

PRAGMA synchronous —— 持久性 vs 性能的权衡 ​

sql
PRAGMA synchronous = FULL;    -- 最安全，每次 COMMIT 都 fsync（默认）
PRAGMA synchronous = NORMAL;  -- 平衡模式（WAL 模式下推荐）
PRAGMA synchronous = OFF;     -- 最快，但断电可能丢数据
选项安全性速度适用场景
FULL⭐⭐⭐较慢金融、订单等关键数据
NORMAL⭐⭐较快大多数应用（WAL 模式下推荐）
OFF⭐最快可重建的缓存数据、测试环境

实际建议：使用 WAL 模式 + synchronous = NORMAL 是性能和安全的最佳平衡点。

7.3 日志模式：DELETE vs WAL ​

SQLite 通过日志文件来保证事务的原子性和持久性。有两种日志模式，理解它们的区别对于性能优化至关重要。

DELETE 模式（默认）—— 回滚日志 ​

DELETE 模式的写入流程：

  ① 写之前：先把原始数据复制到 .db-journal 文件（回滚日志）
  ② 修改主数据库文件
  ③ COMMIT 成功后，删除 .db-journal 文件

  ┌──────────┐     ┌──────────────────┐
  │ main.db  │ ──→ │ main.db-journal  │  ① 备份原始数据
  └──────────┘     └──────────────────┘
       │
       ↓ ② 直接修改
  ┌──────────┐
  │ main.db  │  （新数据写在这里）
  └──────────┘
       │
       ↓ ③ COMMIT 后
  ┌──────────────────┐
  │ main.db-journal  │  ← 被删除
  └──────────────────┘

  如果 ② 之后断电：
  → 下次打开时发现 .db-journal 存在
  → 自动用日志恢复数据（回滚）

WAL 模式 —— 写前日志（Write-Ahead Logging） ​

WAL 模式的写入流程：

  ① 写操作追加到 .db-wal 文件（不修改主数据库）
  ② 读操作先查 WAL，再查主数据库
  ③ 定期把 WAL 的内容合并回主数据库（checkpoint）

  ┌──────────┐     ┌──────────────┐
  │ main.db  │     │ main.db-wal  │  ① 新数据写到这里
  │ (不动)    │     │ (追加写入)    │
  └──────────┘     └──────────────┘
                          │
                          ↓ ③ checkpoint 时
  ┌──────────┐     ┌──────────────┐
  │ main.db  │ ←── │ main.db-wal  │  合并回主文件
  │ (更新了)  │     │ (清空)       │
  └──────────┘     └──────────────┘

切换到 WAL 模式 ​

sql
-- 开启 WAL 模式（只需执行一次，效果永久保存在数据库文件中）
PRAGMA journal_mode = WAL;

-- 查看当前日志模式
PRAGMA journal_mode;  -- 返回 'wal' 或 'delete'

-- 切回 DELETE 模式
PRAGMA journal_mode = DELETE;

DELETE vs WAL 对比 ​

对比维度DELETE 模式WAL 模式
写性能较慢（要先备份再修改）较快（追加写入，顺序 I/O）
读写并发读写互斥读写可以并发 ✅
读读并发可以并发可以并发
写写并发不可以不可以（同一时间只有一个写者）
文件数量.db + .db-journal（临时）.db + .db-wal + .db-shm
数据库大小正常WAL 文件可能较大（checkpoint 前）
适合场景简单应用、很少并发Web 应用、多线程、读多写少

WAL 模式的推荐配置 ​

sql
-- 推荐的 WAL 模式配置组合
PRAGMA journal_mode = WAL;           -- 开启 WAL
PRAGMA synchronous = NORMAL;         -- 平衡安全与性能
PRAGMA wal_autocheckpoint = 1000;    -- 每 1000 页自动 checkpoint
PRAGMA busy_timeout = 5000;          -- 锁等待超时 5 秒

一句话总结：大多数应用都应该用 WAL 模式。它的性能更好，而且支持读写并发。

7.4 并发控制与锁机制 ​

SQLite 是进程/线程安全的，但它的并发模型和 MySQL / PostgreSQL 有本质区别——它用的是数据库级别的锁，而不是行级锁。

SQLite 的 5 种锁状态 ​

锁的升级路径：

  UNLOCKED → SHARED → RESERVED → PENDING → EXCLUSIVE

  ┌───────────┐
  │ UNLOCKED  │  没有任何操作
  └─────┬─────┘
        ↓ 开始读
  ┌───────────┐
  │  SHARED   │  正在读取，允许其他连接同时读
  └─────┬─────┘
        ↓ 准备写
  ┌───────────┐
  │ RESERVED  │  准备写入，但还没真正写。其他连接仍可读
  └─────┬─────┘
        ↓ 要 COMMIT 了
  ┌───────────┐
  │ PENDING   │  等待所有 SHARED 锁释放
  └─────┬─────┘
        ↓ 所有读者离开
  ┌───────────┐
  │ EXCLUSIVE │  独占！正在写入数据库文件
  └───────────┘
锁状态含义允许其他连接
UNLOCKED无操作任意
SHARED正在读读 ✅ 写 ❌
RESERVED准备写读 ✅ 新写 ❌
PENDING等待写新读 ❌ 新写 ❌
EXCLUSIVE正在写读 ❌ 写 ❌

DELETE 模式下的锁行为 ​

连接 A（写）  连接 B（读）

  BEGIN;
  UPDATE ...    SELECT ...     ← B 可以读
  (RESERVED)    (SHARED)

  COMMIT;       SELECT ...     ← 此时 B 被阻塞！
  (EXCLUSIVE)   (等待中...)

  完成!          SELECT ...     ← A 完成后 B 才能继续
  (UNLOCKED)    (SHARED)

WAL 模式下的锁行为 ​

连接 A（写）  连接 B（读）

  BEGIN;
  UPDATE ...    SELECT ...     ← B 可以读（读主文件）
  (写WAL)       (读主文件)

  COMMIT;       SELECT ...     ← B 仍然可以读！
  (写WAL)       (读主文件)         不被阻塞 ✅

  写和读互不干扰，各走各的路径：
  写 → 写入 WAL 文件
  读 → 从主文件 + WAL 中获取最新数据

常见错误与解决方案 ​

错误 1：database is locked

原因：另一个连接持有锁，当前连接超时。
解决方案：

  1. 设置 busy_timeout（等待一段时间再报错）
     PRAGMA busy_timeout = 5000;  -- 等待最多 5 秒

  2. 切换到 WAL 模式（减少锁冲突）
     PRAGMA journal_mode = WAL;

  3. 缩短事务持续时间（尽快 COMMIT）

  4. 检查是否有未关闭的连接或未提交的事务

错误 2：database is locked（多进程写入）

sql
-- 在代码中使用 busy handler / 重试机制
-- Python 示例
import sqlite3
conn = sqlite3.connect('my.db', timeout=10)  -- 10 秒超时
conn.execute('PRAGMA journal_mode=WAL')
conn.execute('PRAGMA busy_timeout=5000')

错误 3：database disk image is malformed

原因：数据库文件损坏（通常是非正常关闭或文件系统问题）
解决方案：

  1. 检查完整性
     PRAGMA integrity_check;

  2. 尝试恢复
     sqlite3 corrupt.db ".recover" | sqlite3 recovered.db

并发最佳实践 ​

┌─────────────────────────────────────────────────┐
│           SQLite 并发使用指南                     │
│                                                 │
│  1. 始终使用 WAL 模式                            │
│  2. 始终设置 busy_timeout                        │
│  3. 写操作尽快完成（短事务）                      │
│  4. 一个进程内共享同一个连接（或使用连接池）       │
│  5. 不要跨网络访问 SQLite 文件（NFS 不可靠！）    │
│  6. 不要同时用多个进程大量写入                    │
│  7. 如果需要高并发写入 → 考虑 PostgreSQL          │
└─────────────────────────────────────────────────┘

本章小结 ​

知识点要点
事务语法BEGIN / COMMIT / ROLLBACK；SAVEPOINT 做局部回滚
自动事务每条独立 SQL 自动包裹在事务中
性能技巧批量操作用显式事务包裹，速度提升 100~500 倍
ACIDSQLite 完整支持 ACID 四特性
日志模式DELETE（默认）vs WAL；推荐 WAL
WAL 配置journal_mode=WAL + synchronous=NORMAL + busy_timeout=5000
锁机制数据库级别锁；DELETE 模式读写互斥；WAL 模式读写可并发
常见错误database is locked → 设 busy_timeout + 用 WAL + 短事务

下一章预告：探索 SQLite 的特色功能——JSON 操作、FTS5 全文搜索、窗口函数、生成列和 STRICT 表。

坚持是一种品格

Copyright © 2024-2026 Jersey