Архитектура SQLite: байткод VDBE, состояния блокировки файла, STRICT и два писателя

Приложение на SQLite получает database is locked на COMMIT писателя и на SELECT новых читателей, а в EXPLAIN не видно ни одной блокировки. Причина в устройстве: SQL компилируется в программу виртуальной машины VDBE, машина ходит по b-tree, b-tree просит страницы у pager, а блокировки файла берёт нижний слой, и конфликт всплывает там, где транзакция пытается закрыться. Опыты на sqlite3 из Python: байткод запроса с JOIN, типы по значениям и STRICT, переходы блокировок при разных соединениях и два процесса, которые пишут одновременно. Как выглядит ячейка b-tree, показано в статье про строки в трёх движках, как работают журнал и WAL — в статье про режимы журнала SQLite, зачем вообще нужна база, а не файл — в первой статье о базах и файлах.

Стенд

Каталог: D=$(mktemp -d /tmp/sqlarch.XXXXXX) && cd $D; скрипты ниже сохраняются в нём (в конце rm -rf $D) как e1.py, e2.py, e3.py, e4.py. Версия sqlite3.sqlite_version — 3.50.2, каталог на tmpfs (findmnt -T . -o FSTYPE): секунды ничего не говорят о диске, поэтому считаем опкоды, строки и ошибки.

Опыт 1: EXPLAIN запроса с JOIN

Две таблицы, индекс по orders.customer_id, три заказа. Запрос выбирает заказы одного клиента. Сначала план, потом программа:

import sqlite3
db = sqlite3.connect("e1.db", isolation_level=None)
db.executescript("""
CREATE TABLE customers(id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INT, total INT);
CREATE INDEX orders_customer ON orders(customer_id);
INSERT INTO customers VALUES (1,'ann'),(2,'bob');
INSERT INTO orders VALUES (1,1,10),(2,1,20),(3,2,30);
""")
q = "SELECT c.name, o.total FROM customers c JOIN orders o ON o.customer_id = c.id WHERE c.id = 1"
for r in db.execute("EXPLAIN QUERY PLAN " + q): print(r[3])
for r in db.execute("EXPLAIN " + q): print("%-4d%-13s%-4d%-4d%-4d%-8s%d" % (r[0], r[1], r[2], r[3], r[4], r[5] or "", r[6]))
print(db.execute("SELECT name, rootpage FROM sqlite_master").fetchall())
n = [0]
db.set_progress_handler(lambda: n.__setitem__(0, n[0] + 1) or 0, 1)
print(db.execute(q).fetchall(), "шагов VDBE:", n[0])

вывод
SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
SEARCH o USING INDEX orders_customer (customer_id=?)
0   Init         0   16  0           0
1   OpenRead     0   2   0   2       0
2   OpenRead     1   3   0   3       0
3   OpenRead     2   4   0   k(2,,)  2
4   Integer      1   1   0           0
5   SeekRowid    0   15  1           0
6   Integer      1   2   0           0
7   Affinity     2   1   0   D       0
8   SeekGE       2   15  2   1       0
9   IdxGT        2   15  2   1       0
10  DeferredSeek 2   0   1           0
11  Column       0   1   3           0
12  Column       1   2   4           0
13  ResultRow    3   2   0           0
14  Next         2   9   1           0
15  Halt         0   0   0           0
16  Transaction  0   0   3   0       1
17  Goto         0   1   0           0
[('customers', 2), ('orders', 3), ('orders_customer', 4)]
[('ann', 10), ('ann', 20)] шагов VDBE: 26

Колонки EXPLAIN: адрес, опкод, P1–P5. Программа начинается с Init: прыжок на адрес 16 (P2), там Transaction и Goto 1 назад; адрес 15 — Halt, куда ведут выходы. Опкоды по порядку:

Корневые страницы в sqlite_master (2, 3 и 4) совпадают с P2 у OpenRead: индекс и обе таблицы лежат в одном файле, каждая в своём дереве. Запрос с двумя строками результата выполнил 26 шагов VDBE (обработчик прогресса с шагом 1).

