Concurrency, WAL, and busy snapshots

import sqlite3
from pathlib import Path
from tempfile import TemporaryDirectory
from contextlib import closing

with TemporaryDirectory(prefix="sqlite-wal-training-") as temporary:
    path = Path(temporary) / "training.db"
    with closing(sqlite3.connect(path, isolation_level=None)) as writer, \
         closing(sqlite3.connect(path, isolation_level=None)) as reader:
        assert writer.execute("PRAGMA journal_mode=WAL").fetchone()[0] == "wal"
        writer.executescript("CREATE TABLE item(value INTEGER); INSERT INTO item VALUES(1);")
        reader.execute("BEGIN")
        first = reader.execute("SELECT value FROM item").fetchone()[0]
        writer.execute("UPDATE item SET value=2")
        same = reader.execute("SELECT value FROM item").fetchone()[0]
        reader.execute("COMMIT")
        later = reader.execute("SELECT value FROM item").fetchone()[0]
        print("snapshot", first, same, later)

snapshot 1 1 2

The open reader keeps1 across the writer commit; a new reader transaction observes2. No simultaneous checkpoint/write race is attempted.

import sqlite3
from pathlib import Path
from tempfile import TemporaryDirectory
from contextlib import closing

with TemporaryDirectory(prefix="sqlite-busy-training-") as temporary:
    path = Path(temporary) / "training.db"
    with closing(sqlite3.connect(path, isolation_level=None, timeout=0)) as first, \
         closing(sqlite3.connect(path, isolation_level=None, timeout=0)) as second:
        first.executescript("CREATE TABLE item(value INTEGER); INSERT INTO item VALUES(1);")
        first.execute("BEGIN IMMEDIATE")
        try:
            second.execute("BEGIN IMMEDIATE")
        except sqlite3.OperationalError as error:
            assert error.sqlite_errorcode == sqlite3.SQLITE_BUSY
            print("contender", error.sqlite_errorname)
        else:
            raise AssertionError("expected bounded writer contention")
        finally:
            first.execute("ROLLBACK")
        second.execute("BEGIN IMMEDIATE")
        second.execute("UPDATE item SET value=3")
        second.execute("COMMIT")
        print("after_release", second.execute("SELECT value FROM item").fetchone()[0])

contender SQLITE_BUSY
after_release 3

The second writer is rejected while the first owns the transaction, then succeeds after explicit rollback.