Share Notes

chundev

View the Project on GitHub latteouka/share-notes

SQLite WAL Snapshot 同步（一）：機制與一致性設計

Created: 2026-08-24

日期：2026-08-24

環境：macOS、APFS、Node.js、SQLite WAL、加密 SQLite CLI

TL;DR

正在被桌面應用程式使用的 SQLite WAL 資料庫，不能只複製主 .db 檔；已提交但尚未 checkpoint 的資料可能只存在 -wal。若因隔離要求不能用 SQLite 開啟來源，可在每次查詢前偵測 DB/WAL 變動，以新目錄複製 DB＋WAL，核對複製前後 stat signature、來源／副本 SHA-256，再用副本實際開庫驗證；任何一步失敗就 fail closed。

這套方法在一次實測中，把約 70 MiB 主 DB 與約 2 MiB WAL 同步成第二份版本化 snapshot，查詢立即讀到新增紀錄，來源主 DB hash 維持不變。它是「來源不可被 SQLite 開啟」時的工程折衷，不是 SQLite Online Backup API 的形式化替代品。

目錄

  1. 問題背景
  2. WAL 為什麼改變備份模型
  3. 需求與非目標
  4. 替代方案比較
  5. 一致性模型
  6. Query-time refresh
  7. Candidate、clone、hash 與開庫驗證

背景

許多桌面應用程式把本機狀態存進 SQLite。

典型需求不是「備份一次」而已，而是：

這類情境常出現在：

表面上看，只要定期 cp database.db snapshot.db。

但 WAL mode 改變了「資料庫狀態」的定義。

主 DB 不再等於完整的最新狀態。

問題與症狀

症狀一：主 DB hash 沒變，但應用程式明明新增了資料

WAL mode 的 writer 先把變更追加到 database.db-wal。

在 checkpoint 以前：

這不是寫入延遲，也不是 cache bug。

它是 WAL 的正常行為。

症狀二：複製後的 DB 可以開，但缺少最近幾筆

可能原因包括：

症狀三：偶發 database disk image is malformed

若 DB 與 journal/WAL 配對錯誤，副本可能包含：

SQLite 官方明確警告：transaction 進行中直接複製檔案，可能混合新舊內容；存在 journal 或 WAL 時，主檔與 journal 必須一起處理。

症狀四：唯讀開庫仍碰到 -shm

WAL reader 不只讀主 DB 與 WAL。

它通常還需要 wal-index。

wal-index 通常映射到 database.db-shm。

新版 SQLite 可以在特定條件下讀取 read-only WAL database，但這不代表「開 read-only connection 絕不影響任何來源輔助檔」。

若隔離契約要求來源目錄完全不能被 SQLite runtime 接觸，最乾淨的界線是：

只在檔案層讀取來源，所有 SQLite open、WAL recovery 與 wal-index 建立都發生在副本目錄。

機制拆解：WAL 的三個檔案

主資料庫

database.db 保存已 checkpoint 的 page image。

它是基底。

它不保證包含最新 commit。

Write-Ahead Log

database.db-wal 包含：

SQLite reader 開始 transaction 時會決定一個 end mark。

同一個 transaction 只讀到 end mark 以前的最後有效 frame。

因此 writer 可以繼續 append，而既有 reader 仍看到一致視圖。

Shared-memory wal-index

database.db-shm 是 wal-index 的常見實作。

它的目的不是保存不可重建的業務資料。

它加速「某個 page 的最新 frame 在 WAL 哪裡」的查找。

SQLite 原始碼說明了幾個重要特性：

因此，建立隔離副本時通常應：

這個選擇降低了把來源 process-specific wal-index 狀態帶進副本的風險。

常見誤解

誤解 實際情況 為什麼會搞混
主 DB mtime 沒變代表沒有新資料 新 commit 可能只在 WAL rollback journal 心智模型仍停留在主檔
-wal 只是暫存，可以不備份 WAL 是 persistent database state 的一部分 檔名看起來像 cache
-shm 和 WAL 一樣不可缺 SHM 是可重建 wal-index 三個檔案總是一起出現
readonly 表示零副作用 WAL reader 仍可能需要 SHM 或目錄權限 把 SQL read-only 與 filesystem zero-write 混為一談
copy-on-write clone 是一致性 snapshot clone 只降低複製成本，不凍結跨檔案時間點 filesystem clone 與 database transaction snapshot 名稱相似
複製完成就代表可用 仍需 hash、open、schema 與業務查詢驗證 把 I/O success 當成 database correctness
同步失敗時回舊資料比較友善 對 freshness-sensitive agent，silent stale 是假成功 availability 優先的直覺不適用所有產品
mtime 相同足以證明來源沒變 timestamp 解析度、檔案替換與 checkpoint 都可能誤導 stat 很快，容易被當成完整驗證

