Как выполнить SQLite запрос с помощью считывателя данных без блокировки базы данных?

Question

Я использую System.Data.Sqlite для доступа к базе данных SQLite в C#. У меня есть запрос, который должен прочитать строки в таблице. При выполнении итераций по строкам и когда читатель открыт, необходимо выполнить определенные обновления SQL. Я работаю в исключение "база данных заблокирована".

Документация SQLite гласит:

Когда процесс хочет прочитать файл базы данных, он выполняет следующую последовательность шагов:

1. Откройте файл базы данных и получите блокировку SHARED.

Далее в документации говорится о блокировке "SHARED":

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

В FAQ говорится:

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

Книга "Полное руководство по SQLite" гласит:

... соединение может выбрать уровень изоляции без чтения, используя прагму read_uncommited. Если установлено значение true, соединение не будет устанавливать блокировки чтения для таблиц, которые оно читает. Следовательно, другой модуль записи может фактически изменить таблицу, так как соединение в режиме незафиксированного чтения не может ни блокировать, ни блокироваться никакими другими соединениями.

Я попытался установить прагму для чтения незафиксированным в операторе команды SQL-запроса следующим образом:

PRAGMA read_uncommitted = 1;
SELECT Column1, Column2 FROM MyTable

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

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

Обновить:

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

Answer 1

Используйте режим WAL.

Answer 2

Я не смог заставить это работать с помощью поставщика данных с открытым исходным кодом отсюда. Тем не менее, я смог заставить это работать с помощью бесплатной стандартной версии dotConnect следующим образом:

Создайте приведенный ниже импорт DLL, чтобы мы могли включить общий кэш для SQLite.

[DllImport("sqlite3.dll", CallingConvention = CallingConvention.Cdecl)]
public static extern int sqlite3_enable_shared_cache(int enable);

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

sqlite3_enable_shared_cache(1);

Затем добавьте префикс SQL-запроса, используемый программой чтения данных, к следующему:

PRAGMA read_uncommitted = 1;
SELECT Column1, Column2 FROM MyTable

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

Обновить:

Более новая версия поставщика данных Devart SQLite теперь поддерживает это в улучшенном виде. Для включения общего кэша можно сделать следующий вызов:

Devart.Data.SQLite.SQLiteConnection.EnableSharedCache();

Можно настроить незафиксированные чтения в строку подключения, например, следующим образом:

Devart.Data.SQLite.SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder();
builder.ReadUncommitted = true;
builder.DateTimeFormat = Devart.Data.SQLite.SQLiteDateFormats.Ticks;
builder.DataSource = DatabaseFilePath;
builder.DefaultCommandTimeout = 300;
builder.MinPoolSize = 0;
builder.MaxPoolSize = 100;
builder.Pooling = true;
builder.FailIfMissing = false;
builder.LegacyFileFormat = false;
builder.JournalMode = JournalMode.Default;
string connectionString = builder.ToString();