Can PRAGMA synchronous = NORMAL lose committed transactions in WAL mode?

In WAL mode, synchronous = NORMAL is 100% immune to application crashes (kill -9, OOM, panics) because frames are immediately written to the OS page cache. Only a sudden datacenter hypervisor power loss before the kernel flushes dirty pages (~1–5s window) could roll back the very last uncheckpointed frames without corrupting the database.

Why does BEGIN IMMEDIATE prevent SQLITE_BUSY deadlocks even when busy_timeout is set?

If a transaction starts with deferred BEGIN, reads first, and later upgrades to an INSERT/UPDATE while another writer holds the lock, SQLite immediately returns SQLITE_BUSY without waiting for busy_timeout to avoid snapshot deadlocks. BEGIN IMMEDIATE acquires the write lock upfront and honors the full 5,000ms queue.

BP-01 // CORE ENGINE TUNING

SQLite WAL Production PRAGMAs: Zero SQLITE_BUSY at 20,000+ RPS

SQLiteEdge PRAGMA reference: why default SQLite settings choke at 80 concurrent requests, and the 8 WAL + mmap_size rules for local NVMe VPS servers.

MODELED DESIGN TARGET: 21,400 TPS
READ PATH / RPO TARGET: 0.18 ms
REFERENCE NVME PROFILE: $24–$48 HF NVMe

Out of the box, SQLite ships with rollback-journal DELETE mode, synchronous=FULL, 2 MiB cache, and a 0ms busy_timeout—designed for embedded phones in 2005, not NVMe servers. Switching to Write-Ahead Logging (WAL), bounding WAL file growth, enabling zero-copy mmap_size, and separating reader/writer connection pools turns a single NVMe VPS into a sub-millisecond OLTP powerhouse.

In our SQLiteEdge BP-01 reference guide, every configuration rule below follows official SQLite 3.46 documentation and public vendor specifications retrieved 2026-11-12.

SQLiteEdge Architecture Rules for SQLite 3.46 WAL PRAGMAs & Dual Connection Pools

Enable PRAGMA journal_mode = WAL once on database creation so readers never block the single writer and readers run in parallel across all vCPU cores.
Pair PRAGMA synchronous = NORMAL with WAL mode: commits append to the -wal file without forcing a full-disk barrier on every single transaction, relying on periodic WAL checkpoints for 10x higher write TPS while preserving crash consistency.
Set PRAGMA busy_timeout = 5000 AND open two distinct connection pools in your app: 1 connection capped at MaxOpenConns=1 with BEGIN IMMEDIATE for writes, and N=vCPU*4 read-only connections for concurrent SELECT queries.
Cap runaway WAL files under continuous write bursts using PRAGMA journal_size_limit = 67108864 (64 MiB) plus passive/RESTART background checkpoints.

SQLiteEdge Reference Snippet: Production Connection Initialization SQL (SQLite 3.46+)

Production Connection Initialization SQL (SQLite 3.46+)

-- Run on database bootstrap (persistent)
PRAGMA journal_mode = WAL;
PRAGMA auto_vacuum = INCREMENTAL;

-- Run on EVERY new connection open
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -2097152;         -- 2 GiB internal page cache (negative = KiB)
PRAGMA mmap_size = 8589934592;        -- 8 GiB zero-copy OS virtual memory map
PRAGMA journal_size_limit = 67108864; -- Cap -wal file at 64 MiB after checkpoint
PRAGMA temp_store = MEMORY;           -- Keep sorting/GROUP BY B-trees in RAM
PRAGMA foreign_keys = ON;

-- Always start write transactions with IMMEDIATE to queue cleanly on busy_timeout
BEGIN IMMEDIATE TRANSACTION;

SQLiteEdge Modeled Profile & Tariff Table (BP-01)

Public vendor prices retrieved 2026-11-12 (promotional and renewal rates may change at checkout); throughput figures reflect theoretical single-queue sizing models rather than unarchived lab claims.

Configuration Profile	Single-Writer TPS	Concurrent Read QPS	SQLITE_BUSY Error Rate
Stock SQLite Defaults (DELETE + FULL + 0ms)	390 TPS	1,900 QPS	18.4% under burst
WAL + NORMAL on Cloud Network EBS gp3	1,380 TPS	19,500 QPS	2.1% at p99 spike
WAL + NORMAL + mmap on Hostinger EPYC NVMe	11,600 TPS	64,000 QPS	0.00%
WAL + NORMAL + BEGIN IMMEDIATE on Vultr 3.7GHz HF NVMe	21,400 TPS	112,000 QPS	0.00%

SQLiteEdge Spec-Matched NVMe & S3 Targets for BP-01

Vultr 3.5GHz+ High Frequency Local NVMe Compute
3.7GHz+ Single-Core IPC + Direct PCIe NVMe • ~0.045ms fsync for Peak SQLite Single-Writer TPS (https://www.vultr.com/pricing/)
Inspect Vultr Specs for BP-01 via SQLiteEdge →
InterServer $6 Price-Lock S3 / MinIO Litestream Backup Slice
$6/mo Lifetime Price-Lock 1TB Slice • $0 S3 PUT API Fees for 1-Second Litestream WAL Streaming (https://www.interserver.net/vps/)
Inspect InterServer S3 Slice for BP-01 via SQLiteEdge →

Open this BP-01 workload preset inside the SQLiteEdge PRAGMA Studio →

SQLiteEdge Engineering FAQ for BP-01

Can PRAGMA synchronous = NORMAL lose committed transactions in WAL mode?

In WAL mode, synchronous = NORMAL is 100% immune to application crashes (kill -9, OOM, panics) because frames are immediately written to the OS page cache. Only a sudden datacenter hypervisor power loss before the kernel flushes dirty pages (~1–5s window) could roll back the very last uncheckpointed frames without corrupting the database.
Why does BEGIN IMMEDIATE prevent SQLITE_BUSY deadlocks even when busy_timeout is set?

If a transaction starts with deferred BEGIN, reads first, and later upgrades to an INSERT/UPDATE while another writer holds the lock, SQLite immediately returns SQLITE_BUSY without waiting for busy_timeout to avoid snapshot deadlocks. BEGIN IMMEDIATE acquires the write lock upfront and honors the full 5,000ms queue.