Потолок конкурентного доступа к SQLite: сколько писателей, прежде чем всё встанет

MAATRIX

Рано или поздно у любого проекта на SQLite возникает один и тот же вопрос: почему при росте нагрузки запросы на запись вдруг начинают ждать друг друга, а в логах появляется database is locked. Ответ прост и почти всегда неожиданен для тех, кто привык к PostgreSQL или MySQL: SQLite физически не умеет писать в базу из двух мест одновременно — ни в каком режиме. Разберём, почему так устроено, чем journal-mode отличается от WAL, где реально проходит граница применимости SQLite для многопользовательской записи и как понять, что пора переезжать на клиент-серверную СУБД.

Почему SQLite вообще не про параллельную запись

SQLite — не сервер баз данных, а библиотека, которая линкуется прямо в процесс приложения и работает с одним файлом на диске через обычные системные вызовы read/write/fsync. У неё нет демона, который принимает соединения по сети или через сокет и сам решает, в каком порядке выполнять транзакции нескольких клиентов. Вместо этого координацию берёт на себя файловая блокировка (flock/fcntl — в зависимости от ОС) поверх самого файла базы.

Из этого прямо следует ключевое ограничение: сколько бы процессов или потоков ни открыли один и тот же файл .db, писать в него в любой момент времени может только один из них. Это не баг, а осознанное архитектурное решение авторов SQLite: библиотека жертвует параллелизмом записи ради простоты, нулевой конфигурации и отсутствия отдельного серверного процесса. SQLite не заменяет клиент-серверную СУБД для сценариев с высокой конкурентностью записи — она решает другой класс задач: встраиваемое хранилище, конфиги, локальные кеши, мобильные и десктопные приложения, тестовые окружения.

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

Traditional rollback journal: блокировка всей базы целиком

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

Проверить, какой режим сейчас активен, можно прямо из sqlite3:

PRAGMA journal_mode;
-- delete | truncate | persist | memory | wal | off

Значение delete (журнал удаляется после коммита) — это классический rollback journal и вариант по умолчанию для новой базы, если вы ничего не меняли.

В этом режиме блокировки проходят через несколько уровней (UNLOCKED → SHARED → RESERVED → PENDING → EXCLUSIVE), но итог для практики один: писатель, чтобы закоммитить транзакцию, должен получить EXCLUSIVE-блокировку на весь файл базы. Пока она удержана — не может начаться ни одна новая читающая транзакция, и уж тем более никакая другая запись. Читатели, уже открывшие транзакцию до этого момента, доработают на своей версии данных, но новые обращения к базе встанут в очередь.

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

/* или в sqlite3 CLI: */
PRAGMA busy_timeout = 5000; /* мс ожидания перед возвратом SQLITE_BUSY */

Без busy_timeout (по умолчанию 0) первое же столкновение с занятой блокировкой вернёт ошибку немедленно, а не подождёт — это частая причина внезапных database is locked в приложениях, которые никогда не настраивали этот параметр.

WAL: как Write-Ahead Logging разводит читателей и писателя

Режим WAL, появившийся как альтернатива journal ещё в достаточно старых версиях SQLite и ставший де-факто стандартом для нагруженных сценариев, меняет саму механику записи. Вместо того чтобы менять страницы базы на месте и вести журнал отката, SQLite в WAL-режиме дописывает изменённые страницы в отдельный файл имя_базы.db-wal — последовательно, в конец. Сама база данных при этом не трогается до момента checkpoint.

Включается WAL один раз на файл базы (настройка сохраняется в самом файле):

PRAGMA journal_mode = WAL;

Ключевое отличие для конкурентности: читатели в WAL-режиме работают с консистентным снапшотом, собранным из основного файла базы плюс нужной части WAL-файла, и не блокируются писателем — они просто не видят ещё не закоммиченные или не подтверждённые изменения. Писатель, в свою очередь, не блокируется читателями. Это снимает главную боль journal-mode: чтение и запись перестают конкурировать за одну и ту же блокировку.

