Jon Galloway

Summary:SQLite is a great, free, embedable database. It's fast, tiny, and reliable, and has some cool features like support for NTFS compression of the database file. It's multithreaded, but its coarse locking can be a problem.

I first read about SQLite over at EggHeadCafe researching ASP.NET database cache dependencies. It's small, fast, free, and there's a good SQLite ADO.NET provider on SourceForge. More about it here (PPT). I decided to use it for an RSS aggregator pet project. It worked well for a while, but I ran into problems when I began using it in a multi-threaded application (one thread downloading feeds, another marking a feed read when viewed).

SQLite is technically thread safe, but a write operation locks the entire database (more):
- Any resultset being step()'d through uses a shared read-only lock. 
- Any insert/update being executed requires an exclusive write lock. 

You would think the provider would wrap this with some retries or a reader / writer lock (the Python Sqlite wrapper handles retries), but it just throws the Database is Locked exception. I've filed a bug on this, but I'm not too hopeful - it may be viewed as "that's just how SQLite works".

I contemplated wrapping my database access with a TimedLock, but in the end I decided this was a good time to give SharpHSQL a shot. I'm not certain it will handle the multithreaded access, either, but the idea of a pure c# embeded database in my c# app appealed to me.

I think SQLite with the SQLite ADO.NET provider is great for applications with simple data needs, but watch out if you're planning to perform concurrent updates or inserts.