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

Question

我正在使用System.Data.Sqlite来访问C#中的SQLite数据库。我有一个查询必须读取表中的行。在迭代行并且阅读器处于打开状态时，必须执行某些SQL更新。我遇到了“数据库被锁定”的异常。 SQLite文档中指出：

当进程想要从数据库文件中读取数据时，它会按照以下步骤进行：

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

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

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

多个进程可以同时打开同一个数据库。多个进程可以同时进行SELECT操作。但是，只有一个进程可以在任何时刻对数据库进行更改。

这本书《SQLite权威指南》中提到：

...通过使用read_uncommited命令，连接可以选择具有读未提交隔离级别。如果将其设置为true，则该连接将不会在读取表时放置读锁。因此，另一个写入者实际上可以更改表，因为以读未提交模式运行的连接既不能被其他连接阻塞，也不能阻塞其他连接。

我尝试在SQL查询命令语句中将pragma设置为读未提交，如下所示：

PRAGMA read_uncommitted = 1;
SELECT Column1, Column2 FROM MyTable

在使用不同的连接进行SQL更新时，仍然会出现“数据库已锁定”的异常。然后我尝试在连接实例上将隔离级别设置为读未提交。但是仍然出现相同的异常。

如何在保持打开数据读取器以循环遍历数据库行而不锁定数据库的情况下执行更新操作？

更新：

下面的两个答案都有效。但是，我已经不再使用默认的回滚日志，而是使用了Write-Ahead Logging，这提供了更好的数据库读写并发性。

- Elan

Answer 1

使用WAL模式。

- Doug Currie

---

是的，请查看http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki，2011年2月的1.0.68.0版本是与SQLite 3.7.5合并的代码。 - Doug Currie

看起来很有前途；不过我还没有看到任何已经准备好或可供下载的东西...你提到的发行版1.0.68.0是官方可用的，还是只是一个alpha/beta版本？ - Elan

源代码可供下载；由于各种构建工具和目标的复杂性，构建系统仍在进行中。 - Doug Currie

也许可以复制db文件，在修改原始文件时从副本中读取？毕竟这是SQL LITE :) - Sharas

使用 System.Data.SQLite.Core，您可以通过将 "journal mode=Wal" 添加到连接字符串来实现此操作。 - Foole

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

- Elan