SQLite WAL: параллельные чтения и записи без блокировок

Стандартный режим и его ограничения

По умолчанию SQLite использует откатный журнал (rollback journal). Когда вы что-то записываете, SQLite копирует исходные страницы в файл -journal, меняет основную базу, а после коммита удаляет журнал. Если процесс упадёт посреди записи, журнал проигрывается в обратном порядке - и незавершённое изменение откатывается.

Просто и надёжно, но есть один болезненный момент: писатели и читатели дерутся за один и тот же файл. Пока писатель держит блокировку базы, ни один читатель не может начать новую транзакцию. Пока активны читатели - ждёт уже писатель. На нагруженном приложении (скажем, веб-сервер с парой одновременных запросов) ошибки SQLITE_BUSY посыплются быстрее, чем хотелось бы.

Режим WAL это меняет.

Что на самом деле делает WAL

Write-ahead logging переворачивает схему с ног на голову. Вместо того чтобы править основной файл базы на месте, писатель дописывает закоммиченные страницы в отдельный файл с суффиксом -wal. Читатели по-прежнему читают основной файл, но дополнительно заглядывают в WAL - вдруг там есть более свежие версии нужных им страниц.

Итог: писатель и сколько угодно читателей могут работать одновременно. Каждый читатель видит согласованный снимок данных на момент начала своей транзакции, а писатель спокойно дописывает в WAL, не трогая то, на что смотрят читатели.

PRAGMA journal_mode=WAL;

Одной этой директивы достаточно, чтобы переключить базу. Режим прилипает к файлу - он записывается в заголовок, так что все последующие подключения автоматически подхватят WAL. Гонять эту команду на каждом коннекте не нужно: достаточно выполнить её один раз при инициализации базы (или в миграционном скрипте).

PRAGMA возвращает новый режим. Если в ответе wal - всё ок. Если что-то другое, скорее всего, файловая система не поддерживает разделяемую память (об этом ниже).

Как включить WAL и проверить, что он работает

Текущий режим можно посмотреть в любой момент:

PRAGMA journal_mode=WAL;
PRAGMA journal_mode;