Но вот что не меняется: писателей в любой момент времени по-прежнему может быть только один. WAL решает проблему «читатели против писателя», а не «писатель против писателя». Если два процесса одновременно попытаются начать транзакцию записи, второй получит SQLITE_BUSY (или подождёт busy_timeout) — как и в journal-mode, просто конкуренция теперь только между писателями.

Есть и цена, которую WAL берёт за это удобство — checkpoint. WAL-файл не растёт бесконечно: периодически (по умолчанию — когда он достигает примерно 1000 страниц, это регулируется wal_autocheckpoint) SQLite переносит накопленные изменения из WAL обратно в основной файл базы и усекает WAL. Checkpoint требует более широкой блокировки и на короткое время может подтормозить активных писателей и (при PASSIVE-checkpoint — минимально) читателей, всё ещё работающих со старой частью WAL:

PRAGMA wal_autocheckpoint = 1000; -- страниц WAL до авточекпоинта
PRAGMA wal_checkpoint(TRUNCATE);  -- принудительный checkpoint с усечением файла вручную

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

Ещё одно ограничение WAL: он плохо работает на сетевых файловых системах (NFS и подобных), потому что полагается на shared-memory файл -shm для координации процессов, а это требует надёжного mmap между процессами на одной машине. На сетевых томах документация SQLite прямо рекомендует не использовать WAL, а часто и SQLite вовсе.

Journal vs WAL: сравнение по существу

Вывод простой: WAL почти всегда лучше для смешанной нагрузки с преобладанием чтения — он не бесплатен (лишние файлы, checkpoint, зависимость от локальной ФС), но снимает самое болезненное ограничение journal-mode. Однако ни один из режимов не решает проблему нескольких одновременных писателей — она встроена в саму модель однофайловой базы с блокировкой на уровне файла.

Где реально проходит граница применимости SQLite

Граница определяется не объёмом данных (SQLite спокойно тянет базы на десятки и сотни гигабайт) и не количеством читателей (их могут быть сотни без проблем), а характером конкурентной записи. Ориентиры без точных цифр — они у вас будут свои и зависят от диска, длины транзакций и паттерна доступа:

• Один процесс/поток — единственный писатель, читателей может быть много. Десктопное или мобильное приложение, CLI-утилита, локальный кеш конфигурации сервиса. SQLite в WAL-режиме работает прекрасно, без проблем с конкурентностью.
• Несколько писателей, но пишут редко и короткими транзакциями. Несколько фоновых воркеров, которые раз в несколько секунд пишут по одной строке. busy_timeout в разумных пределах (сотни миллисекунд — секунды) сглаживает редкие столкновения, приложение их даже не замечает.
• Несколько писателей, пишущих часто и/или длинными транзакциями. Зона риска: очередь на запись растёт быстрее, чем рассасывается, SQLITE_BUSY всплывает даже с адекватным таймаутом, задержка записи растёт с числом writer'ов. SQLite ещё технически работает, но уже требует дисциплины — короткие транзакции, батчинг, единая очередь записи на уровне приложения.
• Много независимых процессов/инстансов приложения пишут напрямую в общий файл. Типичный антипаттерн — несколько реплик веб-приложения за балансировщиком с общей SQLite-базой на сетевом или общем диске. Здесь SQLite упирается сразу в два ограничения: последовательность записи и (если диск сетевой) ненадёжность WAL. Правильный ответ тут — не тюнинг SQLite, а смена архитектуры хранения.