EXPLAIN QUERY PLAN — не другой механизм, а сводка тех же обращений к курсорам. SEARCH c USING INTEGER PRIMARY KEY — это SeekRowid на адресе 5: по уникальному ключу строка одна, Next у этого курсора нет, цикла нет. SEARCH o USING INDEX orders_customer — связка SeekGE, IdxGT, Next и DeferredSeek: единственный цикл программы. План не показывает Transaction, Column, ResultRow и всё, что происходит с блокировками: он описывает доступ к данным, а не работу машины.

Сама машина — цикл по массиву опкодов:

  for(pOp=&aOp[p->pc]; 1; pOp++){
    /* Errors are detected by individual opcodes, with an immediate
    ** jumps to abort_due_to_error. */
    assert( rc==SQLITE_OK );

    assert( pOp>=aOp && pOp<&aOp[p->nOp]);
    nVmStep++;

Источник: src/vdbe.c L899-L905, тег version-3.50.2, функция sqlite3VdbeExec. Дальше switch( pOp->opcode ) (строка 982) выбирает код опкода. OP_Next вызывает sqlite3BtreeNext (строка 6440) и при успехе переходит на P2:

  if( rc==SQLITE_OK ){
    pC->nullRow = 0;
    p->aCounter[pOp->p5]++;
    ...
    goto jump_to_p2_and_check_for_interrupt;
  }

Источник: src/vdbe.c L6445-L6452, case OP_Next; пропущены строки 6448–6450 (счётчик для тестовой сборки).

Опыт 2: тип у значения, а не у колонки

В колонку INTEGER кладём число, строку-число, текст, дробь, 2.0 и blob; затем то же в таблицу STRICT:

import sqlite3
db = sqlite3.connect(":memory:", isolation_level=None)
db.execute("CREATE TABLE loose(n INTEGER)")
db.execute("CREATE TABLE strict_t(n INTEGER) STRICT")
for v in ("'42'", "'abc'", "1.5", "2.0", "x'ff'"):
    db.execute(f"INSERT INTO loose VALUES ({v})")
print("loose:", db.execute("SELECT n, typeof(n) FROM loose").fetchall())
for v in ("'42'", "'abc'", "1.5"):
    try:
        db.execute(f"INSERT INTO strict_t VALUES ({v})"); print("strict", v, "ok")
    except sqlite3.Error as e: print("strict", v, type(e).__name__ + ":", e)
print("strict:", db.execute("SELECT n, typeof(n) FROM strict_t").fetchall())

вывод
loose: [(42, 'integer'), ('abc', 'text'), (1.5, 'real'), (2, 'integer'), (b'\xff', 'blob')]
strict '42' ok
strict 'abc' IntegrityError: cannot store TEXT value in INTEGER column strict_t.n
strict 1.5 IntegrityError: cannot store REAL value in INTEGER column strict_t.n
strict: [(42, 'integer')]

В обычной таблице '42' стало числом 42 (колонка INTEGER пытается преобразовать значение), 'abc' осталось текстом, 1.5 — real, 2.0 превратилось в целое, blob остался blob: тип записан в каждом значении. Ловить это на чужой базе можно запросом SELECT typeof(col), count(*) FROM t GROUP BY 1. В STRICT (с 3.37.0) '42' преобразуется, 'abc' и 1.5 отвергаются дословно: cannot store TEXT value in INTEGER column strict_t.n. По документации в STRICT разрешены только типы INT, INTEGER, REAL, TEXT, BLOB и ANY. Проверки значений в PostgreSQL, где тип колонки строгий всегда, — в статье об ограничениях.

Опыт 3: состояния блокировки файла

Три соединения к одной базе в режиме DELETE (по умолчанию), timeout=0 — ошибка сразу, без ожидания. A пишет, B держит читающую транзакцию, C пытается прочитать:

import sqlite3, sys
mode = sys.argv[1]
p = f"e3_{mode}.db"
def c(): return sqlite3.connect(p, isolation_level=None, timeout=0)
def step(who, sql):
    try:
        r = who[0].execute(sql).fetchall(); print(f"{who[1]}: {sql:<36} -> ok {r if r else ''}")
    except sqlite3.OperationalError as e:
        print(f"{who[1]}: {sql:<36} -> {e}")
A, B, C = (c(), "A"), (c(), "B"), (c(), "C")
A[0].execute(f"PRAGMA journal_mode={mode}")
A[0].execute("CREATE TABLE t(id INTEGER PRIMARY KEY, v TEXT)"); A[0].execute("INSERT INTO t(v) VALUES ('old')")
print("режим:", mode)
step(A, "BEGIN IMMEDIATE")
step(A, "INSERT INTO t(v) VALUES ('a1')")
step(B, "SELECT count(*) FROM t")
step(B, "INSERT INTO t(v) VALUES ('b1')")
step(B, "BEGIN")
step(B, "SELECT count(*) FROM t")
step(A, "COMMIT")
step(C, "SELECT count(*) FROM t")
step(A, "COMMIT")
step(B, "COMMIT")
step(C, "SELECT count(*) FROM t")
step(A, "COMMIT")

вывод
режим: delete
A: BEGIN IMMEDIATE                      -> ok 
A: INSERT INTO t(v) VALUES ('a1')       -> ok 
B: SELECT count(*) FROM t               -> ok [(1,)]
B: INSERT INTO t(v) VALUES ('b1')       -> database is locked
B: BEGIN                                -> ok 
B: SELECT count(*) FROM t               -> ok [(1,)]
A: COMMIT                               -> database is locked
C: SELECT count(*) FROM t               -> database is locked
A: COMMIT                               -> database is locked
B: COMMIT                               -> ok 
C: SELECT count(*) FROM t               -> database is locked
A: COMMIT                               -> ok 

Те же шаги прочитаны так: BEGIN IMMEDIATE берёт RESERVED — другие ещё могут читать (B прочитал), но писать нельзя (INSERT у B — database is locked). COMMIT у A требует EXCLUSIVE, а B держит SHARED в своей BEGIN: отказ. Но A при этом уже захватил PENDING, и новый читатель C не проходит даже после того, как B закончил: PENDING ждёт, пока уйдут старые читатели, и не впускает новых. Повтор COMMIT у A прошёл. Состояния выведены из поведения и комментария в коде; сами блокировки в опыте не наблюдались (по комментарию в коде это POSIX-блокировки диапазонов байт файла).

  ** If a process that holds a RESERVED lock requests an EXCLUSIVE, then
  ** a PENDING lock is obtained first. A PENDING lock is implemented by
  ** obtaining a write-lock on the 'pending byte'. This ensures that no new
  ** SHARED locks can be obtained, but existing SHARED locks are allowed to
  ** persist. If the call to this function fails to obtain the EXCLUSIVE
  ** lock in this case, it holds the PENDING lock instead. The client may
  ** then re-attempt the EXCLUSIVE lock later on, after existing SHARED
  ** locks have cleared.

Источник: src/os_unix.c L1749-L1756, комментарий в начале unixLock: клиент может повторить попытку позже — это второй COMMIT в выводе. В режиме WAL читатель не блокирует писателя и наоборот, а второй писатель получает отказ: этот сценарий с выводом разобран в опыте 1 статьи о режимах журнала.

Опыт 4: два писателя

Два процесса по 5 000 INSERT в автокоммите в одну таблицу, с busy_timeout=0 и 5000 мс, в режимах DELETE и WAL:

import sqlite3, sys, os
from multiprocessing import Process, Queue
mode, tmo, n = sys.argv[1], int(sys.argv[2]), int(sys.argv[3])
p = f"e4_{mode}_{tmo}.db"

def work(who, q):
    db = sqlite3.connect(p, isolation_level=None)
    db.execute(f"PRAGMA busy_timeout={tmo}")
    ok, errs = 0, set()
    for i in range(n):
        try: db.execute("INSERT INTO t(who, v) VALUES (?, ?)", (who, "x" * 50)); ok += 1
        except sqlite3.OperationalError as e: errs.add(str(e))
    q.put((who, ok, n - ok, sorted(errs)))

if __name__ == "__main__":
    for s in ("", "-wal", "-shm", "-journal"):
        if os.path.exists(p + s): os.remove(p + s)
    d = sqlite3.connect(p, isolation_level=None); d.execute(f"PRAGMA journal_mode={mode}")
    d.execute("CREATE TABLE t(id INTEGER PRIMARY KEY, who INT, v TEXT)"); d.close()
    q = Queue(); ps = [Process(target=work, args=(i, q)) for i in (1, 2)]
    [x.start() for x in ps]; res = sorted(q.get() for _ in ps); [x.join() for x in ps]
    for r in res: print(f"{mode:6} busy_timeout={tmo:<5} процесс {r[0]}: коммитов {r[1]}, ошибок {r[2]} {r[3]}")
    print(f"{mode:6} busy_timeout={tmo:<5} строк в таблице: {sqlite3.connect(p).execute('SELECT count(*) FROM t').fetchone()[0]} из {2 * n}")

Запуск: for m in delete wal; do for t in 0 5000; do python3 e4.py $m $t 5000; done; done.

вывод
delete busy_timeout=0     процесс 1: коммитов 406, ошибок 4594 ['database is locked']
delete busy_timeout=0     процесс 2: коммитов 437, ошибок 4563 ['database is locked']
delete busy_timeout=0     строк в таблице: 843 из 10000
delete busy_timeout=5000  процесс 1: коммитов 5000, ошибок 0 []
delete busy_timeout=5000  процесс 2: коммитов 5000, ошибок 0 []
delete busy_timeout=5000  строк в таблице: 10000 из 10000
wal    busy_timeout=0     процесс 1: коммитов 3640, ошибок 1360 ['database is locked']
wal    busy_timeout=0     процесс 2: коммитов 3438, ошибок 1562 ['database is locked']
wal    busy_timeout=0     строк в таблице: 7078 из 10000
wal    busy_timeout=5000  процесс 1: коммитов 5000, ошибок 0 []
wal    busy_timeout=5000  процесс 2: коммитов 5000, ошибок 0 []
wal    busy_timeout=5000  строк в таблице: 10000 из 10000

Без busy_timeout отказ приходит сразу: в DELETE из 10 000 вставок прошло 843, потеряно 9 157, если приложение не повторяет; в WAL прошло 7 078, потеряно 2 922. Число строк в таблице равно сумме коммитов двух процессов (406 + 437 и 3 640 + 3 438): ошибка — отказ, а не «записалось и пропало». С busy_timeout=5000 оба режима записали все 10 000. Прогоны не детерминированы: в пяти прогонах DELETE без таймаута записал от 843 до 1 083 строк, в трёх прогонах WAL — от 6 089 до 7 078. Писатель в обоих режимах один, busy_timeout ставит второго в очередь.

Где SQLite неуместна

Страница Appropriate Uses For SQLite называет два случая, которые опыт 3 и 4 объясняют. Первый — база на сетевой файловой системе: блокировки файла, на которых стоят все состояния выше, в части реализаций сетевых ФС работают с ошибками, и два клиента могут изменить одну страницу одновременно (по странице, повреждение возможно). Второй — много писателей одновременно, которые не могут встать в очередь: SQLite поддерживает одного писателя на файл, а клиент-серверная СУБД нужна, когда писатели должны идти параллельно. Консультанту из этого — два вопроса клиенту: на какой файловой системе лежит файл (findmnt -T /путь/к/базе -o FSTYPE) и сколько процессов пишут в него одновременно. Резервную копию живой базы делай через backup API, а не копированием файла: см. статью про бэкапы Redis, MongoDB, ClickHouse и SQLite. По странице How SQLite Is Tested, проверенной 28.10.2026, тестового кода в 590 раз больше самой библиотеки (число приведено для версии 3.42.0), а приватный набор TH3 даёт ядру 100 % покрытия ветвей и MC/DC.

Что из этого следует

• database is locked на COMMIT значит открытого читателя. В опыте 3 COMMIT писателя не прошёл, пока B держал транзакцию, и всё это время новые читатели тоже получали отказ: ищи в приложении транзакцию, которую не закрыли.
• Писатель всегда один, поэтому нужен busy_timeout. Без него в опыте 4 потеряно 92 % вставок в DELETE и 29 % в WAL; с ним — ноль.
• Типы в SQLite проверяет приложение, пока таблица не STRICT. Аудит чужой базы начни с typeof() по каждой колонке (опыт 2).

Чек-лист

1. Выполни PRAGMA journal_mode; и findmnt -T <каталог базы> -o FSTYPE: режим журнала известен, файл не на NFS/SMB.
2. Для каждой колонки с ограничением типа проверь SELECT typeof(col), count(*) FROM t GROUP BY 1.
3. В коде записи задай busy_timeout и открывай запись через BEGIN IMMEDIATE.
4. Оцени число одновременных писателей: больше одного процесса без очереди — повод для клиент-серверной СУБД.