System.Data.SQLite

Hi - I'm getting the error "Attempt to write to read-only database" from my application. We have multiple threads accessing a few different sqlite database files, but I'm using C#'s lock statement to ensure only 1 thread can ever access any database file at any one time.  Now it runs fine most of the time under stress but occasionally I get the above error. 

Does anyone know what the error actually means? Is it always another process comming in and locking the file, or can it be a separate thread or a database lock or something?

 I had a look in the provider code and it looks like it boils down to a SQLITE_READONLY error code so I'm about to wade into the C engine code to look for what happens (if anyone has experience with building a debug version of the C code for use with SQLite.NET that would be great to know as well).

From what I can tell this is happening because I have different threads. The lock code ensures that only one thread at a time has a SQLiteConnection to the database.  Each thread gets a new SQLiteConnection for each connection however I use Pooling=True; inside the connection string.

I ended up setting breakpoints inside sqlite3.c  - function "winopen" and it has some code that if it tries to open the file readwrite and it fails then it tries to get it readonly and marks the DB accordingly.  Now in theory this should never happen in my application as I always lock and always dispose of connections/transactions/commands/readers etc. (at least as far as I can tell - they are all inside "using" blocks). Windows is sometimes returning ERROR_SHARING_VIOLATION from the open file call which is what blows things up.

I was running filemon at the time and from what I could tell I saw my application open the file in question - then it did some stuff including locking and then it did an UNLOCK but didn't close it... later on another OPEN gave the sharing violation.

I've no idea yet why it didn't close the file that one time (like it usually does).  Does Sqlite always open a file every time - I thought the idea was that connection pooling in the provider prevented that?  Certainly I'm sure it ran a lot faster when I turned pooling on :).   Ideally I'd like sqlite to keep the file open all the time - no one else should be allowed to touch it in my case - is there an option to do this?

For the record I've switched to SQLite from SQLCE (MS's Sql compact edition).  SQLCE has better concurrency support (i.e. it can do at least table locking and maybe finer than that - so it doesn't lock the whole file)  - but it's so much slower and I'm sure it wasn't even atomic and that transactions wouldn't rollback etc.  With this provider it was easy to switch and it's so much faster and more robust :)

I was wondering if connection pooling and threads just wouldn't work - but as of Sqlite3.5 you can use a connection on any thread it claims so I'm stumped again as to why this is happening.

We're having a lot of problems with access violations and unexpected db lock errors (i.e., timeouts are 30 second and each individual query is 10-15 ms and full transactions are 150ms) so seems like there is some hoakiness with multiple connections in the latest sqlite.  I'm not sure if it's related to the connection pool at all--we get the same results with the pool turned off (but it takes a lot longer to open connections--we're pushing 400ms now to open a new connection).  

We haven't seen the exact error you're seeing at all though--write to read-only db--related to threads.  We have seen it when deploying to new machines and always tracked it back to permissions, but in our particular case it was very consistent--every query resulted in a read-only error and once we fixed permissions, every query was fine.

Anyways, we're really happy to have the extra info you provided in your question and follow up.  Even if it's not a solution yet, it's a great step in the right direction.

Best regards,

Sam 

Thanks for the update :)

 Just to be clear I'm using 1.0.48.0 build (sqlite 3.5.4) which is showing this issue and not some later version.. though I notice 3.5.9 is out now and Robert said he was waiting for that for a new build of Sqlite.NET so I'm hoping that might magically solve it!  If not may turn off pooling and see how that works and hope it's not too slow :(

 Another option may be to not use pooling and just open one SQLiteConnection and lock on that itself so only 1 thread ever uses it and never dispose of it - just dispose the commands and readers etc. ?

  

Any news on this ? We're getting this error rarely but it's still a pain - can't really pinpoint the problem here. We aren't using connection pooling but we do access the db files concurrently. Using System.Data.Sqlite 1.0.56.0 with Sqlite version 3.6.1, on a Windows machine.

If after upgrading to the most recent version of the library you are still getting this error, then please let me know.

 

 Hi Robert,

I'm stil having the same problem, but is random, only in certain computers happens, even the file is not corrupted  because I can use the file in another computer without the problems.

 Do you have an idea how to solve this issue?

 

BTW I'm using the latest version of the library.

 

Which error?  We've discussed a couple different errors in this thread I think.

 

The error that I'm getting is "Attempt to write a read-only database"

 This it is the stack trace:

System.Data.SQLite.SQLiteException: Attempt to write a read-only database attempt to write a readonly database at System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt) 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.ExecuteNonQuery() 

 Thanks in advance!

 Hi Robert,

 Do you have any ideas how this can be solved? I'm still havingo some randome issues in different computers.

 Thanks a lot for your help!

 

Not really.  I can't solve a problem I can't reproduce.  If you have a hard time catching this in a debugger, imagine how difficult it'd be for me to solve with nothing more than a report that it "sometimes" happens ... I'm not trying to make light of the issue at all -- quite the opposite.  It's a great concern to me that it happens, but I have no easy way to go about reproducing it.

I'm sorry for not coming out with this sooner, but I think I encountered this issue and solved it. (or one of the other guys I work with, maybe stealing credit here...)

: The theory is that it is an sqlite issue with read/write sharing when trying to open the file.

The database file might sometimes be used by other applications (virus scanners and other ongodly creations).
These applications have their own decision-making as to which file-sharing paradigm to use, if any - so they might just share read, or not share anything for that matter.

Since Sqlite in this scenario will be the second "client" for this file handle, and the other application didn't share both read and write, trying to open your database for writing will fail immediately with said error:

See the following on file sharing, and what happens in each case of first/second CreateFile calls, and permutations of their corresponding sharing flags (this is the win32 api call for openning files. Used by sqlite.) :
http://msdn.microsoft.com/en-us/library/aa363874(VS.85).aspx

Our solution was to open the file as soon as the app comes up - with read/write sharing - and keep the handle alive for the duration of the applications lifetime.
If the first attempt to open the file failes - we just retry this a few times. Normally it should succeed on the first attempt, but hey - this issue only proves how you should be extra cautious about this stuff...

I'm hoping this is actually the real problem you guys are facing and not something else...

בהצלחה

 silver83 thanks for charing your research with us! Our team also fall into "Attempt to write a read-only database" issue. It was caused by antivirus (NOD32 but I think it doesn't matter). Looks like it locks file while performing on-demand scaning. This causes SQLiteException with ErrorCode == SQLiteErrorCode.ReadOnly. We are using NHibernate and we catch  SQLiteException on flush and if ErrorCode == SQLiteErrorCode.ReadOnly we try flushing again. This solved issue for us.

This a reccurrent error in Vista and 7. The user must set the administrator privileges to the aplication in the properties. Otherwise the error appears.