التزامن وWAL وتعارض لقطات القراءة

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

يحفظ القارئ المفتوح 1 عبر اعتماد الكاتب، وترى معاملة قراءة جديدة 2. لا يختبر المثال سباق كتابة ونقطة فحص متزامنين.

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

يرفض الكاتب الثاني ما دام الأول يملك المعاملة، ثم ينجح بعد تراجع صريح.