7.9.39. SQL-38 — Concurrency: threads, contention

Concept-only tutorial — no new API. SQLite’s concurrency model is blunt but predictable: writers serialize, readers run in parallel (with WAL), and the connection is the unit of locking.

7.9.39.1. Three rules

One connection per thread. Never share a SqlRunner across threads. The handle is connection-state on the C side; SQLite itself is thread-safe at the library level (built with SQLITE_THREADSAFE=1) but a single connection’s prepared statements + transaction state are not.
WAL + busy_timeout is not optional. WAL lets readers and one writer coexist; busy_timeout makes contended writes wait instead of failing immediately. apply_recommended_pragmas sets both. (See tut 33.)
Short write transactions win. Long-running writers block every other writer until commit. Compute outside the transaction; batch the actual rows; commit fast.

7.9.39.2. Connection per thread

Each worker opens its own with_sqlite(...). The underlying file is shared; the handle is per-thread. WAL guarantees readers see a consistent snapshot while another thread writes:

def worker_read(path : string) {
    with_sqlite(path) $(db) {
        // separate handle, separate prepared-statement cache
        let n = _sql(db |> select_from(type<Counter>) |> count)
        to_log(LOG_INFO, "thread {get_thread_id()} sees n={n}")
    }
}

7.9.39.3. Recommended-pragma defaults

apply_recommended_pragmas sets:

journal_mode = WAL (concurrent reads + one writer)
synchronous = NORMAL (durable on commit, faster than FULL)
foreign_keys = ON (constraint enforcement)
busy_timeout = 5000 (5s wait on lock contention)

This is the right starting point for any multi-threaded / multi-process workload.

7.9.39.4. Busy handling

When a writer holds the write lock and another connection tries to write, SQLite returns SQLITE_BUSY. With busy_timeout=5000, libsqlite3 retries internally for up to 5s before surfacing the error. dasSQLITE turns this into a panic in the strict path, or Result::Err in the try_* path. Three patterns:

Default (recommended): apply_recommended_pragmas plus short transactions. Most workloads never hit the 5s ceiling.
Manual retry: try_with_transaction returns SqlError; on some(err), check the error string for "database is locked" / "BUSY", sleep with jitter, retry up to a cap.
Tune the timeout: db |> set_pragma("busy_timeout", 30000) if you have legitimate load spikes longer than 5s.

7.9.39.5. What breaks

Sharing one SqlRunner across threads. Prepared statements held by thread A are silently visible to thread B; concurrent step calls on the same sqlite3_stmt* is undefined behavior. Always open per-thread.
Long-running transactions. A with_transaction block that reads a million rows + computes blocks every other writer until commit. Either run the read outside the transaction and commit only the writes, or use a snapshot read connection in WAL mode (no lock acquired).
journal_mode=DELETE (the legacy default). apply_recommended_pragmas uses WAL. Verify with query_scalar("PRAGMA journal_mode") if you inherit a DB built elsewhere.

7.9.39.6. Out of scope

Connection pools, MVCC across connections, SQLite’s session extension, distributed-write coordination — these are use-case specific. The 80% case is “one connection per thread, WAL, busy_timeout, short transactions” and that’s what this tutorial teaches.