SQLite Interview Questions: WAL, Busy Errors, and Concurrent Transactions

For SQLite interview questions, start with the concurrency contract: WAL allows readers and a writer to overlap, but it does not allow several writers to modify the same database simultaneously. A reader can continue seeing an older committed snapshot while another connection commits new data. Those two facts explain many “database is locked” surprises.

Official engine behavior: SQLite documents WAL reader snapshots and serialized writes in its isolation guide. The practical distinction is between waiting for a writer and trying to upgrade a snapshot that has become stale. Those failures require different recovery reasoning.

The database examples below are original, synthetic exercises.

What does WAL change?

Write-ahead logging, or WAL, records changes in a separate log before checkpointing them into the main database file. A reader uses a consistent committed view; a writer can append newer changes while that reader retains its view. A checkpoint transfers eligible log content into the database file.

Official WAL facts: there is still one writer at a time, and the normal shared-memory WAL design requires participating processes on the same host. It is not a general network-filesystem coordination mechanism. SQLite's WAL documentation explains these boundaries.

Before discussing performance, ask which journal mode is actually active, where the file resides, how long transactions remain open, and which connection owns the write transaction. Enabling WAL does not remove transaction lifetimes or make all lock failures impossible.

Can two connections see different committed values?

Use an original stock table with one row: item 1 has ten units. Connection A begins a deferred transaction and reads ten. Connection B begins an immediate transaction, updates the row to eleven, and commits. A reads again within its existing transaction and still sees ten.

Step	Connection A	Connection B	Meaning
1	BEGIN; read 10	Idle	A establishes a read snapshot
2	Snapshot remains open	Update to 11; COMMIT	New value is committed
3	Read 10 again	Idle	A retains its earlier view
4	Attempt an update	Idle	Stale snapshot cannot become the writer
5	ROLLBACK; new read sees 11	Idle	A gets a fresh view

Step four returned extended code 517, SQLITE_BUSY_SNAPSHOT, in the local fixture. That is not an uncommitted-data anomaly. A had a valid older read view; permitting a write from that view after B's commit would violate the required transaction history.

Explain why step one includes a read. A deferred BEGIN alone does not necessarily establish the particular snapshot used by subsequent reads. An answer that only lists transaction-start statements can miss the operation that actually fixes the reader's view.

How can you reproduce the stale upgrade?

This original Python fixture uses explicit SQL transactions and two connections to the same temporary local file. It was run with SQLite 3.53.4. timeout=0 makes contention visible immediately; the operations are deliberately interleaved in one thread rather than relying on race timing.

import sqlite3
import tempfile
from pathlib import Path

with tempfile.TemporaryDirectory() as folder:
    path = str(Path(folder) / "case.db")
    a = sqlite3.connect(path, isolation_level=None, timeout=0)
    b = sqlite3.connect(path, isolation_level=None, timeout=0)
    try:
        assert a.execute("PRAGMA journal_mode=WAL").fetchone()[0] == "wal"
        a.execute("CREATE TABLE stock(id INTEGER PRIMARY KEY, units INT)")
        a.execute("INSERT INTO stock VALUES(1, 10)")
        a.execute("BEGIN")
        assert a.execute("SELECT units FROM stock").fetchone()[0] == 10
        b.execute("BEGIN IMMEDIATE")
        b.execute("UPDATE stock SET units=11 WHERE id=1")
        b.execute("COMMIT")
        assert a.execute("SELECT units FROM stock").fetchone()[0] == 10
        try:
            a.execute("UPDATE stock SET units=units+1 WHERE id=1")
        except sqlite3.OperationalError as error:
            assert error.sqlite_errorcode == sqlite3.SQLITE_BUSY_SNAPSHOT
        else:
            raise AssertionError("stale write unexpectedly succeeded")
        a.execute("ROLLBACK")
        assert a.execute("SELECT units FROM stock").fetchone()[0] == 11
    finally:
        a.close()
        b.close()

The experiment proves the specified visibility and upgrade behavior for this runtime. It does not test multiple operating-system processes, power loss, network filesystems, or throughput. Record those limits when presenting a reproducible example rather than calling a small fixture a concurrency benchmark.

How do BUSY, BUSY_SNAPSHOT, and LOCKED differ?

Official result-code distinctions: SQLITE_BUSY generally concerns contention with another connection; SQLITE_BUSY_SNAPSHOT is its extended stale-snapshot case. SQLITE_LOCKED commonly concerns a conflict within a connection or shared-cache usage. Consult the SQLite result-code reference, and capture the extended code instead of relying only on a driver's generic message.

In a separate local case, A held BEGIN IMMEDIATE, and B's BEGIN IMMEDIATE returned code five, ordinary SQLITE_BUSY. After A rolled back, B could acquire the write transaction. Waiting could address that ownership conflict; waiting inside A's stale read transaction would not refresh its snapshot in the earlier case.

Do not infer “SQLite deadlock” from every locked message. Log the connection, operation, transaction state, extended result code, and elapsed wait. Also inspect active statements or cursors when the conflict appears to be local. The same English exception text can conceal different recovery boundaries.

