SQLite в бизнес-приложениях на C# — режим WAL, эксклюзивная блокировка, защита от повреждений и выбор между EF Core и голым ADO.NET

В предыдущей статье «Как выбрать место хранения данных для Windows-приложения» я писал, что для растущих бизнес-данных и истории первый кандидат — SQLite. С этим выбором всё ясно, но когда доходит до реальной интеграции, возникают новые сомнения. В консультациях часто слышим: «поиск SQLite в NuGet выдаёт несколько пакетов, и непонятно, какой ставить», «всё вроде работает, но иногда выскакивает database is locked. Спасаемся повторными попытками — но правильно ли это?», «достаточно ли для резервного копирования просто скопировать файл БД?»

Все эти вопросы неизбежно возникают, если эксплуатировать SQLite в бизнес-приложении несколько лет, и, если продумать их на этапе первоначального проектирования, дальше проблем не будет. В этой статье, отталкиваясь от Microsoft.Data.Sqlite, разберём весь набор пунктов, которые я каждый раз проверяю на ревью проектирования: выбор библиотеки, строку подключения и пулинг, устройство режима WAL, отношение к SQLITE_BUSY, ловушки сопоставления типов, защиту от повреждений и резервное копирование, а также выбор между EF Core и голым ADO.NET.

1. Сначала вывод

• Для новой разработки базовый выбор библиотеки — Microsoft.Data.Sqlite (или построенный на нём SQLite-провайдер EF Core). Это другая библиотека, несовместимая с System.Data.SQLite ни по строке подключения, ни по деталям поведения, поэтому всегда обращайте внимание, для какой из них написан найденный в сети пример.¹
• Включайте режим WAL начиная с самого первого релиза. Он повышает параллелизм чтения и записи, и бо́льшая часть случаев database is locked исчезает. Настройка сохраняется в самом файле БД, но не работает на сетевом общем ресурсе.²
• database is locked (SQLITE_BUSY) — это не повод «добавить обработку ошибок», а сигнал пересмотреть проектирование. Microsoft.Data.Sqlite автоматически повторяет попытку вплоть до тайм-аута (по умолчанию 30 секунд)³, но настоящее решение — свести путь записи к одному.
• При большом количестве мелких INSERT простое объединение их в явную транзакцию ускоряет работу на два-три порядка. Если кажется, что «SQLite тормозит», в первую очередь подозревайте размер коммита.
• У SQLite фактически всего четыре типа — INTEGER / REAL / TEXT / BLOB, а DateTime, Guid и decimal хранятся как TEXT. Особенно decimal: сравнение и сортировка подчиняются строковым правилам, поэтому денежные суммы безопаснее хранить как целые числа в минимальной единице валюты.⁴
• Для резервного копирования простое копирование файла во время работы запрещено. Используйте VACUUM INTO или Backup API (SqliteConnection.BackupDatabase). Вручную удалять файлы -wal / -shm тоже строго запрещено.⁵
• Обычный SQLite не поддерживает шифрование. Параметр Password в строке подключения действует только при подключении нативной библиотеки семейства SQLCipher⁶; для небольшого объёма конфиденциальных данных сначала рассмотрите защиту через DPAPI, а не шифрование всей БД.

2. Выбор библиотеки — похожие названия, разное содержимое

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

Microsoft.Data.Sqlite поддерживает команда EF Core, и он же служит основой для SQLite-провайдера EF Core.¹ Поскольку нативный бинарник (само ядро SQLite) входит в NuGet-пакет, отдельного распространения на клиентские ПК не требуется, а различия x86/x64/ARM64 берёт на себя NuGet.

Стоит учитывать, что информация, написанная для System.Data.SQLite, не подходит напрямую. Поскольку эта библиотека используется давно, в интернете масса примеров, рассчитанных именно на System.Data.SQLite, и вы столкнётесь со следующими несовместимостями.

• Строки подключения несовместимы. Такие ключевые слова, как Version=3, UseUTF16Encoding или меняющий формат даты DateTimeFormat, в Microsoft.Data.Sqlite не существуют, и их указание вызывает исключение. Поддерживаемые ключевые слова — весьма короткий список: Data Source / Mode / Cache / Password / Foreign Keys / Default Timeout / Pooling и ещё несколько.⁶
• Обработка типов отличается. Например, Guid по умолчанию хранится как BLOB в System.Data.SQLite, а в Microsoft.Data.Sqlite — как TEXT. В переходный период, когда один и тот же файл БД читают и пишут обе библиотеки, эта разница проявляется как несогласованность данных.

