SQLite and Multithreading

Original Title: SQLite and multiple threads

SQLite supports three thread modes:
1. Single-thread mode
In this mode, mutual exclusion is not performed, and multithreading is insecure.
2. Multithreading
In this mode, using a single database connection in multiple threads is not safe, otherwise it is safe. A database connection cannot be shared among multiple threads)
3. Serial Mode
In this mode, SQLite is thread-safe. Note: use the same database connection without mutual exclusion in multiple threads)

The thread mode can be specified at compile time (when the SQLite library is compiled by source code), start time (when the SQLite application is initialized), or run time (when the database connection is created. Generally, the mode specified during running overwrites the specified mode at startup, and the mode specified at startup overwrites the mode specified at compilation. However, once the single-thread mode is specified, it cannot be overwritten.
The default thread mode is the serial mode.

 

Select thread mode during compilation
You can specify the thread mode by defining the sqlite_threadsafe macro. If this parameter is not specified, the serial mode is used by default. Define the macro sqlite_threadsafe = 1 to specify the serial mode; = 0 to use the single thread mode; = 2 to use the multi-thread mode.
The Return Value of the sqlite3_threadsafe () function can determine the thread mode specified during compilation. If the single-threaded mode is specified, the function returns false. If the serial or multi-threaded mode is specified, the function returns true. The sqlite3_threadsafe () function is earlier than the multi-threaded mode and the mode selection at startup and runtime. Therefore, it cannot distinguish between the multi-threaded mode and the serial mode, nor between the start and running modes.

The last sentence can be understood through the implementation of the sqlite3_threadsafe function.
Sqlite_api int sqlite3_threadsafe (void) {return sqlite_threadsafe ;}
If the single-thread mode is specified during compilation, the critical mutex logic is omitted during construction, so the serial mode or multi-thread mode cannot be specified at startup or runtime.

 

Select thread mode when starting
If the single thread mode is not specified during compilation, you can use the sqlite3_config () function to modify the thread mode during application initialization. The sqlite_config_singlethread parameter can be specified as the single-thread mode, sqlite_config_multithread can be specified as the multi-thread mode, and sqlite_config_serialized can be specified as the serial mode.

 

Select thread mode during running
If the single-thread mode is not specified at the time of compilation and startup, each database connection can be separately specified as a multi-thread mode or a serial mode at the time of creation, but cannot be specified as a single-thread mode. If it is set to single-thread mode during compilation or startup, you cannot specify multi-thread or serial mode during connection creation.
When creating a connection, use the third parameter of the sqlite3_open_v2 () function to specify the thread mode. Sqlite_open_nomutex identifies the connection to create multi-thread mode; sqlite_open_fullmutex identifies the connection to create a serial mode. If no identifier is specified, or sqlite3_open () or sqlite3_open16 () function is used to create a database connection, the specified thread mode will be used as the default thread mode during compilation or startup.