SQLite 完全指南 / 09 - 事务

09 - 事务：BEGIN/COMMIT/ROLLBACK、WAL 模式与并发

9.1 事务基础

SQLite 支持 ACID 事务，保证数据操作的原子性、一致性、隔离性和持久性。

9.1.1 ACID 特性

特性	含义	SQLite 实现
Atomicity（原子性）	事务要么全部完成，要么全部回滚	Journal/WAL 机制
Consistency（一致性）	事务前后数据库保持一致	约束检查（CHECK/FOREIGN KEY）
Isolation（隔离性）	并发事务互不影响	文件锁机制
Durability（持久性）	提交后数据不丢失	fsync/flush 操作

9.1.2 基本事务语法

-- 开始事务（延迟模式）
BEGIN;
INSERT INTO accounts (name, balance) VALUES ('张三', 1000);
INSERT INTO accounts (name, balance) VALUES ('李四', 500);
COMMIT;

-- 回滚事务
BEGIN;
INSERT INTO accounts (name, balance) VALUES ('王五', 200);
-- 发现错误，回滚
ROLLBACK;

-- 保存点（嵌套事务）
BEGIN;
INSERT INTO accounts (name, balance) VALUES ('赵六', 300);
SAVEPOINT sp1;
UPDATE accounts SET balance = balance - 100 WHERE name = '赵六';
-- 只回滚到保存点
ROLLBACK TO sp1;
-- 赵六的 INSERT 仍在，UPDATE 被撤销
COMMIT;

9.1.3 事务类型

-- DEFERRED（默认）：首次读操作时获取共享锁
BEGIN DEFERRED;

-- IMMEDIATE：立即获取保留锁（阻止其他写操作）
BEGIN IMMEDIATE;

-- EXCLUSIVE：立即获取排他锁（阻止所有其他操作）
BEGIN EXCLUSIVE;

类型	获取锁时机	适用场景
`DEFERRED`	首次读/写时	只读事务或低冲突场景
`IMMEDIATE`	立即获取保留锁	写事务（推荐）
`EXCLUSIVE`	立即获取排他锁	高冲突场景

9.2 隐式事务

SQLite 中每条单独的 SQL 语句都在隐式事务中执行：

-- 以下每条语句都是一个隐式事务
INSERT INTO users (name) VALUES ('张三');  -- 自动 BEGIN + COMMIT
UPDATE users SET age = 25 WHERE id = 1;   -- 自动 BEGIN + COMMIT
DELETE FROM users WHERE id = 2;           -- 自动 BEGIN + COMMIT

9.2.1 显式事务 vs 隐式事务的性能

-- ❌ 隐式事务（每条语句一个事务，慢）
INSERT INTO big_table VALUES (1, 'a');
INSERT INTO big_table VALUES (2, 'b');
INSERT INTO big_table VALUES (3, 'c');
-- 3 次磁盘同步

-- ✅ 显式事务（一个事务，快 100 倍）
BEGIN;
INSERT INTO big_table VALUES (1, 'a');
INSERT INTO big_table VALUES (2, 'b');
INSERT INTO big_table VALUES (3, 'c');
COMMIT;
-- 1 次磁盘同步

9.3 自动提交模式

SQLite 默认开启自动提交——每条语句自动在事务中执行。

-- 查看自动提交状态（C API）
-- sqlite3_get_autocommit(db)

-- 在 CLI 中，自动提交默认开启
-- BEGIN 关闭自动提交，COMMIT/ROLLBACK 重新开启

-- 自动提交行为
CREATE TABLE test (id INTEGER PRIMARY KEY, val TEXT);
-- 隐式事务 1
INSERT INTO test VALUES (1, 'hello');
-- 隐式事务 2
INSERT INTO test VALUES (2, 'world');

9.4 保存点（SAVEPOINT）

保存点支持嵌套的事务回滚：

BEGIN;
INSERT INTO users (name, email) VALUES ('张三', '[email protected]');
SAVEPOINT user_created;

