如何在不锁定数据库的情况下使用数据读取器执行SQLite查询? [英] How perform SQLite query with a data reader without locking database?

问题描述

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

I am using System.Data.Sqlite to access SQLite database in C#. I have a query which must read through rows in a table. While iterating through the rows and while the reader is open, certain SQL updates must be performed. I am running into a "database is locked" exception.

SQLite文档指出:

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

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

1. 打开数据库文件并获得SHARED锁.

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

The documentation further states about "SHARED" locking:

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

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.

常见问题解答状态:

多个进程可以同时打开相同的数据库.多个进程可以同时执行SELECT.但是，在任何时候，只有一个进程可以对数据库进行更改.

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.

这本书

...连接可以使用 read_uncommited 编译指示来选择具有 read-uncommitted 隔离级别.如果将其设置为 true ，则该连接将不会在其读取的表上放置读取锁.因此，另一个写程序实际上可以更改表，因为处于读未提交模式的连接既不能阻塞也不能被任何其他连接阻塞.

...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.

我试图将编译指示设置为在SQL查询命令语句中读取未提交的内容，如下所示:

I attempted to set the pragma to read uncommitted within the SQL query command statement as follows:

PRAGMA read_uncommitted = 1;
SELECT Column1, Column2 FROM MyTable

在使用不同连接的同一线程上进行SQL更新仍然失败，并出现数据库已锁定"异常.然后，我尝试将隔离级别设置为在连接实例上读取未提交的内容.除了相同的例外，仍然没有变化.

A SQL update on the same thread using a different connection still failed with a "database is locked" exception. I then attempted to set the isolation level to read uncommitted on the connection instance. Still no change with the same exception.

如何完成一个开放的数据读取器来遍历数据库中的行而不锁定数据库，以便我可以执行更新?

How can I accomplish having an open data reader to loop through rows in the database without locking the database, so that I can execute updates?

更新:

以下两个答案均有效.但是，从那时起，我不再使用默认的回滚日志，而现在使用了预写日志记录功能，该功能可以提高数据库读写的并发性.

Both answers below work. I have however since moved away from using the default rollback journal to now using the Write-Ahead Logging, which provides improved concurrency of database reads and writes.

推荐答案

使用 WAL 模式.

这篇关于如何在不锁定数据库的情况下使用数据读取器执行SQLite查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！