有一次半夜处理线上故障，某项目的数据同步任务卡死，日志里全是“database is locked”，连接池被打满，整个服务像被按了暂停键。事后把代码翻出来，发现问题出在一个批量导入的循环里：每个事务执行失败后没有及时回滚，锁一直被占着，后面的读写全堵住了。这种场景在SQLite开发里太常见了，根子往往是事务用得不严谨。

这篇就把SQLite3事务与数据库编程从头到尾捋一遍：事务的底层机制、锁与日志模式、并发场景下的行为、工程里怎么配置和封装。适合正在用SQLite写业务代码的同学，也适合已经在生产环境被各种锁问题折磨过的人。我会把每个配置和写法背后的Why讲清楚，不只是一堆参数的堆砌。

1. 先把事务机制吃透：SQLite如何保证数据不丢、不乱

1.1 ACID四个特性，实际运行中每天都在发生

不用把ACID当成教科书理论，它其实就是数据安全的底线。原子性的意思是，一批操作要么全部生效，要么等于没发生。一致性指的是数据永远符合约束条件，比如唯一索引、外键这些规则不会被半路绕过。隔离性保证多个事务并发执行时，效果和串行执行一样，不会互相看出写到一半的数据。持久性则是说，一旦事务提交成功，就算断电也不会丢。

用个生活场景类比：你有一本记账本，每次修改一页，先把原页内容抄到旁边保留，再划掉旧页写上新内容。万一写错了，还能靠备份恢复；别人看账本时，要么看到旧页，要么看到全部改完的新页，绝不会看到涂改到一半的状态。SQLite通过日志文件干的就是这件事：修改前把旧数据先写到日志里，崩溃后自动回滚未完成的事务，保证系统回到一致性状态。

实际开发中，我经常看到有人把一个事务里塞进几千条SQL，想靠它保证一整晚跑批的数据不出错。但事务越大，持有的锁时间越长，回滚代价也越高。所以事务设计的第一条经验就是：不是所有操作都应该包在同一个事务里，要看业务能否接受“部分成功”。比如批量处理一千条记录，把每100条作为一个事务块提交，比整批一个事务要稳妥得多。

1.2 五种锁状态，理解后才能看懂“database is locked”

SQLite的锁模型比大型数据库简单直接，一共就五个状态：UNLOCKED、SHARED、RESERVED、PENDING、EXCLUSIVE。

一个普通的写事务，流程是这样的：事务开始时先拿SHARED锁，这个锁允许多个连接同时持有，大家都能读，但不能写。第一次要修改数据时，SHARED锁升级为RESERVED锁，此时其他连接还能继续读，但新的SHARED锁不再授予，其他写请求也会被挡在门外。等到提交时，如果没有别的读事务还握着SHARED锁，就直接升级为EXCLUSIVE锁；如果还有读事务没结束，就先进PENDING状态等着，等读者全部离开后再升EXCLUSIVE。

这五种状态的竞争关系，用一张表看更清楚：

从这张表能推导出很多实际问题的答案。比如为什么读多时写会被卡住？因为只要有一个长查询一直握着SHARED锁，写事务就只能在PENDING状态等待，直到所有读者离开。反过来，为什么多个写事务并发时经常报锁错误？因为RESERVED锁一次只允许一个连接持有，第二个写连接想拿RESERVED时，发现锁已经被别人抢了。

1.3 journal_mode从DELETE到WAL，两种日志模式的取舍

SQLite的崩溃恢复依赖日志系统，有两种主流模式：传统的回滚日志模式（DELETE，也是默认模式）和预写日志模式（WAL）。

DELETE模式下，写者必须持有EXCLUSIVE锁才能修改数据库文件，这意味着写事务执行期间，整个数据库对其他连接是只读的。适合单连接、写入频率很低的小工具或嵌入式场景，逻辑简单，没有checkpoint的负担。

WAL模式则完全不同。修改不直接作用在主库文件上，而是追加写到独立的wal日志文件里。读者继续读主库的旧快照，写者往wal文件尾部追加新记录，写事务不再需要EXCLUSIVE锁，只需要RESERVED锁就够了。这实现了读与写互不阻塞，写也不阻塞读，只有在多个写事务竞争时才出现等待。

WAL模式不是没有代价的。wal文件会持续增长，必须定期执行checkpoint操作把日志内容合并回主库；在只读文件系统或网络文件系统上，WAL会出现各种权限和并发问题；另外，长事务或长查询会阻碍checkpoint，导致wal无限膨胀。

