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

强曰为道

09 - 事务

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

9.1 事务基础

9.1.1 ACID 特性

9.1.2 基本事务语法

9.1.3 事务类型

9.2 隐式事务

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

9.3 自动提交模式

9.4 保存点（SAVEPOINT）

9.4.1 保存点命名

9.5 WAL 模式详解

9.5.1 启用 WAL

9.5.2 WAL 模式的优势

9.5.3 WAL Checkpoint

9.5.4 WAL 的文件

9.6 并发控制

9.6.1 文件锁状态机

9.6.2 并发场景

9.6.3 处理锁等待

9.7 事务与外键

9.7.1 ON DELETE / ON UPDATE

9.8 事务最佳实践

9.8.1 使用 IMMEDIATE 事务

9.8.2 保持事务短小

9.8.3 批量操作优化

⚠️ 注意事项

💡 技巧

📌 业务场景

场景一：转账系统

场景二：批量数据导入

🔗 扩展阅读