Microsoft.Data.Sqlite намеренно сделан тонким: у него нет собственных преобразований типов или удобных функций, зато официальная документация SQLite применима напрямую. Если существующее приложение стабильно работает на System.Data.SQLite, форсировать миграцию не обязательно, но новый код стоит писать на Microsoft.Data.Sqlite, а если миграция всё же нужна — принцип таков: сначала выявить перечисленные выше несовместимости как пункты плана миграции.

3. Соединения и строка подключения — пулинг держит файл открытым

Базовая форма строки подключения — это просто путь к файлу. На практике важно обращать внимание на Mode и Pooling.⁶

using Microsoft.Data.Sqlite;

var builder = new SqliteConnectionStringBuilder
{
    DataSource = dbPath,
    Mode = SqliteOpenMode.ReadWriteCreate  // Значение по умолчанию: создать, если файла нет
};
using var conn = new SqliteConnection(builder.ConnectionString);
conn.Open();

• Mode: по умолчанию ReadWriteCreate (создать, если отсутствует). Для случаев вроде распространения справочной БД только для чтения указание ReadOnly предотвращает запись из-за ошибок в коде или неверных действий пользователя.
• Cache: обычно оставляйте по умолчанию. В документации прямо указано, что Cache=Shared не рекомендуется использовать вместе с режимом WAL, поэтому при подходе этой статьи, ориентированном на WAL, этот параметр не нужен.⁶
• Password: если указать этот параметр, сразу после подключения отправляется PRAGMA key, но стандартная нативная библиотека не поддерживает шифрование, поэтому ничего не происходит.⁶ Если требуется шифрование всей БД, нужно заменить бандл на семейство SQLCipher (например, SQLitePCLRaw.bundle_e_sqlcipher), а для хранения ключа шифрования всё равно потребуется DPAPI (глава 1).
• Default Timeout: тайм-аут команды (по умолчанию 30 секунд). Служит верхней границей времени повторных попыток из главы 5.

Ещё один момент — про асинхронные API. Поскольку у самого SQLite нет асинхронного ввода-вывода, async-методы вроде ExecuteNonQueryAsync внутри выполняются синхронно.⁷ Утверждение «раз сделал async, UI не подвиснет» здесь не работает, поэтому тяжёлые запросы нужно явно выносить в рабочий поток, например через Task.Run. Критерии выбора async/await разобраны в «Практической таблице решений по C# async/await».

3.1 Ловушка пулинга — файл остаётся открытым даже после Close

Начиная с версии 6.0 в Microsoft.Data.Sqlite пулинг соединений включён по умолчанию.⁶ Плюс в том, что не нужно заново открывать файл при каждом Open, но минус в том, что даже после Close / Dispose нативное соединение остаётся в пуле и продолжает удерживать хендл файла БД. В результате следующие операции завершаются ошибкой «файл используется».

• функция «сбросить данные», удаляющая и заново создающая файл БД
• замена файла БД при восстановлении из резервной копии
• перемещение файла БД при удалении приложения или эвакуационной обработке

Решение — очищать пул непосредственно перед операцией над файлом.

// Уничтожаем пул, связанный с этой строкой подключения, и освобождаем хендл файла
SqliteConnection.ClearPool(new SqliteConnection(connectionString));
File.Delete(dbPath);

В режиме WAL (следующая глава) могут оставаться файлы -wal / -shm, поэтому их тоже нужно убрать. Если требуется освободить всё при завершении приложения, используйте SqliteConnection.ClearAllPools(); а для одноразовых утилит, которым пулинг вообще не нужен, можно указать Pooling=False в строке подключения. «Вызвал Close, а удалить не могу» — частый вопрос после перехода на SQLite, поэтому с самого начала встраивайте ClearPool в любую служебную обработку, затрагивающую файл БД.

4. Как устроен режим WAL — используйте, понимая, что происходит

