SQLite: rollback journal и WAL, один писатель, файл -shm и PRAGMA synchronous

Приложение на SQLite получает database is locked, когда один поток пишет, а другой читает, а INSERT в цикле упирается в диск. Причина в режиме журнала. В режиме DELETE перед правкой страницы SQLite копирует её старый вид в -journal, а на COMMIT ждёт, пока читатели уйдут. В режиме WAL новые страницы дописываются в -wal, читатели смотрят в -shm, и писатель их не ждёт, но писатель остаётся один. Опыт на sqlite3 из Python: где лежат старые и новые страницы, кто кого ждёт и сколько вызовов fdatasync стоит коммит. Как база переживает обрыв в журнале, показано в статье про базу и файлы, как фиксирует коммит WAL PostgreSQL — в статье о WAL.

DBA / 01koddo

WAL SQLite: читатель на снимке, один писатель дописывает

Стенд

D=$(mktemp -d /tmp/sqlj.XXXXXX) && cd $D
python3 -c "import sqlite3; print(sqlite3.sqlite_version)"
findmnt -T . -o TARGET,FSTYPE

3.50.2
TARGET FSTYPE
/tmp   tmpfs

Каталог на tmpfs: диска под ним нет, fdatasync почти ничего не стоит, и время в опытах отражает только число файловых операций. Чтобы увидеть цену синхронизации, считаем вызовы, а не секунды: перехватчик fsync и fdatasync через LD_PRELOAD, файл count.c:

#define _GNU_SOURCE
#include <dlfcn.h>
#include <stdio.h>
static long n_fsync, n_fdatasync;
int fsync(int fd){ static int(*f)(int); if(!f) f=dlsym(RTLD_NEXT,"fsync"); n_fsync++; return f(fd);}
int fdatasync(int fd){ static int(*f)(int); if(!f) f=dlsym(RTLD_NEXT,"fdatasync"); n_fdatasync++; return f(fd);}
__attribute__((destructor)) static void bye(void){ fprintf(stderr,"fsync=%ld fdatasync=%ld\n",n_fsync,n_fdatasync);}

gcc -shared -fPIC -o count.so count.c -ldl

Опыт 1: читатель и писатель в двух режимах

Читатель открывает транзакцию и читает таблицу; писатель с timeout=0 пытается вставить строку, пока читатель не закончил.

import sqlite3, sys, threading, time
mode = sys.argv[1]
p = f"conc_{mode}.db"
def conn(timeout=0):
    return sqlite3.connect(p, isolation_level=None, timeout=timeout)
w = conn(); w.execute(f"PRAGMA journal_mode={mode}")
w.execute("CREATE TABLE t(id INTEGER PRIMARY KEY, v TEXT)"); w.execute("INSERT INTO t(v) VALUES ('old')")
r = conn(); r.execute("BEGIN"); print(mode, "читатель видит:", r.execute("SELECT count(*) FROM t").fetchone())
try:
    w.execute("INSERT INTO t(v) VALUES ('new')"); print(mode, "писатель: INSERT и COMMIT прошли")
except sqlite3.OperationalError as e:
    print(mode, "писатель:", e)
print(mode, "читатель всё ещё видит:", r.execute("SELECT count(*) FROM t").fetchone())
r.execute("COMMIT")
print(mode, "читатель после COMMIT:", r.execute("SELECT count(*) FROM t").fetchone())
if mode == "WAL":
    a = sqlite3.connect(p, isolation_level=None, check_same_thread=False); b = conn()
    a.execute("BEGIN IMMEDIATE")
    try: b.execute("BEGIN IMMEDIATE")
    except sqlite3.OperationalError as e: print("второй писатель, busy_timeout=0:", e)
    b2 = conn(timeout=3)
    threading.Timer(0.5, lambda: a.execute("COMMIT")).start()
    t = time.perf_counter(); b2.execute("BEGIN IMMEDIATE"); print(f"второй писатель, busy_timeout=3000: вошёл через {time.perf_counter()-t:.2f} с")

python3 conc.py DELETE; python3 conc.py WAL

