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

Использование System.Data.Sqlite 1.0.86.0 (включая SQLite 3.7.17) в с упреждающей записью в журнале В режиме одновременного чтения у меня возникают блокировки базы данных, чего не должно быть, если я правильно понимаю WAL. Я ничего не пишу и не фиксирую, и режим изоляции транзакции ReadCommitted правильно используется, чтобы избежать сериализации чтения.

БД SQLite (с WAL) заблокирована при подготовке выберите statmement - почему? - аналогичная проблема. Единственный ответ говорит о вызове sqlite3_reset после каждого sqlite3_step, что, насколько я видел в исходном коде, правильно выполняет System.Data.Sqlite.

Полное воспроизведение:

internal static class Program {

    private const string DbFileName = "test.sqlite";
    private static readonly string _connectionString = BuildConnectionString(DbFileName);

    internal static void Main() {
        File.Delete(DbFileName);
        ExecuteSql("CREATE TABLE Test (Id INT NOT NULL, Name TEXT);", true);
        for (int i = 0; i < 10; i++)
            Task.Run(() => ExecuteSql("SELECT Id, Name FROM Test;", false));
        Console.ReadKey();
    }

    private static string BuildConnectionString(string fileName) {
        var builder = new SQLiteConnectionStringBuilder {
            DataSource = fileName,
            DateTimeFormat = SQLiteDateFormats.ISO8601,
            DefaultIsolationLevel = IsolationLevel.ReadCommitted,
            ForeignKeys = true,
            JournalMode = SQLiteJournalModeEnum.Wal,
            SyncMode = SynchronizationModes.Full
        };
        return builder.ToString();
    }

    private static void ExecuteSql(string sql, bool commit) {
        Stopwatch stopwatch = Stopwatch.StartNew();
        using (var connection = new SQLiteConnection(_connectionString)) {
            connection.Open();
            using (SQLiteTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)) {
                using (SQLiteCommand command = connection.CreateCommand()) {
                    command.CommandText = sql;
                    command.ExecuteNonQuery();
                }
                if (commit)
                    transaction.Commit();
            }
        }
        stopwatch.Stop();
        Console.WriteLine("{0}: {1}", stopwatch.Elapsed, sql);
    }

}

Вывод:

00:00:00.1927492: CREATE TABLE Test (Id INT NOT NULL, Name TEXT);
00:00:00.0054247: SELECT Id, Name FROM Test;
00:00:00.0055334: SELECT Id, Name FROM Test;
00:00:00.0056022: SELECT Id, Name FROM Test;
00:00:00.0054860: SELECT Id, Name FROM Test;
00:00:00.0053894: SELECT Id, Name FROM Test;
00:00:00.0056843: SELECT Id, Name FROM Test;
00:00:00.0006604: SELECT Id, Name FROM Test;
00:00:00.0006758: SELECT Id, Name FROM Test;
00:00:00.0097950: SELECT Id, Name FROM Test;
00:00:00.0980008: SELECT Id, Name FROM Test;

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

Ошибка SQLite (261): база данных заблокирована

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


person Julien Lebosquain    schedule 14.06.2013    source источник
comment
Всегда ли это десятая параллельная транзакция? Изменится ли он, если использовать уровень изоляции по умолчанию? Файл БД находится в сети или на локальном диске?   -  person CL.    schedule 15.06.2013
comment
Нет, не всегда десятый, это зависит от прогона, обычно бывает 2-3 раза с 10 потоками. Всего с 3 потоками я получаю это примерно в 50% случаев (конечно, это не научная мера). База данных локальная. Уровень изоляции по умолчанию для SQLite - Serializable, что означает отсутствие параллелизма вообще.   -  person Julien Lebosquain    schedule 15.06.2013
comment
... для данной транзакции (если я получу документы) правильно.   -  person mlvljr    schedule 16.07.2014
comment
Ваш sqlite3 может быть скомпилирован без HAVE_USLEEP, что заставляет параллельные потоки ждать секунды перед повторной попыткой. beets.io/blog/sqlite-nightmare.html   -  person Labo    schedule 19.05.2021


Ответы (1)


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

Простое решение - включить пул (Pooling=True в строке подключения). Это не имеет никакого эффекта в примере, поскольку все соединения открываются одновременно, но в реальном приложении больше нет блокировки, поскольку существующие соединения используются повторно. Большинство простых запросов увеличились с 5 мс до менее 1 мс (на SSD), а сообщения «база данных заблокирована» полностью исчезли.

person Julien Lebosquain    schedule 15.06.2013