SQLite Connection Pool in Java - Locked Database

Question

I have already read several related threads about SQLite in multi-threaded environments but I could not find a clear statement, so forgive me that I bring up this topic yet another time.

I need to access the application's database from multiple threads to execute update statements. The database itself can be SQLite or MySQL, depending on the users choice. For the MySQL handler, I have implemented a connection pool using the tomcat-jdbc library. Now I am searching the best way for the SQLite handler, since normal connection-pooling should not work as SQLite only supports one writing connection at a time that locks the whole database.

Should I just go ahead and make all Threads using the same connection one after another (by synchronizing the connection?) or should I create a connection pool using the already present tomcat library that holds just connection? Might a connection pool with one connection be oversized?

Answer 1

Since it is single file embedded database, pooling will hardly be beneficial.

https://web.archive.org/web/20130410192252/http://sqlite.phxsoftware.com/forums/p/682/3034.aspx

Since the original site is down, I will provide the summary:

I have my pooling implemented and 1000 commands using a pooled connection and the pooled speed was much closer to the single-connection speed.

Many non-pooled connections : 2875.0 ms
Many pooled connections     :   93.8 ms
Pooled with reset           :  546.9 ms
One Connection              :   46.9 ms

So the consensus is that pooling is not very beneficial.

http://www.sqlite.org/faq.html#q5

http://www.sqlite.org/threadsafe.html