How does write-ahead logging change locking, what does SQLITE_BUSY mean, and how do you configure busy handling and transactions?

13.9.3 · L5 · Interview

Predict first

What do the reader and a second writer experience during an open write transaction in WAL mode?

import Foundation
import SQLite3

final class DB {
    var h: OpaquePointer?
    init(_ path: String = ":memory:", busy: Int32 = 0) { sqlite3_open(path, &h); sqlite3_busy_timeout(h, busy) }
    deinit { sqlite3_close(h) }
    @discardableResult func exec(_ sql: String) -> String? {
        var err: UnsafeMutablePointer<CChar>?
        if sqlite3_exec(h, sql, nil, nil, &err) != SQLITE_OK { defer { sqlite3_free(err) }; return String(cString: err!) }
        return nil
    }
    func rows(_ sql: String) -> [[String]] {
        var st: OpaquePointer?; var out: [[String]] = []
        guard sqlite3_prepare_v2(h, sql, -1, &st, nil) == SQLITE_OK else { return [["error: " + String(cString: sqlite3_errmsg(h))]] }
        defer { sqlite3_finalize(st) }
        while sqlite3_step(st) == SQLITE_ROW {
            out.append((0..<sqlite3_column_count(st)).map { i in sqlite3_column_text(st, i).map { String(cString: $0) } ?? "NULL" })
        }
        return out
    }
}

let dir = FileManager.default.temporaryDirectory.appendingPathComponent(UUID().uuidString)
try! FileManager.default.createDirectory(at: dir, withIntermediateDirectories: true)
let path = dir.appendingPathComponent("t.db").path
let a = DB(path)
print("default journal_mode:", a.rows("PRAGMA journal_mode")[0][0])
a.exec("CREATE TABLE t(v INTEGER)"); a.exec("INSERT INTO t VALUES(1)")
print("after switching:", a.rows("PRAGMA journal_mode = WAL")[0][0])
let b = DB(path)
a.exec("BEGIN IMMEDIATE"); a.exec("INSERT INTO t VALUES(2)")
print("reader during the write sees:", b.rows("SELECT count(*) FROM t")[0][0], "row(s)")
print("second writer:", b.exec("INSERT INTO t VALUES(3)") ?? "ok")
a.exec("COMMIT")
print("second writer after commit:", b.exec("INSERT INTO t VALUES(3)") ?? "ok", "count", b.rows("SELECT count(*) FROM t")[0][0])
print("nested BEGIN:", { a.exec("BEGIN"); return a.exec("BEGIN") ?? "ok" }())

default journal_mode: delete / after switching: wal / reader during the write sees: 1 / second writer: database is locked / after commit: ok count 3 / nested BEGIN: cannot start a transaction within a transaction
The reader blocks
The second writer succeeds immediately

Mental model. A rollback journal is a single-lane bridge where traffic stops for roadworks. WAL is a bridge with a second lane for the work crew: cars keep flowing, but only one crew can work at a time.

The default journal mode is delete (a rollback journal); PRAGMA journal_mode = WAL switches to a write-ahead log and the mode is persistent in the file (the check prints delete, then wal). In WAL, a writer appends changes to a separate log and readers keep reading the last committed snapshot, so readers do not block the writer and the writer does not block readers: during the open write transaction the second connection still reads the old row count (1). There is still exactly one writer at a time: a second connection that tries to write while the first holds the write lock fails at once with SQLITE_BUSY (“database is locked”), and succeeds after the first commits. A busy_timeout (the sqlite3_busy_timeout call, GRDB’s busyMode) makes SQLite retry for a while instead of failing immediately; the check used timeout 0 to show the raw behaviour.

Practical settings: enable WAL for app databases (better concurrency and often faster commits), set a busy timeout of a few seconds, keep write transactions short and never hold one across user interaction or a network call, use BEGIN IMMEDIATE for transactions that will write (taking the write lock up front avoids a deadlock-style failure when upgrading a read lock), and use one connection per concurrent writer path or a single serial writer with a pool of readers (what GRDB’s DatabasePool does). WAL creates -wal and -shm side files that must travel with the database (do not copy only the main file for a backup: checkpoint first or use the backup API), and a long-running reader can stop checkpoints from completing, letting the log grow. Nested BEGIN is an error (use SAVEPOINT for nesting). Sharing a WAL database between an app and an extension needs care (Q232).

Readers see the last committed snapshot, but only one writer may hold the write lock.

Common traps

Treating SQLITE_BUSY as a corruption error. It is a lock conflict; set a busy timeout and shorten transactions.
Backing up only the .sqlite file of a WAL database. Recent commits may live only in the -wal file.

They might follow up with

Does WAL allow two simultaneous writers? No: many readers, one writer.
Why BEGIN IMMEDIATE? A deferred transaction that reads then tries to write can fail with SQLITE_BUSY when it upgrades its lock; taking the write lock first avoids it.

How does write-ahead logging change locking, what does SQLITE_BUSY mean, and how do you configure busy handling and transactions?

Predict first

Common traps

They might follow up with

Related questions