How perform SQLite query with a data reader without locking database?

c# locking sqlite system.data.sqlite
Elan picture Elan · Mar 20, 2011 · Viewed 19.9k times · Source

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:

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

Answer

Doug Currie picture Doug Currie · Mar 20, 2011

Use WAL mode.

Related questions

Multiple access to a single SQLite database file via System.Data.SQLite and c#

As I can read from SQLite FAQ it supports multiple processes reading (SELECT) and only one process writing (INSERT, UPDATE, DELETE) database at any moment in time: SQLite uses reader/writer locks to control access to the database. When any …

Read More
How does lock work exactly?

I see that for using objects which are not thread safe we wrap the code with a lock like this: private static readonly Object obj = new Object(); lock (obj) { // thread unsafe code } So what happens when multiple threads access the …

Read More
Why is lock(this) {...} bad?

The MSDN documentation says that public class SomeObject { public void SomeOperation() { lock(this) { //Access instance variables } } } is "a problem if the instance can be accessed publicly". I'm wondering why? Is it because the lock will be held longer than necessary? …

Read More