mardi 25 août 2015

Managing sqlite database in a multithreaded environment

When using the sqlite C++ library, I can use sqlite3_open_v2 to open a database. This will produce an handle to the database, and a pointer to that handle will be set.

Using that pointer, I can call sqlite3_prepare_v2 to prepare a sqlite statement, then I can use sqlite3_step to step through the results of the query.

Now, I am working in an environment where I have several multiple threads that continuously get created and destroyed (it is a server application that spawns new threads to serve incoming, possibly concurrent connections). Now, as far as my understanding goes, I should be creating new handles to the same database with a call to sqlite3_open_v2 every time a new thread is created. However, this adds a significant computational overhead since it can take a while to create a new connection to the database and I need to handle a lot of connections.

So I was wondering if there was a more efficient way to achieve this. Is there a way, for example, to just mutex everything to solve my problems? I can mutex my calls to the only connection object I have: this serializes my communications with the database.

Would this work? Or is there a reason why I can't use the same connection object from several different threads, even if I avoid any form of concurrency?

And if this can work, should I just serialize my calls to sqlite3_prepare_v2, or my first call to sqlite3_step, or all my calls to sqlite3_step? I mean: when I call step for the first time, all the results get loaded or communication with the actual database file takes place every time I call step?

The difference would be between mutexing only the call to prepare, and locking everything until I have finished stepping through the results.

Is something like this feasible, should I just create new connections to the database every time and let sqlite handle all of the concurrency, or am I missing something important that trivially solves my problem?

Aucun commentaire:

Enregistrer un commentaire