In-depth usage of SQLite Multithreading

SQLite supports three thread modes:
Single thread: In this mode, mutual exclusion is not performed, and multithreading is insecure. Disable all mutex locks. errors may occur during concurrent use. This parameter is enabled when SQLITE_THREADSAFE = 0 is added during SQLite compilation, or when sqlite3_config (SQLITE_CONFIG_SINGLETHREAD) is called before SQLite initialization.

Multithreading: In this mode, as long as a database connection is not used by multiple threads at the same time, it is safe. BCoreMutex is enabled in the source code and bFullMutex is disabled. In fact, it is to disable the locks on database connections and prepared statement (prepared statements). Therefore, the same database connection or prepared statement cannot be used concurrently in multiple threads. It is enabled by default when SQLITE_THREADSAFE = 2 is added during SQLite compilation. If SQLITE_THREADSAFE is not 0, you can call sqlite3_config (SQLITE_CONFIG_MULTITHREAD) to enable it before initializing SQLite, or set SQLITE_OPEN_NOMUTEX flag when creating a database connection.

Serial: sqlite is thread-safe. Enable all locks, including bCoreMutex and bFullMutex. Because the database connection and prepared statement have been locked, when using these objects in multiple threads, they cannot be concurrently and become serialized. This parameter is enabled by default when SQLITE_THREADSAFE = 1 is added during SQLite compilation. If SQLITE_THREADSAFE is not 0, you can call sqlite3_config (SQLITE_CONFIG_SERIALIZED) to enable it before initializing SQLite, or set SQLITE_OPEN_FULLMUTEX flag when creating a database connection.

The initialization here refers to calling the sqlite3_initialize () function. This function is automatically called when sqlite3_open () is called, and only the first call is valid.

To ensure thread security, SQLite must set the SQLITE_THREADSAFE preprocessing macro to 1 during compilation. On Windows and Linux, compiled binary distributions are set in this way. If you are not sure whether the library you are using is thread-safe, you can call the sqlite3_threadsafe () interface to find out. Call sqlite3_threadsafe () to obtain the SQLITE_THREADSAFE parameter during the compilation period.

That is to say, the thread mode can be specified during compilation (when the sqlite library is compiled by source code), startup (when the sqlite application is initialized), or Runtime (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, therefore, the critical mutex logic is omitted during construction. Therefore, the serial mode or multithreading 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
Single-thread mode. SQLITE_CONFIG_MULTITHREAD is used as the multi-thread mode, and SQLITE_CONFIG_SERIALIZED is used 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.