CREATE TABLE messages (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO messages (body) VALUES ('привет'), ('мир');
SELECT * FROM messages;

Первый вызов включает WAL и возвращает новый режим. Второй (уже без =) просто опрашивает текущее значение. После этого в каталоге, где лежит messages.db, при активности появятся три файла: messages.db, messages.db-wal и messages.db-shm. Последние два возникают и исчезают в зависимости от того, открыты ли соединения.

Файлы -wal и -shm в SQLite

Вместе с режимом WAL появляются два дополнительных файла, и стоит понимать, за что каждый отвечает:

• -wal хранит зафиксированные транзакции, которые ещё не были перенесены в основную базу. Файл растёт по мере записи и сжимается (или обнуляется) во время checkpoint'а.
• -shm - это файл разделяемой памяти. По сути, индекс по WAL: благодаря ему все соединения видят согласованную картину того, где какая страница лежит, и не приходится сканировать WAL при каждом запросе.

Практический вывод: никогда не копируйте базу в режиме WAL, забирая только файл .db. Свежие данные лежат в -wal, и без него копия окажется устаревшей или вовсе битой. Либо копируйте все три файла, когда никто не пишет, либо - что гораздо надёжнее - используйте SQLite backup API (об этом в следующей главе).

Параллельная запись в SQLite: один писатель, много читателей

Режим WAL не даёт параллельной записи. SQLite по-прежнему сериализует пишущие транзакции: в любой момент времени блокировку записи держит ровно одна. Изменилось другое - записи больше не блокируют чтение, а чтения не блокируют запись.

Поэтому типичное веб-приложение на WAL ведёт себя так:

• Эндпоинты, активно читающие данные, работают параллельно без конкуренции.
• Эндпоинты с записью выстраиваются в небольшую очередь между собой, но не мешают читателям.
• Долгие читающие запросы (аналитика, экспорт) не заставляют писателей ждать.

Если два соединения попытаются писать одновременно, второе получит SQLITE_BUSY. Лечится это обычно адекватным busy timeout - нужно попросить SQLite подождать немного, прежде чем сдаваться:

CREATE TABLE messages (id INTEGER PRIMARY KEY, body TEXT);

PRAGMA busy_timeout=5000;

BEGIN IMMEDIATE;
INSERT INTO messages (body) VALUES ('запись в очереди');
COMMIT;

SELECT * FROM messages;

busy_timeout=5000 означает: «если база залочена, подожди до 5 секунд, прежде чем выдать ошибку». В паре с WAL этого хватает, чтобы пережить ту конкуренцию, с которой реально сталкивается большинство приложений. А форма BEGIN IMMEDIATE берёт блокировку на запись сразу при старте транзакции, а не при первой операции записи - так мы обходим целый класс дедлоков, когда два соединения одновременно собираются писать.

Checkpoint в SQLite: как WAL сворачивается обратно

Файл WAL не может расти бесконечно. Checkpoint - это процесс, при котором закоммиченные страницы из WAL переносятся в основной файл базы, после чего WAL обнуляется.

SQLite запускает checkpoint автоматически, когда WAL переваливает примерно за 1000 страниц (значение wal_autocheckpoint по умолчанию). В большинстве проектов это можно вообще не трогать. Но если хочется подкрутить параметры или запустить checkpoint вручную:

PRAGMA wal_autocheckpoint;
PRAGMA wal_autocheckpoint=2000;

PRAGMA wal_checkpoint(TRUNCATE);

У wal_checkpoint есть несколько режимов:

• PASSIVE - выполняет контрольную точку настолько, насколько это возможно, не мешая ни читателям, ни писателям. Режим по умолчанию.
• FULL - ждёт, пока активные писатели закончат работу, и затем фиксирует в основной БД всё, что было закоммичено.
• RESTART - то же, что и FULL, плюс не даёт новым читателям продолжать работать со старым WAL.
• TRUNCATE - то же, что и RESTART, плюс ужимает файл WAL обратно до нуля байт.

На сервере, как правило, вызывать это вручную не нужно. А вот если вы пишете десктопное приложение и хотите, чтобы при выходе размеры файлов оставались аккуратными, имеет смысл делать TRUNCATE-чекпоинт перед закрытием последнего соединения.

Прагмы, которые хорошо сочетаются с WAL

Сам по себе режим WAL - это уже неплохо. Но в продакшене обычно к нему добавляют ещё пару настроек:

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA busy_timeout=5000;
PRAGMA foreign_keys=ON;

Короткая экскурсия по настройкам:

• synchronous=NORMAL - это рекомендованный спутник режима WAL. Он защищает от падений приложения и краха ОС; потерять последние транзакции можно только при отключении питания в крайне неудачный момент, но даже тогда база останется целостной. Значение по умолчанию FULL безопаснее, но заметно медленнее.
• busy_timeout мы разобрали выше.
• foreign_keys=ON к WAL отношения не имеет, но включать его стоит на каждом соединении - SQLite по умолчанию не проверяет внешние ключи ради обратной совместимости.

Эти настройки действуют в рамках одного соединения (кроме journal_mode - он сохраняется в файле). Выставляйте их сразу после открытия соединения в коде приложения.

Когда WAL - не лучший выбор

WAL - рекомендация по умолчанию, но в нескольких случаях от него лучше отказаться:

• Сетевые файловые системы. WAL опирается на разделяемую память (mmap) между процессами, работающими с базой. NFS, SMB и им подобные надёжно это не поддерживают. Если база лежит на сетевой шаре - оставайтесь на rollback journal, а ещё лучше не держите SQLite на сетевом диске вовсе.
• Носители только для чтения. WAL должен писать файлы -wal и -shm. База на CD-ROM или аналогичном носителе обязана использовать режим без записи (или открываться только на чтение через mode=ro).
• Пакетные задачи с одним писателем и без параллельных читателей. WAL вреда не нанесёт, но и пользы тоже. Стандартного rollback journal вполне достаточно.

Для 95% приложений - веб-бэкендов, десктопных и мобильных программ, встраиваемых устройств с локальным хранилищем - WAL подходит идеально.

Боевая конфигурация

Вот как обычно выглядит SQLite в проде - набор прагм, который можно прямо взять и запустить:

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA busy_timeout=5000;
PRAGMA foreign_keys=ON;
PRAGMA temp_store=MEMORY;

CREATE TABLE IF NOT EXISTS events (
    id INTEGER PRIMARY KEY,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    payload TEXT NOT NULL
);

INSERT INTO events (payload) VALUES ('login'), ('signup'), ('purchase');
SELECT id, created_at, payload FROM events;

temp_store=MEMORY держит временные таблицы и индексы в оперативной памяти, а не на диске - небольшой, но бесплатный выигрыш, если памяти не жалко.

Пропишите всё это один раз при создании подключения в коде инициализации БД - и вы покрыли большую часть того, что нужно SQLite-приложению, чтобы нормально жить под параллельной нагрузкой.

Дальше: резервное копирование и восстановление

Теперь рядом с вашей базой лежат файлы -wal и -shm, и просто скопировать основной файл - уже не вариант для надёжного бэкапа. В следующей главе разберём, как правильно бэкапить живую базу SQLite: команду .backup, online backup API и то, как снять консистентный снимок, не останавливая приложение.