SQLite concurrency issue a deal breaker?

Question

I am looking at databases for a home project (ASP.NET MVC) which I might host eventually. After reading a similar question here on Stack Overflow I have decided to go with MySQL.

However, the easy of use & deployment of SQLite is tempting, and I would like to confirm my reasons before I write it off completely.

My goal is to maintain user status messages (like Twitter). This would mean mostly a single table with user-id/status-message couples. Read / Insert / Delete operation for status message. No modification is necessary.

After reading the following paragraph I have decided that SQLite can't work for me. I DO have a simple database, but since ALL my transaction work with the SAME table I might face some problems.

SQLite uses reader/writer locks on the entire database file. That means if any process is reading from any part of the database, all other processes are prevented from writing any other part of the database. Similarly, if any one process is writing to the database, all other processes are prevented from reading any other part of the database.

Is my understanding naive? Would SQLite work fine for me? Also does MySQL offer something that SQLite wouldn't when working with ASP.NET MVC? Ease of development in VS maybe?

Answer 1

If you're willing to wait half a month, the next SQLite release intends to support write-ahead logging, which should allow for more write concurrency.

Answer 2

I've been unable to get even the simple concurrency SQLite claims to support to work - even after asking on SO a couple of times.

Edit Since I wrote the above, I have been able to get concurrent writes and reads to work with SQLite. It appears I was not properly disposing of NHibernate sessions - putting Using blocks around all code that created sessions solved the problem. /Edit

But it's probably fine for your application, especially with the Write-ahead Logging that user380361 mentions.

Small footprint, single file installation, fast, works well with NHibernate, free, public domain - a very nice product in almost all respects!