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

Question

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

SQLite documentation状态:

When a process wants to read from a database file, it followed the following sequence of steps:

1. Open the database file and obtain a SHARED lock.

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

The database may be read but not written. Any number of processes can hold SHARED locks at the same time, hence there can be many simultaneous readers. But no other thread or process is allowed to write to the database file while one or more SHARED locks are active.

FAQ状态:

Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.

本书The Definitive Guide to SQLite状态:

...a connection can choose to have a read-uncommitted isolation level by using the read_uncommited pragma. If it is set to true, then the connection will not put read locks on the tables it reads. Therefore, another writer can actually change a table as the connection in read-uncommitted mode can neither block nor be blocked by any other connections.

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

PRAGMA read_uncommitted = 1;
SELECT Column1, Column2 FROM MyTable

在同一线程上使用不同连接的 SQL 更新仍然失败，并出现“数据库已锁定”异常。然后我尝试将隔离级别设置为在连接实例上未提交读取。仍然没有变化，但有同样的异常(exception)。

如何让开放数据读取器在不锁定数据库的情况下循环遍历数据库中的行，以便我可以执行更新？

更新:

下面的两个答案都有效。然而，我已经不再使用默认的回滚日志，现在使用预写日志记录，它提供了改进的数据库读写并发性。

Answer 1

使用WAL模式。