вывод
DELETE читатель видит: (1,)
DELETE писатель: database is locked
DELETE читатель всё ещё видит: (1,)
DELETE читатель после COMMIT: (1,)
WAL читатель видит: (1,)
WAL писатель: INSERT и COMMIT прошли
WAL читатель всё ещё видит: (1,)
WAL читатель после COMMIT: (2,)
второй писатель, busy_timeout=0: database is locked
второй писатель, busy_timeout=3000: вошёл через 0.53 с

В DELETE читатель держит общую блокировку файла, и писатель не может взять эксклюзивную для COMMIT: отказ. В WAL писатель прошёл, а читатель продолжил видеть одну строку, пока не закончил транзакцию, и только после COMMIT увидел две: это снимок на момент начала чтения. Писателей же в WAL по-прежнему один: второй BEGIN IMMEDIATE сразу получил database is locked, а с busy_timeout в 3 с ждал 0,53 с, пока первый закончит. Что происходит, когда транзакция сначала читает, а потом хочет писать, разобрано в статье о дедлоках: SQLITE_BUSY там приходит без ожидания.

В коде это одна блокировка (WAL_WRITE_LOCK), которую берёт sqlite3WalBeginWriteTransaction:

  /* Only one writer allowed at a time.  Get the write lock.  Return
  ** SQLITE_BUSY if unable.
  */
  rc = walLockExclusive(pWal, WAL_WRITE_LOCK, 1);
  if( rc ){
    return rc;
  }
  pWal->writeLock = 1;

Источник: src/wal.c L3698-L3705, тег version-3.50.2. Отказ, а не очередь: поэтому ждать приходится через busy_timeout.

Опыт 2: где лежат страницы и что в -wal

Таблица на 100 000 строк по 50 байт, затем транзакция из UPDATE 1 000 строк. В DELETE смотрим -journal внутри транзакции, в WAL — -wal после коммита (wal_autocheckpoint = 0, чтобы ничего не переносилось в базу). Размеры, полученные в опыте: в DELETE внутри транзакции -journal занимает 62 072 байт, что равно 512 + 15 × 4 104 (заголовок и 15 старых страниц по 4 + 4 096 + 4 байта), а после COMMIT файл удалён; база остаётся 5 947 392 байт. В WAL внутри транзакции -wal остаётся 5 990 512 байт, а после COMMIT растёт до 6 052 312, то есть на 61 800 = 15 × 4 120: те же 15 страниц, но уже новые.

Заголовок и кадры читаются из файла как есть, три INSERT после CREATE TABLE в автокоммите:

import sqlite3, struct
db = sqlite3.connect("hdr.db", isolation_level=None)
db.execute("PRAGMA journal_mode=WAL"); db.execute("PRAGMA wal_autocheckpoint=0")
db.execute("CREATE TABLE t(id INTEGER PRIMARY KEY, v TEXT)")
for i in range(3): db.execute("INSERT INTO t(v) VALUES (?)", ("x" * 50,))
b = open("hdr.db-wal", "rb").read()
magic, ver, psz, seq = struct.unpack(">IIII", b[:16])
print(f"заголовок WAL: magic={magic:#x} версия={ver} page_size={psz} checkpoint_seq={seq}; длина файла {len(b)} = 32 + {(len(b)-32)//4120} × 4120")
for i in range((len(b) - 32) // 4120):
    pgno, commit = struct.unpack(">II", b[32 + i*4120 : 32 + i*4120 + 8])
    print(f"кадр {i+1}: страница {pgno}, размер базы после коммита {commit}")

вывод
заголовок WAL: magic=0x377f0682 версия=3007000 page_size=4096 checkpoint_seq=0; длина файла 20632 = 32 + 5 × 4120
кадр 1: страница 1, размер базы после коммита 0
кадр 2: страница 2, размер базы после коммита 2
кадр 3: страница 2, размер базы после коммита 2
кадр 4: страница 2, размер базы после коммита 2
кадр 5: страница 2, размер базы после коммита 2

CREATE TABLE записал кадры 1 и 2, каждый INSERT — по одному кадру с новой копией страницы 2. Ненулевое число в поле «размер базы» отмечает последний кадр транзакции: только до него читатель считает WAL сохранённым. Индекс «страница → последний кадр» лежит в -shm; в него кадр попадает в walIndexAppend:

    /* Write the aPgno[] array entry and the hash-table slot. */
    nCollide = idx;
    for(iKey=walHash(iPage); sLoc.aHash[iKey]; iKey=walNextHash(iKey)){
      if( (nCollide--)==0 ) return SQLITE_CORRUPT_BKPT;
    }
    sLoc.aPgno[idx-1] = iPage;
    AtomicStore(&sLoc.aHash[iKey], (ht_slot)idx);

Источник: src/wal.c L1332-L1338, тег version-3.50.2. Номер страницы кладётся в массив и в хеш-таблицу общей памяти, поэтому читатель находит нужный кадр, не просматривая -wal. В режиме DELETE тот же сброс страниц делает pager_write_pagelist, у которого в начале стоит assert( !pagerUseWal(pPager) ) (src/pager.c L4412-L4418).

Опыт 3: 100 000 INSERT и число fdatasync

Каждый INSERT в автокоммите, значит, 100 000 коммитов. Режим и synchronous приходят аргументами:

import sqlite3, sys, time, os
mode, sync = sys.argv[1], sys.argv[2]
path = f"bench_{mode}_{sync}.db"
db = sqlite3.connect(path, isolation_level=None)
db.execute(f"PRAGMA journal_mode={mode}")
db.execute(f"PRAGMA synchronous={sync}")
db.execute("CREATE TABLE t(id INTEGER PRIMARY KEY, v TEXT)")
t = time.perf_counter()
for i in range(100_000):
    db.execute("INSERT INTO t(v) VALUES (?)", ("x" * 50,))
dt = time.perf_counter() - t
size = lambda s: os.path.getsize(path + s) if os.path.exists(path + s) else 0
print(f"{mode:6} {sync:6} {dt:6.2f} c  db={size(''):>8}  wal={size('-wal'):>8}  journal={size('-journal'):>5}")

for m in DELETE WAL; do for s in FULL NORMAL; do LD_PRELOAD=$PWD/count.so python3 bench.py $m $s; done; done

вывод
DELETE FULL     1.88 c  db= 5947392  wal=       0  journal=    0
fsync=0 fdatasync=300003
DELETE NORMAL   1.97 c  db= 5947392  wal=       0  journal=    0
fsync=0 fdatasync=200002
WAL    FULL     0.46 c  db= 5894144  wal= 4128272  journal=    0
fsync=0 fdatasync=100313
WAL    NORMAL   0.43 c  db= 5894144  wal= 4128272  journal=    0
fsync=0 fdatasync=312

На tmpfs время WAL в 4 раза меньше (0,46 против 1,88 с), хотя синхронизации ничего не стоят: в DELETE каждый коммит создаёт и удаляет -journal, в WAL дописывает кадр. Разница между FULL и NORMAL в секундах (1,88 и 1,97) лежит в шуме, а в счётчике она есть. На реальном диске цена коммита равна числу fdatasync на задержку одного: перемножь счётчик из таблицы на pg_test_fsync-подобный замер своего тома.

Почему WAL + NORMAL почти не синхронизирует, видно в sqlite3PagerSetFlags:

  pPager->walSyncFlags = (pPager->syncFlags<<2);
  if( pPager->fullSync ){
    pPager->walSyncFlags |= pPager->syncFlags;
  }

Источник: src/pager.c L3631-L3634, тег version-3.50.2. Младшие биты walSyncFlags управляют синхронизацией коммита и выставляются только при fullSync, старшие, всегда ненулевые при включённой синхронизации, — синхронизацией checkpoint (макросы WAL_SYNC_FLAGS и CKPT_SYNC_FLAGS в src/wal.h). Поэтому в WAL + NORMAL оставшиеся 312 вызовов относятся к checkpoint, а не к коммитам (по коду; разбивку по причинам опыт не делал). Цена: по документации PRAGMA synchronous, при потере питания последние коммиты могут не оказаться на диске, хотя база остаётся целой; в опыте проверена только сторона счётчика, обрыв питания не воспроизводился.

В выводе видно ещё одно число: -wal остановилась на 4 128 272 байт, то есть 32 + 1 002 × 4 120. Порог wal_autocheckpoint по умолчанию 1 000 страниц (следующий опыт), после checkpoint запись идёт с начала файла, и он не растёт дальше.

Опыт 4: рост -wal и checkpoint

Автоматический checkpoint отключён: 100 транзакций по 1 000 строк, затем ручные PASSIVE и TRUNCATE.

import sqlite3, os
p = "wal.db"
db = sqlite3.connect(p, isolation_level=None)
print(db.execute("PRAGMA journal_mode=WAL").fetchone(), db.execute("PRAGMA wal_autocheckpoint").fetchone())
db.execute("PRAGMA wal_autocheckpoint=0")
db.execute("CREATE TABLE t(id INTEGER PRIMARY KEY, v TEXT)")
sz = lambda s: os.path.getsize(p + s) if os.path.exists(p + s) else None
for b in range(100):
    db.execute("BEGIN")
    db.executemany("INSERT INTO t(v) VALUES (?)", [("x" * 50,)] * 1000)
    db.execute("COMMIT")
print("до checkpoint: db", sz(""), "wal", sz("-wal"), "shm", sz("-shm"))
print("checkpoint PASSIVE (busy, wal frames, moved):", db.execute("PRAGMA wal_checkpoint(PASSIVE)").fetchone())
print("после PASSIVE: db", sz(""), "wal", sz("-wal"))
print("checkpoint TRUNCATE:", db.execute("PRAGMA wal_checkpoint(TRUNCATE)").fetchone())
print("после TRUNCATE: db", sz(""), "wal", sz("-wal"), "shm", sz("-shm"))
db.close()
print("после close: wal", sz("-wal"), "shm", sz("-shm"))

вывод
('wal',) (1000,)
до checkpoint: db 4096 wal 7333632 shm 32768
checkpoint PASSIVE (busy, wal frames, moved): (0, 1780, 1780)
после PASSIVE: db 5947392 wal 7333632
checkpoint TRUNCATE: (0, 0, 0)
после TRUNCATE: db 5947392 wal 0 shm 32768
после close: wal None shm None

Все 100 коммитов остались в -wal: 1 780 кадров, 7 333 632 байт, а файл базы так и остался 4 096 байт. PASSIVE перенёс 1 780 кадров, база стала 5 947 392 байт (1 452 страницы по 4 096), но -wal не уменьшился: кадры лежат на месте до следующей записи. Файл сократил только TRUNCATE. -shm постоянно 32 768 байт, а при закрытии последнего соединения -wal и -shm удаляются.

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

• WAL выбирай для «много читателей, один писатель» на одной машине. Читатель и писатель не блокируют друг друга (опыт 1), а -shm — общая память процессов, поэтому все соединения должны быть на одном хосте; на сетевых файловых системах это условие не гарантируется (раздел о сетевых ФС в документации WAL).
• Писатель всё равно один в любом режиме. Нужен busy_timeout, а транзакции записи делай короткими и открывай их через BEGIN IMMEDIATE, если в них есть запись: апгрейд из чтения в запись возвращает SQLITE_BUSY без ожидания, как в статье про дедлоки.
• Ограничь WAL checkpoint-ом. -wal растёт, пока checkpoint не может пройти за самого старого читателя. Следи за размером -wal: в опыте 4 он дошёл до 7,3 МБ при базе 4 КБ.
• synchronous = NORMAL в WAL — осознанная сделка. 312 fdatasync вместо 100 313 в опыте 3 за риск потерять последние коммиты при отказе питания. Для денег и заказов оставляй FULL, для кэша и черновиков хватит NORMAL.
• Режим журнала живёт в файле, synchronous — в соединении. Проверка: после journal_mode=WAL и synchronous=NORMAL в закрытом соединении новое показало journal_mode = wal и synchronous = 2, то есть FULL по умолчанию.

Чек-лист

1. Выполни PRAGMA journal_mode; на своей базе и проверь, что режим именно тот, который ты ожидаешь.
2. В коде записи задай busy_timeout и BEGIN IMMEDIATE; повтор на SQLITE_BUSY ограничь по числу попыток.
3. Мониторь размер -wal рядом с базой и запускай PRAGMA wal_checkpoint(TRUNCATE) в тихое время, если файл разрастается.
4. Задавай synchronous в каждом соединении явно (FULL или NORMAL): оно не сохраняется в базе, а новое соединение получает FULL.
5. Убери за собой: rm -rf $D.