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

Solution 1:

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).

Solution 2:

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}