В предыдущей статье было сказано лишь «включите режим WAL», поэтому в этот раз разберём и само устройство. При стандартном методе rollback journal чтение блокируется во время записи — это главная причина database is locked. Метод WAL (Write-Ahead Logging) пишет изменения не в основной файл БД, а в отдельный лог-файл только для дозаписи, поэтому чтение не блокирует запись, а запись не блокирует чтение.² Типичная для бизнес-приложений конфигурация — UI-поток отображает историю, пока в фоне записываются измеренные значения — работает прямо в таком виде.

При включении режима WAL рядом с основным файлом БД (содержимым, зафиксированным контрольными точками) появляются два дополнительных файла.

Есть четыре свойства, которые нужно держать в уме.

• Контрольная точка (checkpoint): процесс переноса содержимого -wal в основной файл; по умолчанию выполняется автоматически, когда WAL достигает 1000 страниц (около 4 МБ).² Если долго держится длинная транзакция чтения, контрольная точка не может продвинуться и -wal разрастается, поэтому избегайте конструкции «держать соединение для чтения постоянно открытым и передавать его между компонентами» — открывайте и закрывайте его по мере необходимости (благодаря пулингу повторное открытие быстрое).
• Настройка сохраняется в БД: PRAGMA journal_mode=WAL достаточно выполнить один раз — она записывается в сам файл БД, и после этого режим WAL сохраняется при любом соединении.² Выполнять её при каждом подключении не нужно.
• Запись по-прежнему возможна только одна одновременно: WAL повышает параллелизм чтения и записи, но записи между собой остаются взаимоисключающими. Если ошибочно решить, что «раз включили WAL, можно свободно писать из нескольких потоков», вы столкнётесь с SQLITE_BUSY из главы 5.
• Не работает на сетевом общем ресурсе: поскольку wal-index рассчитан на разделяемую память, он не работает между процессами на разных машинах.² Впрочем, как уже говорилось в предыдущей статье, размещать SQLite на сетевом общем ресурсе вообще не стоит.

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

5. Блокировки и SQLITE_BUSY — сводим запись к одному пути

SQLITE_BUSY (в сообщении исключения — database is locked) возникает, когда другое соединение удерживает блокировку записи. Прежде всего стоит знать, что Microsoft.Data.Sqlite автоматически повторяет попытку при ошибках busy/locked вплоть до тайм-аута команды (по умолчанию 30 секунд).³ То есть собственная логика повтора вида «поймать в catch, поспать и выполнить заново» на стороне приложения обычно не нужна. Если исключение всё же долетает после исчерпания тайм-аута, причина одна из следующих.

• другое соединение (или другой процесс) удерживает транзакцию дольше 30 секунд
• транзакция, начатая как чтение, пытается повыситься до записи и конфликтует с другой записью — ожидание тут не помогает, поэтому она сразу завершается неудачей. Операции вида «сначала прочитать, потом записать» стандартная практика — проектировать сразу как транзакцию записи
• большое количество мелких записей одновременно поступает из нескольких потоков, и идёт борьба за блокировку

Ни одну из этих причин не устраняет «увеличение числа повторов». Решение — укорачивать транзакции и сводить путь записи к одному.

5.1 Создаём очередь записи через System.Threading.Channels

Для данных, источником которых являются несколько потоков (измерения, журналы операций и т. п.), вместо прямой записи каждым потоком в БД направляйте их в очередь, которую обрабатывает выделенный цикл записи. В .NET для этого прямо подходит System.Threading.Channels.

using System.Globalization;
using System.Threading.Channels;
using Microsoft.Data.Sqlite;

public sealed record Measurement(string DeviceId, double Value, DateTime CreatedAtUtc);

public sealed class MeasurementWriter : IAsyncDisposable
{
    private readonly Channel<Measurement> _channel =
        Channel.CreateBounded<Measurement>(new BoundedChannelOptions(10_000)
        {
            FullMode = BoundedChannelFullMode.Wait  // При переполнении заставляем ждать производителя
        });
    private readonly string _connectionString;
    private readonly Task _loop;

    public MeasurementWriter(string connectionString)
    {
        _connectionString = connectionString;
        _loop = Task.Run(WriteLoop);
    }

    // Можно вызывать из любого потока. К БД не обращается. Если цикл записи
    // уже умер, отправитель сразу узнает об этом через ChannelClosedException
    public ValueTask EnqueueAsync(Measurement m) => _channel.Writer.WriteAsync(m);