需求與非目標

必要需求

  1. 不用 SQLite 開啟來源 DB。
  2. 不寫入來源 DB、WAL、SHM 或來源目錄。
  3. 查詢前可偵測新 commit。
  4. DB/WAL 變動時才建立新副本。
  5. 候選副本通過驗證前不可成為 active。
  6. 同步失敗不可默默回傳 stale data。
  7. 同一來源版本不可重複製造 snapshot。
  8. key、token、DB 與 snapshot 不可進 Git。
  9. API 不接受任意 SQL。
  10. 舊 snapshot 不自動刪除。

非目標

替代方案比較

方案 一致性 是否開啟來源 DB 對來源影響 適用情境
SQLite Online Backup API 高 是 使用正常 SQLite locking 能合法建立來源 connection
VACUUM INTO 高 是 需要執行 SQL、建立一致副本 可控制來源 SQLite connection
sqlite3_rsync 高 是／依工具 官方 live-copy protocol 遠端或標準 SQLite
停止應用後複製 DB＋WAL 高 否 有停機 可接受短暫停止
Filesystem snapshot 取決於 FS 跨檔一致性 否 很低 有 volume snapshot 能力
DB＋WAL stable-copy protocol 工程驗證型 否 只有檔案 read 禁止 SQLite 接觸來源
只複製主 DB 低 否 低 僅限確認沒有 WAL/journal

首選順序

如果能開啟來源 DB：

  1. 優先 SQLite Online Backup API。
  2. 或使用 VACUUM INTO。
  3. 遠端同步考慮 sqlite3_rsync。

如果不能開啟來源 DB：

  1. 最好先停止 writer，再複製 DB＋WAL。
  2. 若不能停機，評估 filesystem snapshot。
  3. 若以上皆不可行，才使用本文的 stable-copy protocol。

何時不用本文方法

一致性模型

本文方法不是一次原子的跨檔案 copy。

它採用「複製後證明來源在觀察窗內沒有可見變化」的策略。

Source signature

對主 DB 與 WAL 取得：

概念表示：

db(dev, inode, size, mtimeNs) | wal(dev, inode, size, mtimeNs)

signature 用來做快速 dirty check。

它不是資料完整性的最終證據。

Copy window

一次候選同步包含：

S0 = stat(DB, WAL)
copy(DB)
copy(WAL)
S1 = stat(DB, WAL)
hash(source DB, copied DB)
hash(source WAL, copied WAL)
S2 = stat(DB, WAL)
validate(copied DB + copied WAL)

接受條件：

S0 == S1 == S2
sourceDbHash == copiedDbHash
sourceWalHash == copiedWalHash
validation == success

為什麼需要三次 stat

S0 建立複製前基準。

S1 攔截 copy 過程中的變動。

S2 攔截 hash 過程中的變動。

如果只做 S0 == S1：

為什麼還要 SHA-256

stat 相同不表示 bytes 必然相同。

SHA-256 用來檢查：

SHA-256 在這裡不是用來抵抗攻擊者碰撞。

它的角色是強內容等同性檢查。

NIST FIPS 180-4 定義 SHA-256 的 digest 行為。

仍然不是形式化 atomic snapshot

這個 protocol 仍有理論限制：

因此對一般本機單 writer 桌面應用屬於務實方案。

對金融帳本、醫療寫入或法規備份，不應以此取代 SQLite 原生 backup。

Query-time refresh 架構

為什麼不是持續 polling

背景 polling 的成本包括：

Query-time refresh 把 freshness 成本放到真正需要資料的時刻。

流程：

Agent request
  |
  v
ensureFresh()
  |
  +-- signature unchanged --> active snapshot
  |
  +-- signature changed ----> stable copy
                                |
                                +-- valid --> new active snapshot
                                |
                                +-- invalid --> fail closed
  |
  v
read-only query

優點

缺點

實作拆解：設定邊界

設定值應分成三類。

可追蹤設定