我的实践经验是这样的：本地应用、嵌入式设备、桌面工具，追求并发下读写稳定，优先选WAL。但如果你的应用只有一个数据库连接，读写量都不大，DELETE模式反而更简单直接，不用关心checkpoint和日志大小。

1.4 synchronous、busy_timeout、cache_size三个关键参数

这三个PRAGMA是配置SQLite行为的基础，但很多人只知道设置却说不清原理。

synchronous控制数据库写入到磁盘的时机，有FULL、NORMAL、OFF三档。FULL模式下，每次事务提交都强制把数据刷到物理磁盘，最安全但最慢，尤其在写入密集的场景下性能损失明显。NORMAL模式在WAL模式下非常常用，事务提交时不会强制刷盘，而是交给操作系统自己调度，性能大幅提升，代价是极端断电情况下可能丢失最近几笔已提交事务，但数据库文件结构不会损坏。OFF模式基本就是拿数据安全换性能，除非你很清楚自己在干什么，否则不推荐生产环境使用。

busy_timeout是拿不到锁时等待的毫秒数。SQLite默认是0，意思是拿不到锁立即报错，不做任何等待。这个默认值在实际工程里坑过很多人，我通常至少设置3000毫秒，再配合应用层重试，能显著减少偶发的锁错误。它不会改变锁的分配规则，只是把“立即失败”变成“先等等再失败”。

cache_size决定SQLite内存缓存页面的数量，旧版本的默认值2000页换算下来连2MB都不到，读多场景下性能很差。一般调成大概几十MB的缓存，对查询效率的提升立竿见影。

2. 事务编程入门必修课：BEGIN、提交、回滚与重试

2.1 三种BEGIN的区别，别用默认的DEFERRED裸奔

SQLite提供了三条BEGIN语句：BEGIN DEFERRED、BEGIN IMMEDIATE、BEGIN EXCLUSIVE。它们的区别不在性能，而在拿锁的时机。

BEGIN DEFERRED是默认行为，执行后不立即拿锁，直到事务里第一次真正执行写语句，才尝试升级到RESERVED锁。这个模式的问题在于，如果两个连接都先用DEFERRED开始事务，读取了同一行，再同时尝试更新那一行，两边都会申请RESERVED锁，而锁已经被对方拿着，就会产生经典的升级死锁，SQLite直接返回SQLITE_BUSY，不会自动等待对方释放。

BEGIN IMMEDIATE在事务开始时就立即获取RESERVED锁，提前预约写能力。这样做的结果是，第一个进入事务的连接会阻塞其他连接进入写事务，但不会阻塞读操作，而且后续执行更新时不会再发生从读锁升级到写锁的撞车。我在工程里几乎总是用IMMEDIATE，它能规避掉大部分偶发的锁竞争问题。

BEGIN EXCLUSIVE则更严格，事务一开始就拿EXCLUSIVE锁，任何其他连接都不能读也不能写。适合少数必须独占数据库的场景，比如批量重建索引或整体迁移数据，但日常业务里没必要这么重。

2.2 事务绑定连接，别跨连接开事务

SQLite的事务是绑定在连接实例上的，这一点和很多数据库的会话概念相似。一个事务的BEGIN、执行、COMMIT必须发生在同一个连接上。如果在连接池里取连接A开始事务，执行到一半换成连接B提交，连接B根本没进入事务状态，SQLite会直接报错；而连接A的事务仍然挂着，锁没释放，连接池里的连接也会被慢慢耗尽。

连接池在SQLite场景下的正确用法是：每条业务操作开头从池里取一条连接，在整个事务执行期间独占它，事务结束后归还。不要让一个事务分散到多条连接上执行，也不要在事务中间让连接被其他操作借走。原则很简单：一个事务，一个连接，一气呵成。

2.3 回滚不是可选项，异常时必须处理

从代码层面看，回滚就是在异常处理里加一行，但很多人就是漏了。如果事务执行中抛了异常而你没有回滚，SQLite会保持这个未完成事务的所有锁和日志，后续查询可能一直读到旧数据，其他写操作也会被堵住。更隐蔽的是，有些语言驱动在异常后会自动回滚，有些不会，行为不一致很容易埋雷。

正确的模板长这样：

import sqlite3 conn = sqlite3.connect('app.db', timeout=5) try: conn.execute("BEGIN IMMEDIATE") # 执行业务需要的多条SQL conn.execute("INSERT INTO users(name) VALUES(?)", ("alice",)) conn.execute("UPDATE counters SET val = val + 1 WHERE id = 1") conn.commit() except Exception as e: conn.rollback() raise

