Detailed explanation on the usage of SQLite multi-threading

SQLite supports 3 thread 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 disables the locks on the database connection and prepared statement (prepared statement), so the same database connection or prepared statement cannot be used concurrently in multiple threads. 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 the SQLITE_OPEN_NOMUTEX flag.

Serial: SQLite is thread-safe. Enable all locks, including bCoreMutex and bFullMutex. Because the database connection and prepared statement have been locked, multi-threads cannot use these objects concurrently, and it becomes serial. It is enabled by default when the SQLITE_THREADSAFE =1 parameter is added when SQLite is compiled. 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 the 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 set the SQLITE_THREADSAFE preprocessing macro to 1 when compiling. 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 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.

sqlite3_threadsafe()The return value of the 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 is earlier than the multi-threaded mode and the mode selection at startup and runtime, it cannot distinguish between multithreaded mode and serial mode or between startup and runtime 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 single-threaded mode is specified at compile time, then 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 at compile time, 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 is specified as multi-threaded mode, and SQLITE_CONFIG_SERIALIZED is specified as serial mode.

Select thread mode at runtime
If single-threaded mode is not specified at compile time and startup, each database connection can be individually specified as multi-threaded mode or serial mode when created, but cannot be specified. For single-threaded mode. 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.

【Related Recommendations】

1. XML Free Video Tutorial

2. Li Yanhui XHTML Video Tutorial

3. XML Technical Manual

The above is the detailed content of Detailed explanation on the usage of SQLite multi-threading. For more information, please follow other related articles on the PHP Chinese website!