I'm playing around a bit with this ADO.NET and it looks great so far. One problem that I'm running into is that it keeps throwing this exception: "Database is locked" when my application is trying to open new connection to the database. Does anybody know what causes the problem? I'm looking for any documentation for this library and unable to find any.
I ran into that problem a few times myselof. Generally it's when another part of your code still has a connection open and is trying to modify a table. Try making sure that all your connections get closed when you're finished with them.
I'm using this library in the multi-threaded environement. I'll double check to make sure that I already close all connections after I'm done with them. However, is there a maximum number of con-current connection that this library supports? My application usually has only 2-3 connections open at any time.
I'm not sure what the max. allowed conncetions is, but I'm pretty sure that you can only have one connection that is modifying a particular database at a time. That's not a limitation of this library, but rather of sqlite itself (as far as I understand). You should be able to have multiple concurrent connections that are read-only, though.
Thanks wide-awake. That clears few things up. I think that limit (if you're right) definitely causes this exception in my application. Let me modify my codes a bit and find out. Do you think if there is any performance problem with this limit?
I'm not really sure how it impacts performance. a brief test on my computer looks like it performs ok with multiple connections (based on doing a bunch of selects on big tables in a ~300MB database). I guess it depends on your application -- being limited to one connection that modifies the database might affect performance, but you can probably work around it as long as you keep that limitation in mind when designing your app.
As I understand it, SQLlite is MT-safe (even multiple-process safe). Even if not, it would be nice if writes just waited for a lock rather than throwing an exception. (I'm thinking of porting my app from SqlServer to SQLite and it definitely assumes that multiple threads can write to the database).
I believe multithreading is safe, but only one thread can be writing to the database at a time. A write operation will return the SQLITE_BUSY failure indicator if you try concurrent writes. Theoretically you can handle this in your app and try again, but I don't know how this is handled in this ado.net implementation. Anyone else out there tried this?
Here's a link to the relevant part of the sqlite faq:
PS: had a brief look at the code, and it looks like the Command.ExecuteNonQuery() method (at least) will throw an appropriate exception indicating a SQLITE_BUSY state. You should be able to handle this and deal with pseudo-concurrent (as in multithreaded) writes.
I have also noticed this, and I have a single connection open. It happens after reading data using a datareader (and then closing the data reader), and trying to write to the table... maybe the datareader is not being closed properly?