注意，commit本身也可能失败，比如磁盘满、IO错误。所以异常处理里只有一条rollback路径并不够，严谨的写法是无论commit还是中间语句失败，都先尝试回滚，再抛出异常。不要只记录日志就继续跑，否则连接池很快就会堆满残留事务。

2.4 SQLITE_BUSY重试策略，指数退避比立即重试可靠

设置了busy_timeout之后，仍然可能遇到极端情况下的锁竞争。此时应用层需要自己决定是否重试。最容易犯的错误是catch到锁异常就立即重试，这在高并发下会造成“惊群效应”，所有连接同时重试，反而把锁竞争推得更高。

比较靠谱的做法是指数退避，每次重试之间等待时间逐步拉长。参考实现：

import time import sqlite3 MAX_RETRY = 3 for attempt in range(MAX_RETRY): try: # 执行你的写事务 result = do_write_transaction() break except sqlite3.OperationalError as e: if 'locked' in str(e) and attempt < MAX_RETRY - 1: time.sleep(0.05 * (2 ** attempt)) continue raise

这里0.05秒、0.1秒、0.2秒的序列就是简单的退避策略。另一个要点是，重试的单位应该是整个事务，不是某一条INSERT。如果你在循环里逐条重试INSERT，有可能出现部分写入成功、部分失败，最后数据状态半新半旧，比锁错误更麻烦。

2.5 批量导入时怎么控制事务块大小

批量写数据时，事务设计直接影响性能。每条记录一个独立事务，要不断刷日志和落盘，写一万条能慢到离谱；但如果一万条作为一个事务，中途失败整个回滚，代价又太高。我一般控制每500到1000条做一个事务块，或者按数据量来看，大约每2到5MB提交一次。这样每个事务块独立成败，失败时不会拖累前面的数据，恢复成本也可控。

这个“事务块”设计在工程里还有个额外好处：可以打印进度。每提交一个块，更新一次处理进度，跑批任务在长时间运行时也更容易观察状态。

3. 并发场景进阶：搞定“database is locked”的常见诱因

3.1 隔离级别和可串行化快照，为什么读不到新数据

SQLite的默认隔离级别是可串行化，但在WAL模式下，一个连接第一次读取时就会拿到一个一致的数据库快照，之后直到事务结束，所有查询看到的都是这个快照里的数据。也就是说，如果你的连接开了一个读事务，别人把数据改了并提交了，你在这个事务里继续查询，是看不到新数据的。这不是bug，是隔离性正常的表现。

如果业务希望每次查询都看到最新已提交的数据，最简单的办法是缩短事务。不要在一个长时间运行的查询或循环里保持事务打开，读操作尽量独立成单个事务，读完立即结束。

3.2 长查询与checkpoint的拉锯战

WAL模式下，长查询会阻止checkpoint把wal文件合并回主库，因为checkpoint不能跳过正在读取旧快照的连接。结果就是wal文件不断变大，占磁盘空间，还拖慢后续启动时间。

应对策略分几种。一是把统计报表类长查询放到单独的只读连接上，并在主连接上定期执行PRAGMA wal_checkpoint(PASSIVE)或TRUNCATE，把checkpoint放在业务低峰期。二是直接控制业务查询时长，比如通过分页、分批扫描，避免一条SQL跑几十秒。

3.3 多写连接的锁升级顺序，预防死锁的实战经验

两个以上连接同时写时，锁竞争不可避免，但很多竞争其实是代码结构造成的。最常见的问题是：所有连接都默认用DEFERRED事务，读一行再改一行，于是出现锁升级撞车。

我的建议有三条：

• 所有写事务统一用BEGIN IMMEDIATE，提前拿RESERVED锁。
• 不要一个事务跨多个物理数据库文件（比如ATTACH其他库），除非你严格控制锁获取顺序。
• 事务内不要做外部网络请求或长时间IO，锁持有时间越短，打架概率越低。

3.4 多进程共享同一数据库文件时的特别注意

SQLite本身支持多进程同时访问同一个数据库文件，但并发场景跨进程时最脆弱。不同语言、不同驱动默认的打开方式可能不一致，有的打开了WAL，有的还是DELETE模式，锁语义完全不同，表现出来就是“时好时坏”的诡异问题。所以第一步是统一配置：所有进程、所有连接都设置同样的journal_mode、synchronous、busy_timeout。

其次，多进程场景下更依赖busy_timeout。因为进程间通信成本高，没人帮你排队，一旦撞锁只能靠SQLite内部的等待机制。建议把busy_timeout调到3000毫秒以上，同时应用层加指数退避重试。