    private async Task WriteLoop()
    {
        try
        {
            await WriteLoopCore();
        }
        catch (Exception ex)
        {
            // Через канал сообщаем отправителям, что писатель умер (например, из-за
            // переполнения диска). Если этого не сделать, при заполнении очереди
            // EnqueueAsync будет ждать вечно, и никто не заметит сбой
            _channel.Writer.TryComplete(ex);
            throw;
        }
    }

    private async Task WriteLoopCore()
    {
        using var conn = new SqliteConnection(_connectionString);
        conn.Open();
        var buffer = new List<Measurement>(500);
        while (await _channel.Reader.WaitToReadAsync())
        {
            // Забираем накопившееся, до 500 элементов, и записываем одной транзакцией
            buffer.Clear();
            while (buffer.Count < 500 && _channel.Reader.TryRead(out var m))
                buffer.Add(m);

            using var tx = conn.BeginTransaction();
            using var cmd = conn.CreateCommand();
            cmd.Transaction = tx;
            cmd.CommandText =
                "INSERT INTO measurement (device_id, value, created_at) " +
                "VALUES ($device, $value, $at)";
            var pDevice = cmd.Parameters.Add("$device", SqliteType.Text);
            var pValue  = cmd.Parameters.Add("$value",  SqliteType.Real);
            var pAt     = cmd.Parameters.Add("$at",     SqliteType.Text);
            foreach (var m in buffer)
            {
                pDevice.Value = m.DeviceId;
                pValue.Value  = m.Value;
                // Явно указываем InvariantCulture, чтобы не зависеть от календаря и цифр культуры по умолчанию
                pAt.Value     = m.CreatedAtUtc.ToString(
                    "yyyy-MM-dd HH:mm:ss.fffffff", CultureInfo.InvariantCulture);
                cmd.ExecuteNonQuery();
            }
            tx.Commit();
        }
    }

    public async ValueTask DisposeAsync()
    {
        // Не бросаем исключение, даже если цикл записи уже закрыл канал из-за сбоя
        // (Complete() бросил бы при повторном закрытии, скрыв исходное исключение)
        _channel.Writer.TryComplete();
        await _loop;  // Дописываем оставшееся. Если цикл умер, исходное исключение всплывёт здесь
    }
}

При такой конструкции конфликты записи структурно исключаются, а накопившееся в очереди естественным образом батчируется, так что ускорение из следующего раздела достаётся сама собой. В бизнес-приложении важны и другие детали: дописывание очереди перед закрытием через DisposeAsync при завершении, явное решение о поведении при переполнении через FullMode, а также распространение сбоя цикла записи на отправителей через TryComplete(ex) (если единственный писатель тихо умирает, сбой проявляется как вечное ожидание при заполнении очереди). Та же идея «свести путь к одному» встречается и в проектировании эксклюзивной блокировки при взаимодействии через файлы (см. «Лучшие практики интеграции через файлы и блокировки»).

5.2 Объединение в транзакцию меняет порядок величины

SQLite при каждом коммите выполняет синхронную запись на диск (fsync), поэтому INSERT по одной записи с неявным коммитом упирается в потолок в несколько сотен–тысяч записей в секунду даже на SSD, а на HDD — в несколько десятков в секунду. Достаточно объединить те же INSERT в явную транзакцию по 1000 записей (обернуть BeginTransaction и в конце вызвать Commit), чтобы выйти на десятки–сотни тысяч записей в секунду. Изменение в три строки, которое даже неловко называть «тюнингом», меняет производительность на два-три порядка.

Многие жалобы вроде «импорт CSV занимает 20 минут» или «миграция данных при запуске никак не заканчивается» вызваны именно этим и решаются простым переходом на транзакции и повторным использованием параметров (форма кода из предыдущего раздела). С другой стороны, если сделать транзакцию слишком длинной, она заставит ждать другие записи, поэтому практичный компромисс — один коммит на «несколько сотен–тысяч записей или несколько сотен миллисекунд».

6. Ловушки сопоставления типов — что назначать всего четырём типам

SQLite реально умеет хранить всего четыре типа — INTEGER / REAL / TEXT / BLOB, и каждый тип .NET сопоставляется с одним из них. Основные соответствия в Microsoft.Data.Sqlite таковы.⁴

