Managing sqlite database in a multithreaded environment

Question

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?

Answer 1

I think you should check whether you call sqlite3_config() function after sqlite3_initialize(). If then, the function sqlite3_config() returns SQLITE_MISUSE.

Here is some part of explanations about sqlite3-config() API concerned with the error code, SQLITE_MISUSE.

The sqlite3_config() interface may only be invoked prior to library initialization using sqlite3_initialize() or after shutdown by sqlite3_shutdown(). If sqlite3_config() is called after sqlite3_initialize() and before sqlite3_shutdown() then it will return SQLITE_MISUSE. Note, however, that sqlite3_config() can be called as part of the implementation of an application-defined sqlite3_os_init().

_{Source: http://www.sqlite.org/c3ref/config.html}

Answer 2

You can just let sqlite3 handle all of this for you and by default it should. The sqlite3 libraries should use SQLITE_THREADSAFE=1 by default (empahsis mine):

SQLITE_THREADSAFE=<0 or 1 or 2>
This option controls whether or not code is included in SQLite to enable it to operate safely in a multithreaded environment. The default is SQLITE_THREADSAFE=1 which is safe for use in a multithreaded environment.

And SQL_CONFIG_SERIALIZED should also be used by default also (emphasis mine):

SQLITE_CONFIG_SERIALIZED
There are no arguments to this option. This option sets the threading mode to Serialized. In other words, this option enables all mutexes including the recursive mutexes on database connection and prepared statement objects. In this mode (which is the default when SQLite is compiled with SQLITE_THREADSAFE=1) the SQLite library will itself serialize access to database connections and prepared statements so that the application is free to use the same database connection or the same prepared statement in different threads at the same time.

However, you can also change it yourself with a call to sqlite3_config before initialisation:

sqlite3_config(SQL_CONFIG_SERIALIZED);

You should then be able to open your database using SQLITE_OPEN_FULLMUTEX:

sqlite3* pDatabase;
sqlite3_open_v2("MyDatabase.db", &pDatabase, SQLITE_OPEN_FULLMUTEX, nullptr);

You can also use a std::mutex to prevent access to your sqlite3 calls, but this shouldn't be necessary since sqlite3 handles it for you (but if for some reason you have built the libraries differently for some reason, this would be viable).