Concurrency & Locking

This chapter delivers on sqlite1-1's own roadmap entry for concurrency — and makes explicit contact with postgres1-9's own MVCC chapter, honestly, rather than treating SQLite's simpler model as a lesser version of it.

Why SQLite's Concurrency Model Is Different by Design

sqlite1-1's own throughline applies directly here: SQLite solves a different problem than MySQL/Postgres, so its concurrency answer is naturally different too — not worse. A single embedded application, or a small number of processes sharing one local file, has fundamentally different concurrency needs than many independent, networked clients.

Traditional Locking — Single-Writer, Multiple-Reader

SQLite's traditional locking model (the original rollback-journal mode): at any given moment, either multiple readers can access the database simultaneously, or exactly one writer can — never both at once, and never multiple simultaneous writers. This is enforced through ordinary operating-system file locks on the database file itself — a genuinely different mechanism from Postgres's own MVCC snapshot-based approach from postgres1-9.

A real practical consequence: in traditional mode, a write blocks all reads for its duration — a genuine limitation for any use case with meaningfully concurrent readers and writers.

WAL Mode — The Modern Improvement

PRAGMA journal_mode=WAL;

Write-Ahead Logging (WAL) mode is a real, meaningful improvement: readers can continue reading a consistent, slightly-older snapshot of the database while a write is in progress, since new writes are appended to a separate WAL file rather than modifying the main database file directly, and are only periodically "checkpointed" back into it.

It's important to be precise about what WAL does and doesn't fix: it still allows only one writer at a time. WAL improves reader/writer concurrency — readers no longer block on a write in progress — but it does not add support for multiple simultaneous writers. This is worth stating clearly rather than glossing over.

Honest Comparison With Postgres's Own MVCC

postgres1-9's MVCC exists to let many separate client connections — potentially dozens or hundreds — read and write concurrently without blocking each other, via genuine row-versioning (xmin/xmax). It solves the problem of many independent, networked clients sharing one actively-written dataset.

SQLite's own locking, traditional or WAL, exists to let a small number of processes or threads sharing one local file coordinate safely, without needing anything as sophisticated as MVCC's own row-versioning machinery — because the actual concurrency demands of a typically-single-application embedded context are fundamentally lower. It's tempting to read this as "SQLite's concurrency is worse than Postgres's," but that's exactly the same category error sqlite1-1 warned against — SQLite's locking is simpler because the problem it's solving is simpler, not because it's a weaker attempt at Postgres's own problem.

When This Actually Matters

An embedded mobile app or a CLI tool essentially never has meaningful write concurrency at all — often literally one process, sometimes one thread — making this whole topic close to moot in that context. A shared, multi-process server-side use case (SQLite used as an actual production database behind a web application, previewed in sqlite1-6 and sqlite1-7) is exactly where WAL mode's own single-writer limit starts to become a real, practical constraint worth understanding in advance.

Hands-On Exercises