Особого внимания требуют три типа, попадающие в TEXT.

• DateTime: формат в стиле ISO 8601 в TEXT, если формат и часовой пояс унифицированы, даёт совпадение строковой сортировки с хронологической — на практике проблем нет. Но верно и обратное: всё ломается в тот момент, когда UTC и локальное время смешиваются. Единственное решение — заранее решить «хранить в UTC, преобразовывать в локальное только при отображении» и соблюдать это во всех местах кода; функции SQLite вроде datetime('now') тоже возвращают UTC. Ещё момент: при самостоятельном преобразовании в строку обязательно передавайте в ToString CultureInfo.InvariantCulture. Если оставить культуру по умолчанию, на машинах с негригорианской культурой (японское или буддийское летоисчисление и т. п.) представление года изменится только там, что сломает и сортировку, и чтение (см. пример кода в 5.1).
• Guid: хранится как строка, поэтому сопоставление — это сравнение строк. Если другой инструмент или библиотека запишет его в другом представлении (регистр букв, формат BLOB), сверка не удастся, поэтому если к БД обращаются несколько языков/инструментов, зафиксируйте представление явной спецификацией.
• decimal: самая большая ловушка. Он хранится как TEXT, потому что REAL приводит к потере точности⁴, но сравнение вроде WHERE amount > 1000 для столбца TEXT работает не так, как задумано, потому что в порядке типов SQLite TEXT всегда больше числового значения. Агрегатные функции вроде SUM тоже внутренне преобразуются в REAL, теряя ту самую точность, ради которой и был выбран decimal.

Практическая рекомендация по decimal проста: храните денежные суммы как целое число (INTEGER) в минимальной единице валюты. Для японской иены — как long в единицах йен, преобразуя только при отображении. С целым числом сравнение и агрегация точны и быстры, а ловушка типов исчезает. Если из-за существующей схемы вынуждены хранить decimal как есть, смиритесь с тем, что сравнение и агрегацию нужно выполнять не в SQL, а на стороне .NET после чтения данных.

Кроме того, имя типа, указанное в CREATE TABLE, — всего лишь подсказка для «сходства (affinity)», а собственное имя типа вроде STRING — источник аварий из-за неявного преобразования. Официальная рекомендация — использовать для имён типов столбцов только те же четыре — INTEGER / REAL / TEXT / BLOB.⁴

7. Эксплуатация — не ломать, а если сломалось — уметь восстановить

7.1 quick_check при запуске

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

using var cmd = conn.CreateCommand();
cmd.CommandText = "PRAGMA quick_check";
var result = (string)cmd.ExecuteScalar()!;
if (result != "ok")
{
    // Не дописываем в повреждённую БД. Переходим в режим только для чтения и предлагаем восстановление
    logger.LogError("Обнаружено повреждение базы данных: {Detail}", result);
    EnterReadOnlyMode(result);
}

Политика не выполнять автоматическое восстановление или автоматический откат при обнаружении повреждения (а привлекать пользователя) описана в разделе 6.2 предыдущей статьи.

7.2 Резервное копирование — почему не подходит копирование файла

Простое копирование файла БД во время работы может захватить промежуточное состояние транзакции, и официальная документация SQLite прямо называет это причиной повреждения.⁵ В режиме WAL, если скопировать только основной файл, оставив без внимания -wal (содержащий ещё не применённые коммиты — глава 4), самые свежие данные пропадут.

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

• VACUUM INTO: одной SQL-инструкцией создаёт дефрагментированную копию минимального размера.⁸ Пример кода приведён в разделе 6.3 предыдущей статьи — обращайтесь туда.
• SqliteConnection.BackupDatabase: обёртка над Backup API SQLite, копирует между объектами соединений.

using var source = new SqliteConnection($"Data Source={dbPath}");
using var target = new SqliteConnection($"Data Source={backupPath}");
source.Open();
target.Open();
source.BackupDatabase(target);  // Даже во время работы получается согласованный снимок