INSERT INTO orders (user_id, amount) VALUES (last_insert_rowid(), 100);
SAVEPOINT order_created;

INSERT INTO order_items (order_id, product_id, qty) VALUES (last_insert_rowid(), 1, 2);
-- 如果插入 order_items 失败
ROLLBACK TO order_created;  -- 只撤销 order_items 和 order
-- 用户数据仍然保留

RELEASE order_created;  -- 释放保存点（不回滚）
COMMIT;

9.4.1 保存点命名

SAVEPOINT sp1;
SAVEPOINT sp2;
SAVEPOINT sp3;

-- 回滚到 sp2（sp3 被销毁）
ROLLBACK TO sp2;

-- 释放 sp2（sp2 之前的修改保留）
RELEASE sp2;

-- 提交整个事务
COMMIT;

9.5 WAL 模式详解

WAL（Write-Ahead Logging）是 SQLite 的现代并发模型。

9.5.1 启用 WAL

-- 启用 WAL 模式（全局持久设置）
PRAGMA journal_mode = WAL;

-- 验证
PRAGMA journal_mode;  -- 返回 "wal"

9.5.2 WAL 模式的优势

特性	Rollback Journal	WAL
读写并发	❌ 读阻塞写	✅ 读写可并发
写入性能	一般	更好
文件数量	1 个 journal	.wal + .shm
崩溃恢复	回滚 journal	重放 WAL
网络文件系统	⚠️ 勉强	❌ 不支持

9.5.3 WAL Checkpoint

-- 查看 WAL 自动 checkpoint 阈值
PRAGMA wal_autocheckpoint;  -- 默认 1000 页

-- 设置自动 checkpoint 阈值
PRAGMA wal_autocheckpoint = 500;

-- 手动 checkpoint
PRAGMA wal_checkpoint(PASSIVE);  -- 非阻塞
PRAGMA wal_checkpoint(TRUNCATE); -- 阻塞，截断 WAL
PRAGMA wal_checkpoint(FULL);     -- 阻塞，确保全部合并
PRAGMA wal_checkpoint(RESTART);  -- FULL + 重启

模式	阻塞	说明
`PASSIVE`	否	尽可能多 checkpoint，不等待读者
`FULL`	是	等待所有读者完成后 checkpoint
`TRUNCATE`	是	FULL + 截断 WAL 文件为 0 字节
`RESTART`	是	FULL + 重启 WAL 文件

9.5.4 WAL 的文件

mydb.db       -- 主数据库文件
mydb.db-shm   -- 共享内存文件（WAL 索引）
mydb.db-wal   -- WAL 文件（写入缓冲）

⚠️ 不要手动删除 .wal 和 .shm 文件！ 这会导致数据丢失。

9.6 并发控制

9.6.1 文件锁状态机

UNLOCK → SHARED → RESERVED → PENDING → EXCLUSIVE → UNLOCK

9.6.2 并发场景

场景	Rollback Journal	WAL
多个读者	✅ 并发	✅ 并发
一个写者 + 多个读者	❌ 写阻塞读	✅ 并发
多个写者	❌ 只有一个写者	⚠️ 轮流写入
一个写者 + checkpoint	❌ 阻塞	⚠️ 取决于模式

9.6.3 处理锁等待

-- 设置忙等待超时（毫秒）
PRAGMA busy_timeout = 5000;  -- 等待 5 秒

-- 代码中检查返回值
-- 当数据库被锁定时，sqlite3_step() 返回 SQLITE_BUSY

# Python 示例：处理锁等待
import sqlite3

conn = sqlite3.connect('mydb.db', timeout=10)  # 10 秒超时
conn.execute('PRAGMA busy_timeout = 10000')

try:
    conn.execute('BEGIN IMMEDIATE')
    conn.execute('INSERT INTO users (name) VALUES (?)', ('张三',))
    conn.commit()
