Multithreading of SQLite

Is SQLite thread-safe?

Sometimes yes. To ensure thread security, SQLite must set the threadsafe preprocessing macro to 1 during compilation. In the default released compiled version, Windows is thread-safe, but Linux is not. If thread security is required, the Linux version must be re-compiled.

"Thread security" refers to the "sqlite3" structure returned by two or three threads that can call different sqlite3_open () at the same time. Instead of using the same sqlite3 structure pointer in multiple threads.

A sqlite3 structure can only be used in the process that calls sqlite3_open to create it. You cannot open a database in one thread and pass the pointer to another thread for use. This is because of the limitations of most multithreading systems (or bugs ?) For example, on redhat9. In these problematic systems, the fcntl () Lock created by one thread cannot be deleted or modified by another thread. Because SQLite relies on the fcntl () lock for concurrency control, serious problems may occur when database connections are transferred between threads.

Maybe there is a way to solve the fcntl () lock problem in Linux, but it is very complicated and it will be extremely difficult to test the correctness. Therefore, SQLite currently does not allow sharing handles between threads.

In UNIX, you cannot use a fork () system call to put an open SQLite database into a sub-process. Otherwise, an error occurs.

 

 

Multi-process can open the same database at the same time, or select at the same time. However, only one process can change the database immediately.

SQLite uses read/write locks to control database access. (The Win95/98/me operating system does not support read/write locking. In versions earlier than 2.7.0, this means that only one process can read the database at a time in windows. In version 2.7.0, this problem is solved by executing a user interval probability read/write lock policy in the Windows interface code .) However, if the database file is in an NFS file system, the locking mechanism for controlling concurrent reading may fail. This is because the fcntl () file of NFS is sometimes locked. If multiple processes may read the database concurrently, avoid placing the database file in the NFS file system. According to Microsoft's documentation, if you do not run the cmd.exe background program, the lock in the FAT file system may not work. People who are very experienced in Windows tell me that the locking of network files has many problems and is not reliable. If so, sharing an SQLite database file in two or more Windows systems will cause unpredictable problems.

We know that no other embedded SQL database engine supports more concurrency than SQLite. SQLite allows multiple processes to open and read databases at the same time. When any process needs to be written, the entire database will be locked in this process. However, this generally takes only a few milliseconds. Other processes only need to wait and continue other transactions. Other embedded SQL database engines often only allow single-process access to the database.

However, Client/Server database engines (such as PostgreSQL, MySQL, and Oracle) usually support higher concurrency and multi-process writing to the same database at the same time. Since there is always a well-controlled server that coordinates database access, this ensures the implementation of the above features. If your application requires high concurrency, you should consider using the Client/Server database. In fact, experience tells us that most applications require less concurrency than their designers think.

When SQLite attempts to operate a file locked by another process, the default action is to return sqlite_busy. You can use the C code to change this line. Use the sqlite3_busy_handler () or sqlite3_busy_timeout () API function.

If two or more processes open the same database at the same time, and one of the processes creates a new table or index, other processes may not immediately see the new table. Other processes may need to be shut down and re-linked to the database.

--------------------------------------------------------------------------------