Однако у BackupDatabase есть нюанс. Текущая реализация Microsoft.Data.Sqlite копирует максимально быстро, но блокирует запись из других соединений до завершения.⁹ Если делать резервную копию большой БД во время активных измерений или действий пользователя, записи в этот период проявятся как SQLITE_BUSY или зависание экрана. Безопасное разделение таково: для повседневного создания поколений резервных копий использовать VACUUM INTO, а BackupDatabase приберечь для взаимного копирования с in-memory БД или для дублирования в окно, когда запись остановлена, — например, во время обслуживания. Если для периодического запуска используете планировщик заданий, не копируйте файл извне, а поручите выполнение указанного выше резервного копирования самому приложению (или небольшому инструменту, корректно открывающему SQLite). Проектирование самого механизма периодического запуска описано в «Надёжной эксплуатации планировщика заданий для периодического выполнения».

7.3 Расположение и миграция

Базовое расположение файла БД — %LOCALAPPDATA%\<название компании>\<название приложения>, а минимальная схема версионирования — миграция при запуске через PRAGMA user_version. Обе темы с примерами кода уже разобраны в предыдущей статье (глава 3 и раздел 6.1), поэтому здесь не повторяем. Добавим лишь одно: если перед выполнением миграции сделать одно поколение резервной копии из раздела 7.2, худший случай — «миграция не удалась, и приложение не запускается» — превращается в простую замену файла.

8. Когда стоит использовать EF Core — точка безубыточности ORM

До сих пор мы писали код на голом Microsoft.Data.Sqlite, но есть и чёткие случаи, когда стоит использовать SQLite-провайдер EF Core. Критерий выбора — характер приложения.

Если выбираете EF Core, нужно знать ограничения, специфичные для SQLite-провайдера.¹⁰

• Пересборка таблицы из-за ограничений ALTER TABLE: SQLite не поддерживает напрямую изменение типа или удаление столбца, поэтому миграции, включающие AlterColumn или DropColumn, выполняются как пересборка: «создать новую таблицу → скопировать данные → удалить старую таблицу → переименовать». В окружениях с большим объёмом данных это влияет на время применения и использование диска, поэтому изменения схемы больших таблиц нужно планировать заранее.
• Нельзя создать идемпотентный скрипт: скрипты миграции с условиями if-then, как для SQL Server, сгенерировать нельзя. Реалистичный способ применения — dbContext.Database.Migrate() при запуске приложения.
• Операции с decimal / DateTimeOffset вычисляются на стороне клиента: особенности типов из главы 6 никуда не исчезают и в EF Core. Любое сравнение, кроме равенства, и сортировка вычисляются на стороне клиента, поэтому рекомендация хранить суммы как целые числа в минимальной единице действует и в EF Core (можно преобразовывать в long для хранения через value converter).
• WAL включён по умолчанию: БД, созданная EF Core, изначально работает в режиме WAL⁷, поэтому настройка из главы 4 не требуется. Однако понимание поведения по-прежнему необходимо.

Даже при выборе EF Core хорошо работает приём с использованием in-memory БД SQLite для модульных тестов слоя репозитория. Поскольку она работает на том же провайдере, что и в продакшене, зазор вида «на моках проходит, а на реальной БД падает» сужается. О том, на каком уровне писать тесты, см. «Границу между модульными и интеграционными тестами».

9. Итог

SQLite — библиотека, о которой можно сказать: «просто встроить — 30 минут, а чтобы правильно эксплуатировать — нужно проектирование». При этом необходимое проектирование вполне определено, и если превратить содержание этой статьи в чек-лист, получится шесть пунктов.

• Библиотека — Microsoft.Data.Sqlite (или EF Core поверх него). Не смешивайте с информацией, рассчитанной на System.Data.SQLite
• Включайте режим WAL начиная с первого релиза и понимайте роль файлов -wal / -shm
• Сводите запись к одному пути (очередь записи на Channels), объединяйте мелкие INSERT в транзакции
• Унифицируйте DateTime по UTC, суммы храните как целые числа в минимальной единице валюты. Не сравнивайте и не агрегируйте decimal прямо как TEXT
• Выполняйте quick_check при запуске, резервное копирование — через VACUUM INTO или BackupDatabase. Не копируйте файл во время работы
• Не забывайте SqliteConnection.ClearPool в операциях, удаляющих или заменяющих файл БД