另一个容易踩的坑是文件权限。多进程访问数据库文件时，必须确保所有进程对数据库文件和所在目录都有读写权限，否则权限不足导致的报错很容易被误判成锁问题。

3.5 只读连接，数据查询和报表分析的正确打开方式

某些场景只需要查询，不需要写入，比如数据分析、报表生成。此时可以打开只读连接，或者直接把数据库文件设为只读权限。只读连接不会申请任何写锁，也不参与锁竞争，查询性能往往比普通连接更好。

只读连接也不是完全没有限制，它不能开启写事务，任何尝试写入的操作都会报错“attempt to write a readonly database”。所以当你的代码里有多态业务逻辑，需要根据配置决定是否写库时，只读连接需要单独处理，不能直接复用同一套写代码。

4. 性能与安全防护：事务之外其他必须关注的工程细节

4.1 用SQLite自带工具定位性能瓶颈

遇到性能问题，先别急着怀疑事务。SQLite提供几个常用工具：PRAGMA quick_check检查数据库完整性，VACUUM整理页碎片，EXPLAIN QUERY PLAN查看查询是否走了索引，sqlite3_analyzer分析页分布和碎片程度。如果事务层面的锁竞争频繁，最直接的信号是大量SQLITE_BUSY，这时应该优先排查长事务和锁持有时间，而不是调数据库参数。

4.2 刷盘频率、FAT文件系统与安全性的取舍

在嵌入式设备或某些网络文件系统上，频繁刷盘的代价非常高。如果遇到SQLite写性能差，先检查是不是默认FULL模式造成每次提交都落盘。在WAL模式下把synchronous降到NORMAL，写入性能能提升数倍甚至一个量级，代价是极端场景下可能丢最后几笔已提交事务，但不会损坏数据库。

这里有个反直觉的点：FULL模式并不等于“文件不会损坏”，它只是保证已提交数据落盘。如果数据库文件本身因为断电等原因物理损坏了，FULL也无能为力。所以备份仍然是必需品。在高频写入场景中，我会额外做一个定期备份API调用，把数据库文件复制到另一块位置，防止单点损坏。

4.3 主键与rowid的关系，为什么INTEGER PRIMARY KEY快得多

SQLite每行数据默认有一个隐藏的rowid列，用来物理定位记录。如果你建表时指定INTEGER PRIMARY KEY，这个列实际上是rowid的别名，读写和更新是最快的路径。但如果用TEXT或复合主键，SQLite会额外创建一个普通索引来维护唯一性，写入和更新都要多维护索引，性能自然差一些。

如果你的应用主键是UUID这类文本，可以考虑在业务表中额外加一个INTEGER PRIMARY KEY列作为真正的物理主键，把文本主键改为普通唯一索引。这样既不影响业务逻辑，写入性能也有提升。

4.4 foreign_keys默认关闭，千万不能依赖直觉

很多开发者第一次接触SQLite都会踩这个坑：外键类型和约束都建好了，删除父表记录时子表记录竟然还在，没报任何错。原因是SQLite的外键约束默认是关闭的，每个连接需要自己执行PRAGMA foreign_keys = ON。

这个设置是连接级的，连接池里的每个连接都要设置。如果忘了，等于在数据库层放弃了外键约束，数据一致性压力全落到业务代码上。建议在连接初始化统一的工具类中设置，避免散落在各个业务代码里。

4.5 定期完整性检查，防患于未然

SQLite的PRAGMA quick_check是轻量级的完整性校验，比VACUUM快很多。我通常在应用启动时跑一次，或者备份之前跑一次。如果返回了错误，说明数据库文件有损坏风险，这时要停止写入操作，先评估备份恢复方案，不要继续盲目写入。

5. 一个完整工程示范：Java环境下的SQLite事务管理工具封装

5.1 工程目标与整体结构

用一个Java示例来展示如何把前面这些原则落地。这个工具类负责三件事：统一初始化连接参数、提供事务模板方法、处理SQLITE_BUSY重试。项目里只需要一个类，就能让所有业务代码在统一规范下使用事务。

5.2 连接初始化与四行PRAGMA

import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class SqliteManager { public static Connection open(String path) throws SQLException { Connection conn = DriverManager.getConnection("jdbc:sqlite:" + path); try (Statement st = conn.createStatement()) { st.execute("PRAGMA journal_mode = WAL"); st.execute("PRAGMA synchronous = NORMAL"); st.execute("PRAGMA busy_timeout = 3000"); st.execute("PRAGMA foreign_keys = ON"); } return conn; } }

