Firebird is the best in embedded area
Here is the full thread on nhibernat and the quote
“However, the thing that finally made us switch from SQLite to Firebird was the fact that it only support one open transaction at a time. This makes it essentially useless for multithreaded/multiuser applications. SQLite is excellent for some tasks, but if you have more than one thread/process accessing the database at the same time you really should consider using another database. ”
While it is true that SQLite only supports one transaction at a time, it does not follow that it is “essentially useless for multithreaded/multiuser applications”. In fact SQLite works wonderfully for multi-threaded applications, and in some cases can run *faster* than other databases as it has lower transaction overhead than most larger RDBMs with fine-grained locking.
SQLite has very good thread support, and you need only follow the basic concurrency rules (one connection per thread) and transaction rules (BEGIN IMMEDIATE) for it to work well. Furthermore, is supports shared cache mode where multiple threads can work from a single connection.
Don’t get me wrong, I think Firebird is a fine database — I have used it in production systems before and have been very happy with it. And it is certainly true that there are applications where Firebird is more well-suited, but multi-threaded applications are certainly not outside the domain of SQLite. In fact, my basic benchmarks show SQLite getting 1,500 TPS with 200 threads concurrently hitting a single database:
That’s not too bad for most applications. And if that that weren’t enough, it is not hard to patch SQLite to keep that same level of performance with over twice that number of threads.