Полезная практика — не гадать, а смоделировать свою нагрузку записи заранее (несколько параллельных writer'ов с реалистичной частотой и размером транзакций) и посмотреть на распределение задержек и частоту SQLITE_BUSY, а не полагаться на общие ориентиры из статей.

Что можно сделать, чтобы отодвинуть потолок SQLite

Прежде чем переезжать на другую СУБД, стоит выжать то, что даёт сама SQLite, — это часто откладывает проблему надолго или снимает её полностью:

• Включить WAL, если ещё не включён, — самый дешёвый и эффективный шаг для смешанной нагрузки чтение/запись.
• Сериализовать запись на уровне приложения — вместо того чтобы позволять N потокам конкурировать за блокировку файла, направить всю запись через одну очередь/один поток-писатель внутри процесса. Это убирает SQLITE_BUSY как класс проблемы: конкуренции на уровне файла больше нет, есть только внутренняя очередь.
• Батчить транзакции. Тысяча отдельных INSERT, каждый в своей транзакции, — это тысяча циклов блокировки и (в non-WAL режиме) тысяча fsync. Обернуть их в одну транзакцию BEGIN ... COMMIT — типичное ускорение на порядки, потому что fsync и захват блокировки происходят один раз, а не на каждую строку.
• Настроить busy_timeout осознанно, а не оставлять 0, но помнить: это сглаживает редкие столкновения, а не решает системную перегрузку записью.
• Проверить synchronous. FULL даёт максимальную защиту от сбоя питания ценой дополнительных fsync; NORMAL в WAL-режиме — обычно разумный компромисс (риск повреждения самой базы почти исключён, в худшем случае теряются последние закоммиченные, но не прошедшие checkpoint транзакции). О том, насколько диск и его кеш вообще честно сообщают об fsync, — в статье Барьеры записи и кеш диска, который врёт о сохранности ваших данных.
• Разнести чтение и запись физически, если это оправдано архитектурой: писатель — единственный процесс, а остальные сервисы читают через WAL-снапшот или периодическую репликацию файла.

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

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

Переезд на клиент-серверную СУБД оправдан не потому, что «так положено для продакшена» — это миф, SQLite вполне продакшен-грейд инструмент для своего класса задач. Оправдан он тогда, когда возникает хотя бы один из следующих признаков:

• Несколько процессов/серверов пишут в одну логическую базу одновременно, и это норма нагрузки, а не редкость. PostgreSQL и MySQL спроектированы как серверы, принимающие параллельные соединения от множества клиентов и разруливающие конкуренцию на уровне строк (row-level locking) или через MVCC, а не на уровне всего файла.
• Транзакции записи достаточно длинные или частые, чтобы очередь SQLITE_BUSY не рассасывалась даже после сериализации и батчинга. Если запись уже сведена к одной очереди в приложении, а она всё равно не успевает за входящим потоком — это физический потолок пропускной способности одного писателя, а не проблема конфигурации.
• Нужна репликация, отказоустойчивость или горизонтальное масштабирование чтения на несколько машин. У SQLite нет встроенной сетевой репликации между инстансами — только сторонние надстройки поверх WAL для нишевых сценариев. PostgreSQL и MySQL несут это из коробки.
• Приложение работает как несколько независимых реплик за балансировщиком, и всем им нужен консистентный доступ к общим данным без единой точки записи на диске одного сервера.
• Нужны сетевой доступ к базе с нескольких машин, права доступа на уровне ролей, партиционирование больших таблиц или другие возможности серверной СУБД, которых у файловой библиотеки нет по архитектуре.

Если ни один из этих пунктов не про ваш проект — переезд с SQLite, скорее всего, добавит операционной сложности (отдельный процесс, сетевые задержки, права доступа, бэкапы средствами СУБД) без реальной выгоды. Сравнение серверных СУБД между собой, если решение о переезде принято, — в статье PostgreSQL или MySQL: что выбрать для сервера, а практические грабли миграции — в статье С MySQL на PostgreSQL: подводные камни. После переезда стоит сразу прикинуть, сколько подключений выдержит новая СУБД на вашем сервере — методика измерения в статье Сколько подключений к MySQL до свопа.

Частые вопросы