In depth use of SQLite multi threading summary

SQLite supports three threading modes:
Single thread: in this mode, there is no mutual exclusion, so it is not safe to use multiple threads. Disable all mutex locks. Errors occur when used concurrently. SQLITE_THREADSAFE=0 when SQLite is compiled, or when sqlite3_config(SQLITE_CONFIG_SINGLETHREAD) is called before SQLite is initialized.

Multithreading: in this mode, a database connection is safe as long as it is not used by multiple threads at the same time. The source is to enable bCoreMutex, disable bFullMutex. Essentially, you disable locks on database connections and prepared statements, so you can't use the same database connection or prepared statement concurrently in multiple threads. Enabled by default when SQLite is compiled with the SQLITE_THREADSAFE=2 parameter. If SQLITE_THREADSAFE is not 0, sqlite3_config(SQLITE_CONFIG_MULTITHREAD) can be invoked before initializing SQLite. Or set the SQLITE_OPEN_NOMUTEX flag when creating a database connection.

Serial: sqlite is thread safe. Enable all locks, including bCoreMutex and bFullMutex. Because database connections and prepared statements are locked, multiple threads cannot use these objects concurrently and become serial. Enabled by default when SQLite is compiled with the SQLITE_THREADSAFE =1 parameter. If SQLITE_THREADSAFE is not 0, you can call sqlite3_config(SQLITE_CONFIG_SERIALIZED) before initializing SQLite. Or when creating a database connection, set SQLITE_OPEN_FULLMUTEX flag& anywhere; .

      By initialization, I mean calling the sqlite3_initialize() function, which is automatically called when sqlite3_open() is called and only valid the first time.

    To be thread-safe, SQLite must set the SQLITE_THREADSAFE preprocessor macro to 1 at compile time. On both Windows and Linux, this is set up in a compiled binary distribution. If you're not sure if the library you're using is thread-safe, call the sqlite3_threadsafe() interface to find out. Call sqlite3_threadsafe() to get the SQLITE_THREADSAFE parameter at compile time.

    That is, the threading mode can be specified at compile time (when the sqlite library is compiled through source code), at startup (when an application using sqlite is initialized), or at run time (when a database connection is created). In general, the pattern specified at runtime overrides the pattern specified at startup, and the pattern specified at startup overrides the pattern specified at compile time. However, the single-threaded mode, once specified, cannot be overridden. The default thread mode is serial mode.

Thread mode is selected at compile time
      You can specify the threading pattern by defining the SQLITE_THREADSAFE macro. If not specified, the default is serial mode. Define the macro SQLITE_THREADSAFE=1 to specify the use of serial mode; =0 use single thread mode; = 2 use multithreaded mode.

      The return value of the sqlite3_threadsafe() function determines the thread pattern specified at compile time. If single-threaded mode is specified, the function returns false. The function returns true if serial or multithreaded mode is specified. Because the sqlite3_threadsafe() function predatesmultithreaded mode and the choice of modes at startup and runtime, it can neither distinguish between multithreaded mode and serial mode nor between startup and runtime mode.

      The last sentence can be understood by the implementation of sqlite3_threadsafe function to understand the SQLITE_API int sqlite3_threadsafe(void){return SQLITE_THREADSAFE; If the single-threaded mode is specified at compile time, the critical mutex logic is omitted at construction time, and therefore serial or multithreaded mode cannot be specified at startup or run time.

Select thread mode at startup
      If the single-threaded mode is not specified at compile time, you can modify the threaded mode at application initialization using the sqlite3_config() function. The parameter SQLITE_CONFIG_SINGLETHREAD can be specified as
Single-threaded mode, SQLITE_CONFIG_MULTITHREAD is specified as multithreaded mode, and SQLITE_CONFIG_SERIALIZED is specified as serial mode.

Thread mode is selected at run time
      If the single-threaded mode is not specified at compile time and startup time, then each database connection can be individually specified in multithreaded or serial mode at creation time, but not in single-threaded mode. If you specify single-threaded mode at compile time or at startup, you cannot specify multithreaded or serial mode when creating a connection.

      The thread mode is specified with the third argument of the sqlite3_open_v2() function when the connection is created. SQLITE_OPEN_NOMUTEX identifies the connection to create a multithreaded pattern. SQLITE_OPEN_FULLMUTEX identifies the connection created in serial mode. If no identity is specified, or if the sqlite3_open() or sqlite3_open16() functions are used to create a database connection, the thread mode specified at compile time or startup is used as the default thread mode.