SQLite - Database is locked
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.