注意几个细节：journal_mode是数据库级别的设置，修改后永久保存在数据库文件里，但第一次打开时设置一次更稳妥；synchronous、busy_timeout、foreign_keys都是连接级或临时级设置，每条新连接都必须重新执行。所以该方法必须作为统一入口，而不是每个地方自己建连接。

5.3 事务模板方法，强制try-rollback-commit结构

@FunctionalInterface public interface SqlFunction<T> { T apply(Connection conn) throws SQLException; } public <T> T inTransaction(Connection conn, SqlFunction<T> fn) throws SQLException { conn.setAutoCommit(false); try (Statement st = conn.createStatement()) { st.execute("BEGIN IMMEDIATE"); T result = fn.apply(conn); conn.commit(); return result; } catch (Exception e) { conn.rollback(); throw new SQLException("transaction failed", e); } }

这个模板的核心价值是：由框架保证回滚，业务代码只需要专注于SQL逻辑。如果业务在lambda里抛了异常，rollback一定执行；如果忘记commit，连接最终也会处于不可用状态，但因为统一入口的存在，排查成本更低。

5.4 重试机制与指数退避

private static final int MAX_RETRY = 3; public <T> T executeWithRetry(Connection conn, SqlFunction<T> fn) throws SQLException { for (int attempt = 0; attempt < MAX_RETRY; attempt++) { try { return inTransaction(conn, fn); } catch (SQLException e) { if ("database is locked".equalsIgnoreCase(e.getMessage()) && attempt < MAX_RETRY - 1) { sleepQuietly(50L << attempt); continue; } throw e; } } throw new SQLException("exhausted retry attempts"); } private void sleepQuietly(long millis) { try { Thread.sleep(millis); } catch (InterruptedException ignored) { Thread.currentThread().interrupt(); } }

重试的粒度是整段事务，不是单条SQL。这里的50L << attempt产生50、100、200毫秒的退避序列，足够应对大多数偶发锁竞争。

5.5 批量插入的块提交写法

public void batchInsert(List<Record> records, int batchSize) throws SQLException { try (Connection conn = open("app.db")) { for (int i = 0; i < records.size(); i += batchSize) { List<Record> chunk = records.subList(i, Math.min(i + batchSize, records.size())); executeWithRetry(conn, c -> { try (var ps = c.prepareStatement( "INSERT INTO metrics(name, value) VALUES(?, ?)")) { for (Record r : chunk) { ps.setString(1, r.name); ps.setLong(2, r.value); ps.addBatch(); } ps.executeBatch(); } return null; }); } } }

这样每个chunk都是一个独立事务，失败时只回滚当前chunk，前面已提交的数据不受影响。batchSize设为1000时，写一万条记录大约触发10个事务，性能远好于每条一个事务，回滚风险也可控。

5.6 用两个连接验证锁行为

Connection c1 = open("app.db"); Connection c2 = open("app.db"); c1.setAutoCommit(false); try (Statement st = c1.createStatement()) { st.execute("BEGIN IMMEDIATE"); } c2.setAutoCommit(false); try (Statement st = c2.createStatement()) { st.execute("BEGIN IMMEDIATE"); // 期望在busy_timeout后抛SQLITE_BUSY } catch (SQLException e) { System.out.println("expected lock: " + e.getMessage()); } c1.rollback(); c1.close(); c2.close();

这个验证能直观看到：第一个连接持有IMMEDIATE锁时，第二个连接试图进入写事务会等待3秒后失败。把busy_timeout设成0再跑一次，体验一下同步失败的区别，会对锁模型有更深的体感。

6. 常见问题速查与避坑实录

这些坑我基本都踩过。早期做数据迁移脚本时，因为没设foreign_keys，跑了半夜把主表删了才发现子表数据还趴在库里，最后只能靠备份恢复。后来把连接初始化统一封装，才真正解决了这类问题。

7. 我的一点体会

用SQLite这些年，最大的感触是：它上手简单，但要用好并不简单。不少项目初期只当它是一个文件存储工具，代码里随意开事务、随意拿连接，直到并发上来才被各种锁问题打醒。其实SQLite单机能力相当强，只要掌握事务的边界、锁的规律、日志模式的取舍，很多看似棘手的性能问题都能迎刃而解。

如果你刚开始在项目里使用SQLite，建议第一天就建立一个统一的连接管理工具类，把几个PRAGMA参数固化下来，所有业务代码从这唯一的入口拿连接。这个习惯能帮你省掉后面大量排查锁问题的时间。数据库层面能保持简单，业务代码也就会跟着简单。