SQLite - ALMOST a great embedded database solution for .NET applications
A comment on my post
about the SQL/e announcement said it sounds a lot
like SQLite. The subject warrents a
separate post. SQLite is a great embedded database. It's free, open source,
small, fast, ACID
compliant, and frequently updated.
SQLite is an ideal embedded database provided you
don't care about concurrency. That's because any write
operation locks the entire database
. Even
Microsoft
Access beats that; it's had record level locking for a while.
That's not a complete show stopper, even for multi-threaded applications, if
your data provider handles SQLITE_BUSY errors correctly. By "handling the
error", I mean repeatedly trying the database operation until it
succeeds or the connection or command timeout is reached, which is kind of
sad but works. I submitted a bug
report for the ADO.NET
Data Provider for SQLite project almost a year ago about this exact issue;
the fix was made quickly, but the result hasn't helped anyone since
this project hasn't produced a new release since August 2005. Unless you want to
mess with building from the SourceForge CVS repository, you're out of luck under
.NET 1.1.
However, there's a new project for a .NET 2.0 data provider for SQLite
which appears to handle
database locks correctly. So that's good.
Still, before SQLite can compete with Firebird or SQL/e,
it needs to clean up
its act when it comes to multi-threading. That means moving beyond just
publishing best
practices on how to work around the quirks of a database engine which has two
lock states (locked / not locked) to table, page, and row level locks. I
think Shailesh has a pretty good
suggestion for adding a real locking
strategy to SQLite. Might be a good idea if they integrate this before Mozilla integrates
SQLite as their new Unified Storage system.
And a general recommendation for using any embedded database engine - use an
abstration layer. I learned this the hard way; an OR/M like NHibernate
would have saved me a lot of time and frustration.