How perform SQLite query with a data reader without locking database?
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.
The SQLite documentation states:
When a process wants to read from a database file, it followed the following sequence of steps:
- Open the database file and obtain a SHARED lock.
The documentation further states about "SHARED" locking:
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.
The FAQ states:
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 book The Definitive Guide to SQLite states:
...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.
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
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?
Update:
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.