HOST=127.0.0.1
LINE_DB_PATH=.data/fallback.db
LINE_SOURCE_DB_PATH=/absolute/path/to/source.db
LINE_SNAPSHOT_DIR=.data/snapshots
SQLITE_BIN=.local/bin/sqlite-tool

.env.example 只能放 placeholder。

真實絕對路徑不應公開。

Secret

.secrets/database-key
.secrets/api-token

要求：

大型私密產物

.data/fallback.db
.data/snapshots/
.local/bin/sqlite-tool

全部加入 .gitignore。

實作拆解：快速 dirty check

以下是簡化 TypeScript：

import { stat } from "node:fs/promises";

async function signature(databasePath: string): Promise<string> {
  const db = await stat(databasePath, { bigint: true });
  const wal = await stat(`${databasePath}-wal`, { bigint: true });

  return [
    db.dev,
    db.ino,
    db.size,
    db.mtimeNs,
    wal.dev,
    wal.ino,
    wal.size,
    wal.mtimeNs,
  ].join(":");
}

實務上還要處理：

WAL 不存在不一定是錯誤。

可能原因：

因此 signature 應明確包含 wal:none，而不是把 ENOENT 一律視為致命錯誤。

實作拆解：唯一 candidate 目錄

每次同步都建立新目錄：

.data/snapshots/.incoming/candidate-ABC123/

候選目錄的規則：

成功後才 rename：

.data/snapshots/snapshot-2026-08-24T14-27-38Z-ABC123/

這個 rename 的目的不是創造 DB atomicity。

DB atomicity 已由前面的 validation 判斷。

rename 的目的，是讓「可被 loader 看見」成為單一步驟。

實作拆解：APFS copy-on-write clone

Node.js 提供：

import { constants } from "node:fs";
import { copyFile } from "node:fs/promises";

await copyFile(source, destination, constants.COPYFILE_FICLONE);

COPYFILE_FICLONE 的語意是：

如果要求沒有 clone 就失敗：

await copyFile(source, destination, constants.COPYFILE_FICLONE_FORCE);

為什麼 clone 有價值

假設主 DB 是 70 MiB。

每次一般 copy 都要：

clone 可共享未改變 extent。

只有任一版本後續寫入時，相關 block 才分裂。

clone 不能提供什麼

clone 不保證：

所以 clone 是成本最佳化，不是一致性證明。

Apple 的 APFS 文件列出 clonefile 與 COPYFILE_CLONE API。

Node 文件也明確說 copyFile() 本身不保證 atomicity。

實作拆解：Hash 驗證

import { createHash } from "node:crypto";
import { createReadStream } from "node:fs";

async function sha256(filePath: string): Promise<string> {
  const hash = createHash("sha256");

  for await (const chunk of createReadStream(filePath)) {
    hash.update(chunk as Buffer);
  }

  return hash.digest("hex");
}

驗證主 DB：

const [sourceHash, copiedHash] = await Promise.all([
  sha256(sourceDb),
  sha256(copiedDb),
]);

if (sourceHash !== copiedHash) {
  throw new Error("Copied database hash does not match the source");
}

WAL 使用相同做法。

不要只 hash 副本

只 hash 副本只能得到 fingerprint。

它不能證明：

不要把 hash 放進公開 log

Hash 通常不是 secret。

但來源檔 hash 可能：

公開文章可用範例 hash。

內部 verification report 再保留精確值。

實作拆解：SQLite 驗證只在副本進行

檔案 hash 相同仍不代表資料庫可查。

最後一定要實際開庫。

最低驗證：

SELECT COUNT(*)
FROM sqlite_master
WHERE type = 'table';

更好的驗證：

SELECT
  (SELECT COUNT(*) FROM sqlite_master WHERE type = 'table') AS tables,
  (SELECT COUNT(*) FROM message_table) AS messages;

驗證應確認：

PRAGMA key 回成功不代表 key 正確

某些加密 SQLite wrapper 會接受 key 設定動作。

錯誤 key 可能直到第一次 page read 才失敗。

因此必須執行真正讀取 sqlite_master 的 query。

不要把 key 放 argv

錯誤做法：

sqlite-tool database.db "PRAGMA key='secret'; SELECT ..."

問題：

較好的做法是經 stdin 傳入，並避免 echo 到 log。

參考資料

下一篇：Node.js 實作、測試與維運