How do SQLite transactions, journal modes (WAL) and constraints work, and which defaults surprise people?

13.2.1 · L3 · Interview

Predict first

What are the default journal mode and foreign key setting, and what does switching to WAL report?

import Foundation
import SQLite3

func openDB(_ path: String) -> OpaquePointer { var db: OpaquePointer?; precondition(sqlite3_open(path, &db) == SQLITE_OK); return db! }
func run(_ db: OpaquePointer, _ sql: String) -> Int32 { sqlite3_exec(db, sql, nil, nil, nil) }
func scalar(_ db: OpaquePointer, _ sql: String) -> String {
    var st: OpaquePointer?
    sqlite3_prepare_v2(db, sql, -1, &st, nil)
    defer { sqlite3_finalize(st) }
    guard sqlite3_step(st) == SQLITE_ROW, let c = sqlite3_column_text(st, 0) else { return "nil" }
    return String(cString: c)
}
let db = openDB(NSTemporaryDirectory() + "t13a-\(getpid()).sqlite")
print(scalar(db, "PRAGMA journal_mode"), scalar(db, "PRAGMA foreign_keys"))
print(scalar(db, "PRAGMA journal_mode=WAL"))
sqlite3_close(db)

delete 0, then wal
wal 1, then wal
delete 1, then delete

Predict first

A transaction inserts a valid row and then violates a constraint, and is rolled back. How many rows are there, and after a good commit?

import Foundation
import SQLite3

func openDB(_ path: String) -> OpaquePointer { var db: OpaquePointer?; precondition(sqlite3_open(path, &db) == SQLITE_OK); return db! }
func run(_ db: OpaquePointer, _ sql: String) -> Int32 { sqlite3_exec(db, sql, nil, nil, nil) }
func scalar(_ db: OpaquePointer, _ sql: String) -> String {
    var st: OpaquePointer?
    sqlite3_prepare_v2(db, sql, -1, &st, nil)
    defer { sqlite3_finalize(st) }
    guard sqlite3_step(st) == SQLITE_ROW, let c = sqlite3_column_text(st, 0) else { return "nil" }
    return String(cString: c)
}
let db = openDB(NSTemporaryDirectory() + "t13b-\(getpid()).sqlite")
_ = run(db, "CREATE TABLE a(id INTEGER PRIMARY KEY, n TEXT NOT NULL)")
_ = run(db, "BEGIN"); _ = run(db, "INSERT INTO a(n) VALUES('x')"); _ = run(db, "INSERT INTO a(n) VALUES(NULL)"); _ = run(db, "ROLLBACK")
print(scalar(db, "SELECT count(*) FROM a"))
_ = run(db, "BEGIN"); _ = run(db, "INSERT INTO a(n) VALUES('x')"); _ = run(db, "INSERT INTO a(n) VALUES('y')"); _ = run(db, "COMMIT")
print(scalar(db, "SELECT count(*) FROM a"))
sqlite3_close(db)

1, then 3
0, then 2
2, then 2

Predict first

A child row references a parent that does not exist. When does it succeed?

import Foundation
import SQLite3

func openDB(_ path: String) -> OpaquePointer { var db: OpaquePointer?; precondition(sqlite3_open(path, &db) == SQLITE_OK); return db! }
func run(_ db: OpaquePointer, _ sql: String) -> Int32 { sqlite3_exec(db, sql, nil, nil, nil) }
func scalar(_ db: OpaquePointer, _ sql: String) -> String {
    var st: OpaquePointer?
    sqlite3_prepare_v2(db, sql, -1, &st, nil)
    defer { sqlite3_finalize(st) }
    guard sqlite3_step(st) == SQLITE_ROW, let c = sqlite3_column_text(st, 0) else { return "nil" }
    return String(cString: c)
}
let db = openDB(NSTemporaryDirectory() + "t13c-\(getpid()).sqlite")
_ = run(db, "CREATE TABLE a(id INTEGER PRIMARY KEY)")
_ = run(db, "CREATE TABLE b(id INTEGER PRIMARY KEY, a_id INTEGER REFERENCES a(id))")
print(run(db, "INSERT INTO b(a_id) VALUES(999)") == SQLITE_OK)
_ = run(db, "PRAGMA foreign_keys=ON")
print(run(db, "INSERT INTO b(a_id) VALUES(998)") == SQLITE_OK)
sqlite3_close(db)

false, then false
true, then false
true, then true

Mental model. A transaction is writing a whole letter in draft and posting it only when it is complete. WAL is leaving drafts in an outbox that the post office collects later, so people can keep reading the mailbox in the meantime.

SQLite stores a whole database in one file and gives ACID transactions: a group of statements between BEGIN and COMMIT either all take effect or, after ROLLBACK (or a crash), none do. Without an explicit transaction every statement is its own transaction, which makes bulk inserts slow (each one syncs to disk) and multi-step updates unsafe: wrap related changes in one transaction. A rollback really discards the work: inserting a good row and then a row that violates NOT NULL and rolling back leaves the table empty. To stay atomic across crashes SQLite writes to a journal first. The default mode is a rollback journal (journal_mode = delete): readers and the writer block each other. WAL (PRAGMA journal_mode = WAL) appends changes to a separate -wal file and periodically checkpoints them into the database: readers do not block the writer and the writer does not block readers (still one writer at a time), and commits are faster. The mode is persistent for the database file. The cost: extra -wal and -shm files that must travel with the database (copy all three, or checkpoint first), so a database kept in WAL must not be copied or backed up as the single .sqlite file while it is open.

Know the surprising defaults. Foreign keys are not enforced by default: PRAGMA foreign_keys is 0 on every new connection, so an insert that references a missing parent succeeds until you turn it on per connection. SQLite is dynamically typed (a column’s declared type is an affinity, not a constraint; use STRICT tables or CHECK constraints to enforce types). PRAGMA user_version is a free integer in the file header, which is the standard place to store the schema version. PRAGMA integrity_check returns ok for a healthy database. Concurrency: many connections can read, one can write; SQLITE_BUSY is returned when the lock is held, so set a busy_timeout and keep transactions short. Finally, SQLite and iOS: a suspended app holding a lock on a database in a shared container can be terminated by the system (the 0xdead10cc case in the platform topic), so finish writes before backgrounding and never keep transactions open.

A new database uses the rollback journal and does not enforce foreign keys. The WAL pragma reports the new mode.

A rollback discards everything in the transaction, including the valid row. The committed transaction keeps both rows.

With foreign keys off (the default) the bad reference is accepted. After enabling the pragma on the connection it is rejected.

Common traps

Assuming foreign keys are enforced. They are off by default on each new connection; run PRAGMA foreign_keys = ON.
Inserting many rows without a transaction. Each insert commits separately, which is very slow; batch them.

They might follow up with

What does WAL change for concurrency? Readers and the writer no longer block each other.
Where do you store the schema version? PRAGMA user_version.

How do SQLite transactions, journal modes (WAL) and constraints work, and which defaults surprise people?

Predict first

Predict first

Predict first

Common traps

They might follow up with

Related questions