except sqlite3.OperationalError as e:
    if 'database is locked' in str(e):
        conn.rollback()
        print('数据库被锁定，稍后重试')
    else:
        raise

9.7 事务与外键

PRAGMA foreign_keys = ON;

-- 外键约束在事务提交时检查
BEGIN;
INSERT INTO orders (user_id, amount) VALUES (999, 100);
-- 如果 user_id=999 不存在，COMMIT 时会失败
COMMIT;
-- Error: FOREIGN KEY constraint failed

-- 设置外键检查时机
PRAGMA defer_foreign_keys = ON;  -- 推迟到事务结束时检查
PRAGMA defer_foreign_keys = OFF; -- 立即检查

9.7.1 ON DELETE / ON UPDATE

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    amount REAL,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE       -- 用户删除时级联删除订单
        ON UPDATE CASCADE       -- 用户 ID 更新时级联更新
);

-- 其他选项：
-- SET NULL    — 设置为 NULL
-- SET DEFAULT — 设置为默认值
-- RESTRICT    — 阻止操作（默认）
-- NO ACTION   — 类似 RESTRICT

9.8 事务最佳实践

9.8.1 使用 IMMEDIATE 事务

-- ❌ DEFERRED 事务可能导致死锁
BEGIN;  -- DEFERRED
SELECT * FROM accounts WHERE id = 1;  -- 获取 SHARED 锁
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- 尝试 RESERVED → 可能 SQLITE_BUSY

-- ✅ IMMEDIATE 事务更安全
BEGIN IMMEDIATE;  -- 立即获取 RESERVED 锁
SELECT * FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

9.8.2 保持事务短小

-- ❌ 长事务（阻塞其他写操作）
BEGIN;
-- ... 大量复杂操作 ...
-- ... 等待外部 API 响应 ...  ← 不要在事务中等待！
COMMIT;

-- ✅ 短事务
-- 先获取数据
data = SELECT * FROM users WHERE id = 1;
-- 在应用层处理
processed = process(data);
-- 快速事务
BEGIN;
UPDATE users SET ... WHERE id = 1;
COMMIT;

9.8.3 批量操作优化

-- ❌ 每条 INSERT 一个事务（慢）
INSERT INTO big_table VALUES (1, 'a');
INSERT INTO big_table VALUES (2, 'b');
-- ... 10000 条

-- ✅ 一个事务批量插入（快 100-1000 倍）
BEGIN;
INSERT INTO big_table VALUES (1, 'a');
INSERT INTO big_table VALUES (2, 'b');
-- ... 10000 条
COMMIT;

-- ✅ 使用 PREPARE + 循环（编程接口）
-- 准备一次，执行多次

⚠️ 注意事项

WAL 模式下不要放在网络文件系统上——NFS/SMB 上的 WAL 可能导致数据库损坏
长事务会阻止 WAL checkpoint——WAL 文件会持续增长
BEGIN EXCLUSIVE 会阻止所有其他操作——谨慎使用
ROLLBACK 只能回滚到事务开始或 SAVEPOINT——不能回滚事务中间的某一部分
外键约束默认不开启——每次连接都需要 PRAGMA foreign_keys = ON
PRAGMA journal_mode = WAL 是持久设置——存储在数据库文件中

💡 技巧

写操作使用 BEGIN IMMEDIATE——避免 SQLITE_BUSY 错误
批量操作一定要使用事务——性能差距巨大
使用 WAL 模式——读写并发性能更好
使用 SAVEPOINT 实现部分回滚——比嵌套事务更灵活
设置合理的 busy_timeout——避免锁冲突时立即失败

📌 业务场景

场景一：转账系统

BEGIN IMMEDIATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

场景二：批量数据导入

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;

BEGIN;
INSERT INTO data SELECT * FROM staging_table;
DELETE FROM staging_table;
COMMIT;

🔗 扩展阅读

📖 下一章：10 - PRAGMA 配置 —— journal_mode、synchronous、cache_size