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