SQLite WAL параллельная запись, многопоточность и работа на сервере

SQLite часто воспринимают как базу данных для мобильных приложений, локальных программ или небольших прототипов. Для серьёзного серверного приложения, по распространённому мнению, обязательно нужны PostgreSQL или MySQL.

Но действительно ли это так? Можно ли использовать SQLite в production, обслуживать тысячи пользователей и одновременно обрабатывать множество запросов на чтение и запись?

Чтобы ответить на эти вопросы, нужно разобраться в режиме WAL, принципах работы с транзакциями и ограничениях SQLite.

Что такое WAL в SQLite

WAL (Write-Ahead Logging) — это режим журналирования с опережающей записью.

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

Допустим, база находится в файле app.db. При включении WAL рядом с ним обычно появляются ещё два файла:

• app.db-wal — журнал изменений;
  
• app.db-shm — вспомогательный файл для обмена информацией между процессами, работающими с базой.
  

После завершения транзакции новые данные уже доступны для чтения. Позже SQLite переносит накопленные изменения из WAL в основной файл базы. Этот процесс называется checkpoint.

Такой подход позволяет не заставлять читателей ждать, пока каждая запись будет перенесена в основной файл базы данных.

Подробнее механизм описан в официальной документации SQLite.

Как WAL позволяет читать и записывать данные одновременно

Представим сервер мобильного приложения, которым пользуются несколько тысяч человек.

Один пользователь открывает новости, второй читает обсуждение, третий отправляет сообщение, а четвёртый публикует новую новость.

В режиме WAL чтение и запись могут выполняться одновременно. Пока один запрос записывает изменения в журнал, другие запросы могут читать базу данных.

При этом читатели получают согласованный снимок данных. Если транзакция записи ещё не завершена, её изменения не будут видны другим соединениям до фиксации транзакции.

Это особенно полезно для серверных приложений, где запросы на чтение встречаются чаще, чем запросы на изменение данных.

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

SQLite допускает только одного активного писателя для конкретной базы. Если два запроса одновременно пытаются изменить данные, один из них получает возможность записывать первым, а второй должен дождаться освобождения блокировки.

Таким образом, WAL позволяет одновременно читать и записывать данные, но сами операции записи в одну базу выполняются последовательно.

Многопоточность и параллельная запись — в чём разница

Эти понятия легко перепутать, хотя они описывают разные вещи.

Многопоточность означает, что программа может выполнять задачи в нескольких потоках. SQLite поддерживает многопоточную работу, но конкретные правила использования соединений зависят от выбранного режима потокобезопасности.

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

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

SQLite поддерживает многопоточную работу и конкурентный доступ. Однако в стандартной реализации записи в одну базу сериализуются: одновременно записывать может только одна транзакция.

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

Если транзакции короткие, а интенсивность записи умеренная, такое ограничение может практически не мешать работе приложения.

Что происходит при одновременных запросах на запись

Предположим, два пользователя почти одновременно отправили сообщения.

Первый запрос начал транзакцию и получил возможность записывать данные. Второй запрос тоже хочет записать сообщение, но должен дождаться, пока первый завершит транзакцию.

Для ожидания блокировок в SQLite существует настройка busy_timeout:

PRAGMA busy_timeout = 5000;

Она позволяет ждать освобождения блокировки до пяти секунд, прежде чем SQLite вернёт ошибку SQLITE_BUSY.

Однако это не универсальное решение всех конфликтов.

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

В таком случае SQLite может вернуть SQLITE_BUSY_SNAPSHOT. Простое ожидание не поможет: приложению необходимо отменить транзакцию и повторить всю операцию с актуальными данными.

Чтобы лучше управлять такими ситуациями, нужно понимать, как начинать транзакции.

Зачем нужен BEGIN IMMEDIATE

По умолчанию команда BEGIN начинает транзакцию в режиме DEFERRED. Это означает, что право на запись не резервируется сразу: транзакция может сначала читать данные и только потом попытаться их изменить.

Для транзакций, которые точно будут записывать данные, часто лучше использовать BEGIN IMMEDIATE.

Например:

BEGIN IMMEDIATE;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;

В этом примере мы переводим деньги между двумя счетами. Команда BEGIN IMMEDIATE позволяет получить блокировку на запись в начале транзакции, ещё до выполнения запросов изменения данных.