What does BEGIN IMMEDIATE buy you?

Official transaction behavior: deferred transactions postpone write acquisition; BEGIN IMMEDIATE attempts to start a write transaction immediately and can fail with busy contention. SQLite describes these modes in its transaction documentation.

If a read-modify-write operation definitely needs to write, acquiring the write transaction before reading can avoid discovering stale-snapshot upgrade failure after doing the calculation. It also serializes competing writers earlier. That trade-off is useful to explain rather than recommending immediate mode for every read.

Keep the protected work short. Reading a row, calling an external service, waiting for user input, and then updating while holding the write transaction would unnecessarily retain writer ownership. Do external work under a deliberately designed consistency contract, then keep the database decision bounded.

For inventory, an atomic conditional update such as UPDATE stock SET units=units-1 WHERE id=1 AND units>0 can combine the availability check with the change. Inspect whether a row was updated. That application pattern avoids a separate unprotected check, but it still operates under SQLite's writer and transaction rules.

Does a busy timeout make retries safe?

Official API behavior: a busy timeout installs waiting behavior for certain lock conflicts until the configured delay is exhausted. It does not promise that every busy condition waits or eventually succeeds. See sqlite3_busy_timeout.

Treat timeout as a contention policy, not an idempotency policy. An application still needs an overall deadline, bounded attempts, and a decision about restarting the transaction. Retrying a stale update inside the same read transaction repeats the same invalid premise.

After a stale-snapshot failure, end that transaction, start a new one, re-read the current values, and recompute the decision. If the application read ten units and calculated an action from ten, restarting only the final SQL statement can preserve stale business logic even after the database snapshot changes.

A busy COMMIT deserves inspection too: SQLite's transaction documentation notes cases where the transaction remains active and the commit can be retried. Do not generalize “all errors mean rollback succeeded” or “every error means retry just this statement.” Know which transaction is still open before the next action.

Why can a checkpoint stop making progress?

Official checkpoint behavior: active readers can limit which WAL frames can be checkpointed or when the log can be reset. Checkpoint modes have different waiting and completion behavior; the WAL checkpoint pragma returns status and frame counts.

The original checkpoint exercise first resets the WAL, then opens a read transaction on A. B updates the row to twelve. With A retaining the earlier view, B's passive checkpoint returned (0, 1, 0): one log frame, zero checkpointed frames. The first zero does not mean the WAL has been completely drained.

After A rolled back, a truncate checkpoint returned (0, 0, 0) and completed. These are observed fixture values, not counts to expect for every schema, page size, or workload. A larger database can create many frames for one logical update.

For a growing WAL, inspect transaction age and checkpointed-versus-log frames before changing SQL. A connection left in a transaction can retain a reader view long after its initial query returned. Forced checkpoint modes may add waiting; they are not a substitute for understanding the long-lived reader.

Which retry boundary should you choose?

Use this original decision ledger. If no transaction started because another writer owns the slot, a bounded acquisition retry may be appropriate. If a read transaction's snapshot is stale, restart the whole read-modify-write unit. If the failure concerns local statement state, repair that state rather than increasing the timeout blindly.

Separate database effects from external effects. Suppose an endpoint charges a card and then fails to insert a local receipt. Replaying the entire handler could charge twice unless the provider and application have an explicit idempotency and reconciliation design. SQLite rollback cannot reverse an already completed external request.

For database-only operations, define a durable operation identity when replay must be safe. A uniqueness constraint can reject a duplicate operation record; it does not automatically make every downstream effect idempotent. Test the failure point before commit, after commit, and before the client receives the response.

For an original debugging rehearsal, produce a small event log rather than only the final locked message. Record connection A or B, transaction start and end, operation name, primary and extended error, elapsed waiting, and whether a new snapshot was acquired. Then compare two schedules: writer acquisition fails before any read, versus a write upgrade fails after A has already read ten units. The same user-facing error can hide different restart boundaries. Include an overall request deadline in the rehearsal so repeated bounded waits cannot silently exceed it. These observations make the recovery argument inspectable without claiming that a larger timeout fixes every contention pattern.

Which version and file details matter?

Current official version note: SQLite documents a rare WAL-reset corruption race fixed in 3.51.3 and later, with selected older-release backports. See the WAL-reset section. The local 3.53.4 fixture is on the fixed side of that boundary; its sequential schedule does not reproduce that race.

Record the SQLite engine version actually linked into the application's driver. A command-line tool and a Python, Android, or bundled application library can use different engine builds. Mentioning the operating-system version alone does not establish database behavior.

Treat the WAL as part of database state while it contains committed changes. Do not manually delete it to silence growth or lock symptoms, or copy only the main file during active use and assume a consistent backup. Use the engine's supported backup and checkpoint procedures for the intended situation.

Sources and Further Reading

• SQLite isolation — serialized writes and WAL snapshots.
• SQLite WAL — log lifecycle, file requirements, and current version caveats.
• SQLite result codes — primary and extended errors.
• SQLite transactions — deferred/immediate transactions and commit recovery.
• SQLite busy timeout — bounded lock waiting.
• SQLite WAL checkpoint pragma — checkpoint modes and return fields.