The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

sqlite w/ multiple threads on MS-Win

Can an sqlite database be accessed from multiple threads (either by opening new handles from each thread or passing the same database pointer to all the threads)?

I did some googling and found some information, but the answer was kinda vague for me.

If you've ever used it in this type of scenario, please let me know how it went.
outback Send private email
Thursday, January 10, 2008
from what I understand it is possible.  it creates a locks a second file to do transactions.
Thursday, January 10, 2008
Sqlite 2 had a limitation that it locked entire tables rather than individual records so could be slow with many users - don't know if this is fixed in 3
Martin Send private email
Thursday, January 10, 2008
If memory serves me correctly, you want to have each thread have it's own handle to the DB.  Sharing a handle across threads was not kosher last time I read the FAQ.
Tom Send private email
Thursday, January 10, 2008
My Windows app pounds on a few SQLite 3 databases.  Multiple threads can each have their own handle to the database and access it 'concurrently'.  I say 'concurrently' because in reality as soon as one database connection starts a transaction, the entire database file is locked for the duration, and the others will have to wait.

Recent versions (3.5 and above I believe) now allow you to pass a single database handle around to multiple threads, but I'm fairly certain that you have to guarantee that the handle is only used by one thread at any time (so you might as well just have a connection/handle per thread).

3.5 also introduced shared-cache, where each thread can share a cache of database pages.  It seems like that could increase throughput, but I haven't played with it much.
Thursday, January 10, 2008
I should also mention that the SQLite newgroup is very active and extremely helpful.  You'll get very solid advice there from people that know more about SQLite than I do.
Thursday, January 10, 2008
Thank you
Thursday, January 10, 2008

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz