如何在不锁定数据库的情况下使用数据读取器执行 SQLite 查询？

Question

我正在使用 System.Data.Sqlite 在 C# 中访问 SQLite 数据库。我有一个查询必须读取表中的行。在迭代行和读取器打开时，必须执行某些 SQL 更新。我遇到了“数据库已锁定”异常。

SQLite 文档 指出：

当进程想要从数据库文件中读取时，它遵循以下步骤序列：

1. 打开数据库文件并获取共享锁。

该文档进一步说明了“共享”锁定：

数据库可以读取但不能写入。任意数量的进程可以同时持有共享锁，因此可以有许多同时的读取者。但是，当一个或多个共享锁处于活动状态时，不允许其他线程或进程写入数据库文件。

常见问题解答指出：

多个进程可以同时打开同一个数据库。多个进程可以同时执行 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 语句，如下所示：

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();