Если другой писатель уже работает с базой, SQLite будет ожидать освобождения блокировки в пределах настроенного busy_timeout. Если получить блокировку не удастся, ошибка возникнет в начале транзакции, а не после выполнения её основной работы.

Это особенно важно для операций, в которых сначала читаются данные, а затем на их основе принимается решение об изменении базы.

Например, приложение проверяет наличие товара на складе, рассчитывает остаток и затем уменьшает его. Если между проверкой и записью данные изменятся, результат может оказаться неверным. Начало такой операции с BEGIN IMMEDIATE помогает избежать конфликта из-за устаревшего снимка базы.

При этом BEGIN IMMEDIATE не следует использовать без разбора для каждой транзакции. Если транзакция только читает данные, обычного чтения достаточно. А если транзакция пишет, но её логика не зависит от предварительного чтения, выбор режима нужно делать с учётом конкретной операции.

Практическое правило: если транзакция сначала читает данные, а затем должна на их основе записать изменения, рассмотрите BEGIN IMMEDIATE. Держите транзакции короткими и не выполняйте внутри них длительные вычисления или сетевые запросы.

В режиме WAL команды BEGIN IMMEDIATE и BEGIN EXCLUSIVE в отношении блокировок работают одинаково.

Подробнее: официальная документация SQLite о транзакциях.

Как включить WAL

Для включения режима достаточно выполнить запрос:

PRAGMA journal_mode = WAL;

Режим сохраняется в настройках базы данных и остаётся активным после перезапуска приложения.

Для типового серверного приложения можно также настроить ожидание блокировок и проверку внешних ключей:

PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;

Первая настройка задаёт время ожидания блокировок. Вторая включает проверку внешних ключей для текущего соединения.

Эти настройки не заменяют друг друга: busy_timeout помогает дождаться освобождения блокировки, а BEGIN IMMEDIATE позволяет заранее получить право на запись, если транзакция будет изменять данные.

Есть и ещё одна настройка, влияющая на производительность:

PRAGMA synchronous = NORMAL;

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

Это может заметно ускорить запись. Однако при аварийном отключении питания или жёсткой перезагрузке последние подтверждённые транзакции могут быть потеряны.

Важно: речь именно о режиме WAL. При synchronous = NORMAL SQLite сохраняет гарантии целостности базы при соблюдении условий работы операционной системы, файловой системы и оборудования, но не гарантирует сохранность последних изменений после потери питания.

Если сохранность каждой подтверждённой транзакции критична, стоит рассмотреть synchronous = FULL и измерить производительность на реальном оборудовании.

Можно ли использовать SQLite на сервере

Да. SQLite подходит для многих серверных приложений, если характер нагрузки соответствует её модели работы.

В отличие от PostgreSQL и MySQL, SQLite не требует отдельного сервера базы данных. Её движок работает внутри процесса приложения, а данные хранятся в файле.

Архитектура может выглядеть так:

Мобильное приложение
        |
        v
      HTTP API
        |
        v
    SQLite + WAL
        |
        v
    app.db на диске

Мобильные приложения обращаются к API, а сервер самостоятельно работает с базой данных. Пользователям не нужен прямой доступ к файлу базы.

Для небольшого стартапа такая архитектура может оказаться проще отдельного сервера PostgreSQL. Меньше компонентов — проще развёртывание, обновление и обслуживание. Не нужно отдельно администрировать сервер СУБД, хотя резервное копирование, мониторинг и контроль дискового пространства всё равно необходимы.

Сколько пользователей выдерживает SQLite

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

Разработчики SQLite приводят ориентир: сайты с нагрузкой менее 100 000 HTTP-запросов в день, как правило, должны нормально работать на SQLite. Они подчёркивают, что это консервативная оценка, а не жёсткий предел: продемонстрирована работа и с нагрузкой в десять раз выше.

Например, официальный сайт SQLite использовал SQLite для обработки сотен тысяч HTTP-запросов в день.

При этом HTTP-запрос и запрос к базе данных — не одно и то же. Один запрос к сайту может выполнять множество SQL-операций, а другой вообще не обращаться к базе. Поэтому эти цифры нельзя напрямую переносить на любое приложение.

