An in-depth summary of the use of SQLite multi-threading

SQLite supports 3 threading modes:
Single thread: In this mode, there is no mutual exclusion and multi-threading is unsafe. Disable all mutex locks, and errors will occur when used concurrently. It is enabled when the SQLITE_THREADSAFE=0 parameter is added when SQLite is compiled, or when sqlite3_config(SQLITE_CONFIG_SINGLETHREAD) is called before initializing SQLite.

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

　 Serial: SQLite is thread-safe. Enable all locks, including bCoreMutex and bFullMutex . Because the database connection and prepared statements are all locked, so when multiple threads use these objects, they cannot be used concurrently, and it becomes serial. SQLITE_THREADSAFE is added when SQLite is compiled =1 parameter is enabled by default. If SQLITE_THREADSAFE is not 0, you can call sqlite3_config(SQLITE_CONFIG_SERIALIZED) to enable it before initializing SQLite; or when creating a database connection, set SQLITE_OPEN_FULLMUTEX flag .

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

In order to achieve thread safety, SQLite must be compiled when compiling Set the SQLITE_THREADSAFE preprocessor macro to 1. On Windows and Linux, This is the setting in good compiled binary distributions. 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 get the compile-time SQLITE_THREADSAFE parameter.

That is to say, the thread mode can be specified at compile time (when the SQLite library is compiled from source code), at startup (when the application using SQLite is initialized), or at runtime (when the database connection is created). Generally speaking, the mode specified at run time will override the mode specified at startup, and the mode specified at startup will override the mode specified at compile time. However, once single-threaded mode is specified, it cannot be overridden. The default thread mode is serial mode.

Select thread mode when compiling
You can specify the thread mode by defining the SQLITE_THREADSAFE macro. If not specified, defaults to serial mode. Define the macro SQLITE_THREADSAFE=1 to specify the serial mode; =0 to use the single-threaded mode; =2 to use the multi-threaded mode.

The return value of the sqlite3_threadsafe() function can determine the thread mode specified at compile time. If single-threaded mode is specified, the function returns false. If serial or multi-threaded mode is specified, the function returns true. Since the sqlite3_threadsafe() function precedes the multi-threaded mode and the mode selection at startup and runtime, it cannot distinguish between multi-threaded mode and serial mode or startup and runtime modes.

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

Select thread mode at startup
If single-threaded mode is not specified when compiling, you can use the sqlite3_config() function to modify the thread mode during application initialization. The parameter SQLITE_CONFIG_SINGLETHREAD can be specified as single-threaded mode, SQLITE_CONFIG_MULTITHREAD as multi-threaded mode, and SQLITE_CONFIG_SERIALIZED as serial mode.

Select thread mode at runtime

If single-threaded mode is not specified at compile time or startup, each database connection can be individually specified as multi-threaded mode or serial mode when created, but cannot be specified as single-threaded model. If single-threaded mode is specified at compile time or startup, you cannot specify multi-threaded or serial mode when creating the connection.

Use the third parameter of the sqlite3_open_v2() function to specify the thread mode when creating a connection. SQLITE_OPEN_NOMUTEX identifies a connection created in multi-threaded mode; SQLITE_OPEN_FULLMUTEX identifies a connection created in serial mode. If no identifier is specified, or the sqlite3_open() or sqlite3_open16() function is used to create a database connection, the thread mode specified at compile time or startup time will be used as the default thread mode.

The above is an in-depth summary of the use of SQLite multi-threading. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!