系统与基础设施sqlite.org
如何破坏一个 SQLite 数据库文件
How to Corrupt a SQLite Database File
2013 — 2022，它一次次回到首页
黑点是上首页（≥ 50 分）的那次投稿，空心点是没冲上去的投稿，点越大分数越高。
导读
这是 SQLite 官方文档，系统列举 SQLite 数据库文件可能被损坏的各种方式：磁盘/文件系统不遵守同步语义、并发访问与锁问题、同一应用链接多个 SQLite 副本、事务进行中做文件级备份等。它解释损坏机理并给出规避原则，是理解 SQLite 可靠性边界的重要资料。
为什么经久不衰：SQLite 无处不在却常被当作普通文件处理，这篇官方清单把罕见但致命的损坏路径讲透，今天仍是使用 SQLite 的必读避坑指南。
HN 评论精选
真实运维案例：不遵守 sync 的消费级硬盘可能导致 SQLite 损坏。
2007 年我在一家数字标牌公司工作，全美部署了约 8000 套系统，软件严重依赖 SQLite。后来开始出现随机数据库损坏；排查发现同一 SKU 的机器内部硬件会变，尤其是硬盘——所有损坏数据库的机器都用某个品牌（WD）硬盘，没出问题的用另一个品牌（Seagate）。我们没再深究，只是锁定硬件配置并把故障机器硬盘换成“好”品牌。我猜原因是“不遵守 sync 请求的磁盘驱动器”。当时用的是廉价消费级设备，部署量又大，完全解决的成本（停摆、派技术员、隔夜寄送、排查时间）恐怕不低。
cpuguy83查看英文原文
In another life (2007) I worked for a company that does digital signage. We had about 8000 systems deployed across the US. The software on them relied heavily on SQLite. At some point we started seeing random corrupted SQLite databases. Trying to figure out what was causing this we realized our pc manufacturer was using the same sku but hardware inside could vary... and in particular the hard drive. Every system with a corrupt DB had a particular brand hard drive (WD), the ones that didn't fail was another (Seagate). We never really spent much more time on this other than to lock down the hardware config and replace all the hard drives in the failed systems with the "good" brand. Super interesting to see this. I'm assuming it was: Disk drives that do not honor sync requests We were of course using consumer grade equipment, because the company was pretty cheap, but also we had so many to deploy. I wonder how much this cost the company to fully resolve. Between systems not playing, having to send technicians, shipping systems overnight, time troubleshooting, etc.
实践建议：用 serialized 模式、单进程单连接并在应用层控制事务。
我发现实践中让 SQLite 文件损坏非常困难。Windows 上最糟只见过满屏“Database File is Locked”，重写访问逻辑后解决。大量试验后，我认为 SQLite 的顺路径是：1）使用编译或配置为 serialized threading mode 的发行版（多数情况默认）；2）单进程单连接访问数据库文件，该连接可在线程间共享并按需加锁（得益于第 1 点）；3）事务在应用代码里加锁（即在 SQLiteConnection 实例上），而不是依赖数据库；这对带主键生成的插入尤其必要，也适用于多步操作不希望两个线程交错更新时（如一个账户扣款另一个账户入账）。目前这是最性能简单的方式。“每次操作一个连接”对 SQLite 不理想，因为真有文件句柄要反复打开关闭；那更适合托管数据库。
bob1029查看英文原文
I have found that corruption of SQLite files in practice is a very difficult thing to achieve. On Windows, the worst I've ever seen is a wall of "Database File is Locked" which was resolved with a rewrite of the DB access logic. After much experimentation, I have found that the happy path for SQLite is as follows: 1. Use a SQLite distribution that is compiled or otherwise configured for serialized threading mode (this is the default in most cases). 2. Use a single connection from a single process to access the SQLite database file. This connection can be safely shared between multiple threads with optional locking as required (due to the guarantees offered by #1 above). 3. Transactions are handled with locking in the application code (i.e. on the SQLiteConnection instance), not in the database. This is predominantly required for inserts with PK generation, but also applicable in cases where you don't want 2 different threads to interleave updates during a certain multi-step operation (e.g. debiting one account and crediting another). So far, we have found this to be the most performant and simple way to get business data in and out of a SQLite database file. The "one connection per operation" approach is NOT ideal for SQLite because there is an actual database file that needs a handle created/closed each time. One connection per makes sense when you are using a hosted database designed for this access model.
质疑：在事务进行中做文件级备份可能产生损坏备份，而普通桌面软件正处在这种风险中。
> 后台自动备份的系统可能在事务中途复制 SQLite 数据库文件，备份可能包含部分旧、部分新内容，因而损坏。 我很惊讶！软件运行、打开 SQLite 且可能写入时做备份真的不完全安全吗？对有懂行管理员的大数据库没问题，但 SQLite 常被非技术终端用户使用且他们不知道有 SQLite 文件，比如 Firefox 和 Chrome。这意味着普通用户边浏览网页边跑备份，可能倒霉得到坏备份，恢复时损坏数据库。我理解对吗？若是，许多包含 SQLite 的项目做法略不安全。（-journal/-wal 也许能防，但文档只说它们防事务失败，不是同一回事。）
adrianmonk查看英文原文
> Systems that run automatic backups in the background might try to make a backup copy of an SQLite database file while it is in the middle of a transaction. The backup copy then might contain some old and some new content, and thus be corrupt. I am surprised by this! Is it really not entirely safe to take a backup while some software is running, has a SQLite database open, and might be writing to it? That limitation is fine for a big database that has someone knowledgeable administering it who will take care to follow any required backup procedures. But SQLite is often included in software that nontechnical end users will use without even knowing that there's a SQLite file. For example, Firefox and Google Chrome use it. This seems to mean that if a regular end user is browsing the web while backups run on their computer, it's possible they could be unlucky and get a bad backup that would corrupt the database if they had to restore it. Am I interpreting that right? If so, it suggests that many/most software projects that include SQLite are doing it in a slightly unsafe way. (It seems possible that the -journal and -wal files could protect against that, but this SQLite documentation only mentions that they protect you when transactions fail, which is a different case.)
提出文档中“事务中途备份会损坏”与 SQLite 回滚机制看似矛盾，备份最多落后一个事务。
第 1.2 点（事务中途备份文件）不是和上面说 SQLite 能抵抗断电、回滚未完成事务相矛盾吗？你最后只会得到一个落后一个事务的数据库，对备份来说本来就没问题。
andai查看英文原文
Doesn't point 1.2 (backing up the file in the middle of a transaction) contradict the part above about how SQLite is resistant to power failures and simply rolls back the incomplete transaction? You'd just end up with a db outdated by 1 transaction, which is fine for a backup anyway.
历次讨论
同类还值得读
TTY 揭秘（2008）
The TTY demystified (2008)
经典文章深入剖析了 Unix/Linux 中 TTY 子系统的工作原理，包括线路规程、终端驱动、信号与控制字符等机制。
系统与基础设施上首页 11 次 · 2009–2025linusakesson.net编辑精选为什么 GNU grep 这么快
Why GNU grep is Fast
FreeBSD 邮件列表上一篇广为流传的帖子，解释 GNU grep 为什么快：核心在于 Boyer-Moore 字符串搜索算法配合精心优化的内部循环和 I/O，让程序在搜索时“…
系统与基础设施上首页 9 次 · 2010–2022lists.freebsd.orgXplain：为普通人讲解 X11
Xplain – Explaining X11 for the rest of us
《Xplain》是作者（HN 用户 Jasper_）写的一系列 X11 长文，用他在浏览器中重建的 X11 绘图模型做交互演示，讲解 X11 的绘图模型与窗口系统设计。
系统与基础设施上首页 6 次 · 2013–2020magcius.github.io编辑精选旋风教程：为 Linux 创建极小 ELF 可执行文件（1999）
A Whirlwind Tutorial on Creating Really Teensy ELF Executables for Linux (1999)
breadbox 写于 1999 年的经典教程，主题是如何为 Linux 手工构造并裁剪出体积极小的 ELF 可执行文件。
系统与基础设施上首页 6 次 · 2007–2025muppetlabs.comPostgreSQL 内部原理
The internals of PostgreSQL
《The Internals of PostgreSQL》是一本免费在线书籍，系统讲解 PostgreSQL 的内部实现，包括查询处理、并发控制、WAL、缓冲区管理等核心机制。
系统与基础设施上首页 5 次 · 2017–2025interdb.jp编辑精选