Для стартапа правильнее начать с SQLite, измерять реальную нагрузку и переходить на другую СУБД при появлении конкретных ограничений, а не только из-за роста числа зарегистрированных пользователей.

Источник: Appropriate Uses For SQLite.

Примеры серверных проектов на SQLite

SQLite используется не только в локальных программах.

PocketBase — backend-платформа, использующая SQLite для хранения данных и предоставляющая API для приложений. Это пример готового серверного решения на базе SQLite.

Litestream — инструмент для репликации SQLite в удалённое хранилище. Он помогает организовать резервное копирование и восстановление базы.

Datasette — инструмент для публикации и исследования данных SQLite через веб-интерфейс. Он показывает, как SQLite может использоваться в серверных приложениях, где преобладает чтение.

Эти проекты не означают, что SQLite подходит для любой нагрузки, но демонстрируют, что её использование на сервере — вполне реальный подход, а не только временное решение для прототипа.

Ограничения SQLite в production

Несмотря на преимущества, SQLite подходит не для всех задач.

Высокая конкуренция за запись

Если множество запросов постоянно изменяют данные, единственная блокировка на запись может стать узким местом. Если транзакции регулярно ждут освобождения блокировки, стоит проанализировать их длительность и частоту записи.

Если после оптимизации нагрузка всё ещё превышает возможности SQLite, PostgreSQL может оказаться более подходящим решением.

Несколько серверов приложения и сетевые файловые системы

В режиме WAL все процессы, работающие с одной базой, должны находиться на одном компьютере. WAL требует, чтобы они могли использовать общую разделяемую память, поэтому этот режим не работает на сетевых файловых системах, обеспечивающих доступ к одной базе с разных машин.

Это относится и к попыткам разместить файл SQLite на NFS и использовать его одновременно с нескольких серверов. Сам факт, что Docker-контейнеры видят один общий том, не означает, что такой способ работы безопасен: если том реализован через сетевую файловую систему, ограничения WAL сохраняются.

Для нескольких серверов приложения лучше использовать PostgreSQL или другую СУБД с клиент-серверной архитектурой.

Документация: SQLite WAL — ограничения.

Резервное копирование

При включённом WAL нельзя бездумно копировать только основной файл базы данных, пока приложение продолжает работать. Часть актуальных данных может находиться в журнале.

Один из простых способов создать согласованную копию работающей базы — использовать команду:

VACUUM INTO 'backup.db';

Она создаёт новый файл базы данных, содержащий согласованный снимок исходных данных. При этом файл backup.db должен отсутствовать или быть пустым.

У этого способа есть ограничение: если выполнение команды прервётся из-за сбоя, резервная копия может оказаться неполной. Поэтому для регулярного резервного копирования работающей базы также стоит рассмотреть SQLite Online Backup API, который позволяет создавать копию постепенно, не блокируя базу на всё время операции.

Резервную копию необходимо хранить отдельно от исходной базы и периодически проверять возможность восстановления.

Источники: VACUUM INTO, SQLite Online Backup API.

Долгие транзакции и рост WAL

Если чтение долго удерживает снимок базы, SQLite может не завершить перенос изменений из WAL в основной файл. Из-за этого журнал может расти, а производительность — снижаться.

Поэтому важно не только включить WAL, но и следить за длительностью транзакций и размером журнала.

Когда переходить на PostgreSQL

Универсального количества пользователей, после которого SQLite перестаёт подходить для проекта, не существует.

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

О переходе на PostgreSQL стоит задуматься, когда:

• ошибки SQLITE_BUSY возникают регулярно;
  
• запросы на запись заметно задерживаются из-за блокировок;
  
• приложению требуется высокая интенсивность конкурентной записи;
  
• необходимо масштабировать приложение на несколько серверов с общей базой;
  
• требуются репликация и автоматическое переключение между серверами.
  

При этом переход на PostgreSQL не должен быть самоцелью. Если SQLite обеспечивает необходимую производительность и надёжность, смена СУБД может лишь увеличить сложность инфраструктуры.

Итог

SQLite — полноценная реляционная СУБД, которую можно использовать в production, в том числе на сервере небольшого стартапа.

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

Чтобы эффективно использовать SQLite, важно держать транзакции короткими, правильно работать с блокировками, применять BEGIN IMMEDIATE для подходящих операций записи, следить за размером WAL и организовать резервное копирование.

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

database