Если узнаёте в этом свою конфигурацию — спасаетесь от database is locked повторными попытками или делаете резервные копии простым копированием файла, — попробуйте один раз пройтись по пунктам этой статьи, пока ничего не сломалось. Исправление каждого из них само по себе — небольшое изменение.

Похожие статьи

• Как выбрать место хранения данных для Windows-приложения — таблица решений SQLite / JSON / реестр / Access
• Хранение конфиденциальной информации в Windows-приложении — избегаем открытых настроек с помощью DPAPI
• Надёжная эксплуатация периодического выполнения через планировщик заданий
• Практическая таблица решений по C# async/await — как писать код, не подвешивая UI и без взаимоблокировок

Смежные области консультаций

Komura Software Co., Ltd. занимается ревью проектирования бизнес-приложений со встроенным SQLite (проектирование блокировок, резервного копирования и миграций), расследованием проблем в работающих приложениях — таких как database is locked, повреждение данных, снижение производительности — а также поддержкой миграции с существующих хранилищ данных вроде Access.

• Техническая консультация / ревью архитектуры
• Разработка Windows-приложений
• Использование и миграция существующих активов
• Контакты

Источники

Похожие статьи

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

Связанные темы

Эти страницы показывают тему статьи в более широком контексте услуг и решений.

Услуги по этой теме

Статья напрямую связана со следующими услугами.

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

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

Почему в SQLite появляется ошибка «database is locked»?

SQLITE_BUSY возникает, когда другое соединение удерживает блокировку записи. Microsoft.Data.Sqlite автоматически повторяет попытку вплоть до тайм-аута команды (по умолчанию 30 секунд), поэтому собственная логика повторов обычно не нужна. Если исключение всё же возникает, причина — либо длинная транзакция дольше 30 секунд, либо конфликт при повышении транзакции чтения до записи, либо борьба за блокировку между множеством мелких записей из разных потоков, и увеличение числа повторов эту проблему не решает. Настоящее решение — укорачивать транзакции и сводить путь записи к одному, например через очередь на основе System.Threading.Channels. Включение режима WAL тоже устраняет большую часть блокировок между чтением и записью.

Какую библиотеку выбрать для работы с SQLite из C#?

Для новой разработки базовый выбор — Microsoft.Data.Sqlite, ADO.NET-провайдер, который поддерживает Microsoft (или построенный на нём SQLite-провайдер EF Core). Нативное ядро SQLite входит в NuGet-пакет, поэтому никакой отдельной работы по распространению не требуется. Старожил System.Data.SQLite — совершенно другая библиотека, несовместимая ни по строке подключения, ни по обработке типов: например, Guid по умолчанию хранится как BLOB в System.Data.SQLite, а в Microsoft.Data.Sqlite — как TEXT. Всегда обращайте внимание, для какой из библиотек написан найденный в интернете пример.

Можно ли делать резервную копию SQLite простым копированием файла?

Простое копирование файла во время работы приложения запрещено: оно может захватить промежуточное состояние транзакции, и официальная документация SQLite прямо называет это причиной повреждения. В режиме WAL файл -wal содержит коммиты, ещё не применённые к основному файлу, поэтому копирование только основного файла приводит к потере самых свежих данных. Правильные способы — VACUUM INTO (одной SQL-инструкцией создаётся согласованная копия минимального размера) или SqliteConnection.BackupDatabase. Однако BackupDatabase блокирует запись из других соединений до завершения копирования, поэтому для повседневного создания поколений резервных копий безопаснее VACUUM INTO.

Почему массовая вставка INSERT в SQLite работает медленно?

Почти всегда причина — размер коммита. SQLite при каждом коммите выполняет синхронную запись на диск (fsync), поэтому при INSERT по одной записи с неявным коммитом даже на SSD упираетесь в потолок в несколько сотен–тысяч записей в секунду. Достаточно объединить те же INSERT в явную транзакцию по 1000 записей (обернуть BeginTransaction и в конце вызвать Commit), чтобы выйти на десятки–сотни тысяч записей в секунду — ускорение на два-три порядка. С другой стороны, если сделать транзакцию слишком длинной, она заставит ждать другие записи, поэтому практичный компромисс — один коммит на несколько сотен–тысяч записей или на несколько сотен миллисекунд.

Об авторе

Страница с профилем автора статьи.

Го Комура

Представитель KomuraSoft LLC

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