Observations on Embedded databases
I spent significant parts of the last two weeks dealing with embedded databases. I have used, SQL CE, SQLite, FireBird, db4o and Berkeley DB.
My requirements were really simple, or so I thought. I just wanted safe for multi threading and support for transactions.
Let me go over them in order:
SQL CE
This is a really nice DB, syntax is comparable to SQL Server, so it makes a lot of things simpler. It has transaction support and is supposed to be multi threaded safe.
It is not.
It is very easy to get SQL CE into situations where it hang. Usually when it is attempting to open the database. Oh, and there is no lock timeout for this issue, so it literally hang.
Result: overruled.
SQLite
I just love this DB. It is simple, straightforward, and aside from crazy date manipulation support, just works. It has both transactions and multi threading support.
However, multi threading support is gained by locking the entire database. This is an acceptable behavior, in most scenarios, but for my needs, it meant that threads stepped on each other all too often, and that wasn't acceptable.
FireBird
I had a really hard time getting this to work correctly. I got some recommendations for it, so I decided to go for it.
It doesn't handle transaction isolation, so I never actually got to the point of testing multi threading behavior.
db4o
This was my first foray into using db4o, so take anything that I say with a grain of salt.
It looked like it would be a good solution. However, I couldn't figure out how to get two threads share the same file. Each connection lock the file, so they cannot be used concurrently. Using the server version might solve this, but I am looking for embedded solution, not server solution.
Berkeley DB
I spent the most amount of time here. And it is almost there.
I had one critical issue that I managed to overcome, but then BDB totally killed itself when it hang in my tests. After tearing up a lot of hair, I ended up finding out that there is a known bug in version 4.5 (which is the latest version that has a .NET binding) that can cause this.
Conclusion
I start using Dictionary as my data store. Just don't restart the server.