System.Data.SQLite

When connecting 2 or more users to the same database I consistently get this error message. (This instance is when a thread is retrieving a configuration value, read-only operation.)

System.Data.SQLite.SQLiteException: The database file is locked database is locked

at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)

at System.Data.SQLite.SQLiteDataReader.NextResult()

at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)

at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)

at System.Data.SQLite.SQLiteCommand.ExecuteScalar()

at XrefDatabaseConnection.GetXrefConfig(String config_name)

 

I've tried to shuffle around the different pragma values to allow for concurrent editing of a database file but nothing seems to work. Right now I have.

this.ExecuteNonQuery("PRAGMA cache_size = 32768");
this.ExecuteNonQuery("PRAGMA count_changes = 0");
this.ExecuteNonQuery("PRAGMA journal_mode = DELETE");
this.ExecuteNonQuery("PRAGMA read_uncommitted = 1");
this.ExecuteNonQuery("PRAGMA synchronous = NORMAL");
this.ExecuteNonQuery("PRAGMA temp_store = MEMORY");

 // This ExecuteNonQuery is just an implementation that executes the query without creating the SQLCommand object directly. The function does that work for you.

 

I open the database with the following connection string.

Data Source={0};FailIfMissing=False;Legacy Format=False;Cache Size=16384;Page Size=32768; // Where {0} is the file name.

 

The file can and usually resides on a network path when I have concurrent users editing the file. Assume file permissions are set properly.

 

Also, I sometimes (very very rarely) have this problem when running the application itself and I suspect that it occurs because of a thread accessing the database. These threads do not write to the database! These threads that run are ran for short periods of time and clone the database connection rather than using the main thread's one. During my testing I've disabled all threads accessing the database and I still have problems when working with files on the network concurrently.

 

Lastly, the database access works similarly when working locally or on a network drive if 1 user is connected. It doesn't seem to matter.

 

So you're doing no writes, and no transactions are open at the time?

 

This exception can occur when 2 users are trying to read the database only. No transactions are created unless bulk loading is occuring.

 

Details:

I have a custom object that wrappers the database connection and restricts the ability to have only 1 opened transaction at a time. The CreateCommand function looks to see if there is a transaction currently running and if so it attached the transaction to the command, just so the code is safer. For the scenarios I see the transaction is not activated because I only use transactions during bulk loads where the database itself is not available for others to use it. The wrapper rejects connection attempts when bulk loading data and those operations are done locally.

I have seen cases were the application will try to write something and crash because the file is locked. But that typically occurs after other similar exceptions have been thrown.

I'm thinking it could be that I'm not bracketing using statements around my SQLiteDataReader objects. Could doing this help out the problem?

 

IE using (SQLiteDataReader reader = cmd.ExecuteReader())

{

}

Leaving readers open could cause issues.  Those won't get cleaned up until the lazy garbage collector gets around to it.  It'd certainly be better in any case to have using() statements around your readers at the very least.  The following objects use unmanaged resources that the garbage collector will be lazy about cleaning up:

SQLiteCommand, SQLiteConnection, SQLiteDataReader, and possibly SQLiteTransaction if I recall correctly.

 I'm going through my code now and am adding using brackets around all the readers commands and data adapters. I'll probably get back to you tonight on this. I do already apply Close on all these objects in their proper manner but I'll double check the code.

I have already added the using statements to certain areas and it does seem to help. Can't reproduce the error now but I haven't tested this throroughly.

Thanks for your help.

I did more rigorous testing and it seems that I don't get the error anymore. I think it was happening when I would load large amounts of table effectively scanning an entire table of about 40,000 records and hand them to the client. All areas that have possible points of shared queries were corrected in my code for now.