I am creating a web application that is accessing a SQLite database in the server. I also have "clients" that updates this same database. As we know SQLite locks the entire database during INSERTs which are done by the clients and the web application is also trying to make some UPDATEs at the same time. So my problem now is about concurrency in database access. I would like to use an embeddable database like SQLite. Any suggestions.
A:
Thought about MySQL? We're using it in our application an it works great.
duduamar
2010-04-12 19:16:19
+1
A:
You've got to the point where I would be switching to using a separate database server. The SQLite wiki has a page on when to use SQLite and when not; highly concurrent writes are one of the times when you're beyond its architecture (which deliberately doesn't handle this case well so it can do better at others).
PostgreSQL and MySQL are probably your first ports of call.
Donal Fellows
2010-04-12 19:32:58
+3
A:
If you value convenience over raw performance the database bundled with Java 6, JavaDB/Derby, may work well for you. It offers row-level locking and has it enabled by default.
BigZig
2010-04-12 21:13:12
+4
A:
H2 database the new thing from the creator of Hypersonic SQL: H2 stands for Hypersonic 2, however H2 does not share code with Hypersonic SQL or HSQLDB. H2 is built from scratch.
raticulin
2010-04-12 21:28:46
How do you compare H2 database with HSQLDB or JavaDB. I am more interested in concurrency since I don't want the database to be locked for UPDATEs when I am doing INSERTs. I believe SQLite locks the database when you perform any write to the database.
dexter
2010-04-15 15:06:18
see http://www.h2database.com/html/features.html#comparison for a detailed comparison with Derby and others (not SQLite though), it details locking there.
raticulin
2010-04-15 15:36:32
have you personally used it in production environment? how was it?
dexter
2010-04-15 15:44:13
myself not in production. But look at other's experience http://stackoverflow.com/questions/827220/any-real-world-experience-with-h2-database
raticulin
2010-04-16 10:34:50