Concurrent Control in SQLite

This document describes the technologies to concurrent access to a SQLite database. There is also some code analysis to reveal the low level implementations.

Multi-process

See the Official FAQs Can multiple applications or multiple instances of the same application access a single database file At the same time?

Multiple processes can has the same database open at the same time. Multiple processes can is doing a SELECT at the same time. But one process can is making changes to the database at any moment in time, however.

SQLite uses Reader/writer locks to control access to the database. (under Win95/98/me which lacks support for reader/writer locks, a probabilistic simulation is used instead.) Caution:this locking mechanism might not work correctly if the database file was kept on an NFS filesystem. This is because Fcntl () file locking are broken on many NFS implementations. You should avoid putting SQLite database files on NFS If multiple processes might try to access the file at the same time. On Windows, Microsoft's documentation says that locking may isn't work under FAT filesystems if you're not running the Sha Re.exe Daemon. People who has a lot of experience with Windows tell me that file locking of the network files is very buggy and are not depen Dable. If what they say are true, sharing an SQLite database between the or more Windows machines might cause unexpected problems.

We is aware of no other embedded SQL database engine, that supports as much concurrency as SQLite. SQLite allows multiple processes to has the database file open at once, and for multiple processes to read the database a T once. When any process wants to write, it must lock the entire database file for the duration of its update. But this normally only takes a few milliseconds. Other processes just wait for the writer to finish then continue about their business. Other embedded SQL database engines typically-a single process-to-connect to the database at once.

However, Client/server database engines (such as PostgreSQL, MySQL, or Oracle) usually support a higher level of concurren Cy and allow multiple processes to being writing to the same database at the same time. This was possible in a client/server database because there was always a single well-controlled server process available to Coordinate access. If your application have a need for a lot of concurrency and then you should consider using a client/server database. But experience suggests the most applications need much less concurrency than their designers imagine.

When SQLite tries-to access a file-is-locked by another process, the default behavior are to return sqlite_busy. You can adjust this behavior from C code using the Sqlite3_busy_handler () or sqlite3_busy_timeout () API functions.

Code Analysis

Multi-process concurrent control is implemented using FCNTL on Linux/unix, Lockfileex/unlockfileex on Windows, and Probabi Listic locking on Win95/98/me.

See https://sqlite.org/lockingv3.html for more details

• Unlocked No Locks is held on the database. The database may neither read nor written. Any internally cached data are considered suspect and subject to verification against the database file before being used. Other processes can read or write the database as their own locking states permit. The default state.
• SHARED The database may is read but not written. Any number of processes can hold GKFX locks at the same time, hence there can many simultaneous. But no other thread or process was allowed to write to the database file while one or more SHARED locks is active.
• RESERVED A RESERVED Lock means that the process was planning on writing to the database file at some point in the future BU T that it was currently just reading from the file. Only a single RESERVED lock is active at one time, though multiple gkfx locks can coexist with a single RESERVED loc K. RESERVED differs from PENDING on that new GKFX locks can be acquired while there is a RESERVED lock.
• PENDING A PENDING Lock means that the process holding the lock wants to write to the database as soon as possible and is J UST waiting on all the SHARED locks to clear so, it can get an * * EXCLUSIVE lock. No New SHARED locks is permitted against the database if a PENDING lock is active, though existing GKFX locks are allow Ed to continue.
• EXCLUSIVE an EXCLUSIVE lock was needed in order to write to the database file. Only one EXCLUSIVE lock was allowed on the file and no other locks of any kind be allowed to coexist with an EXCLUSIVE loc K. In order to maximize concurrency, SQLite works to minimize the amount of time that EXCLUSIVE locks is held.

Multi-thread

See Using SQLite in multi-threaded applications

1 overview

SQLite supports three different threading modes:

Single-thread. In this mode, all mutexes was disabled and SQLite is unsafe to use with more than a single thread at once.

Multi-thread. In this mode, SQLite can being safely used by multiple threads provided that no single database connection is used Simultaneo usly in, or more threads.

Serialized. In serialized mode, SQLite can is safely used by multiple threads with no restriction.

The threading mode can be selected at Compile-time if the SQLite library is a being compiled from source code) or at the star T-time (when the application, intends to use SQLite are initializing) or at Run-time (when a new SQLite database Connec tion is being created). Generally speaking, Run-time overrides Start-time and Start-time overrides Compile-time. Except, Single-thread mode cannot be overridden once selected.

The default mode is serialized.

2 compile-time Selection of threading mode

Use the Sqlite_threadsafe compile-time parameter to selected the threading mode. If no Sqlite_threadsafe compile-time parameter is present and then serialized mode is used. This can is made explicit with-dsqlite_threadsafe=1. With-dsqlite_threadsafe=0 the threading mode is Single-thread. With-dsqlite_threadsafe=2 the threading mode is multi-thread.

The return value of the Sqlite3_threadsafe () interface is determined by the Compile-time threading mode selection. If single-thread mode is selected at Compile-time and then Sqlite3_threadsafe () returns false. If either the Multi-thread or serialized modes is selected, then Sqlite3_threadsafe () returns True. The Sqlite3_threadsafe () interface predates the Multi-thread mode and Start-time and Run-time mode selection and so are Una ble to distinguish between multi-thread and serialized mode nor are it able to report Start-time or Run-time mode changes.

If single-thread mode is selected at Compile-time, then critical mutexing logic was omitted from the build and it is Imposs Ible to enable either Multi-thread or serialized modes at start-time or Run-time.

3 Start-time Selection of threading mode

Assuming that the Compile-time threading mode was not Single-thread and then the threading mode can be changed during Initiali Zation using the Sqlite3_config () interface. The sqlite_config_singlethread verb puts SQLITE into single-thread mode, the sqlite_config_multithread verb sets multi-thr EAD mode, and the sqlite_config_serialized verb sets serialized mode.

4 run-time Selection of threading mode

If single-thread mode has no been selected at Compile-time or start-time and then individual database connections can is CRE Ated as either Multi-thread or serialized. It is a possible to downgrade a individual database connection to Single-thread mode. Nor is it possible to escalate a individual database connection if the Compile-time or Start-time mode is single-thread.

The threading mode for a individual database connection is determined by flags given as the third argument to Sqlite3_ope N_V2 (). The SQLITE_OPEN_NOMUTEX flag causes the database connection to being in the Multi-thread mode and the Sqlite_open_fullmutex F Lag causes the connection to is in serialized mode. If neither flag is specified or if Sqlite3_open () or Sqlite3_open16 () was used instead of SQLITE3_OPEN_V2 (), then the DEFA Ult mode determined by the Compile-time and Start-time settings are used.

Code analysissqlite_threadsafe=0

See below code, once sqlite_threadsafe=0, the mutex operations is omitted. That's why if Single-thread mode was selected at Compile-time, it's impossible to enable either multi-thread or serialized Modes at start-time or Run-time.

#if SQLITE_THREADSAFE
  sqlite3_mutex *mutex;
#endif

#if SQLITE_THREADSAFE
# include 
# define SQLITE_UNIX_THREADS 1
#endif

Sqlite_threadsafe=1 and sqlite_threadsafe=2

Sqlite_threadsafe=1 means serialized mode, and-dsqlite_threadsafe=2 means multi-thread mode.

In both modes, mutexes is used to protect critical resource. So what ' s the difference? See below Code,

Global.c

SQLITE_WSD struct Sqlite3Config sqlite3Config = {
   SQLITE_DEFAULT_MEMSTATUS,  /* bMemstat */
   1,                         /* bCoreMutex */
   SQLITE_THREADSAFE==1,      /* bFullMutex */

Main.c

isThreadsafe = sqlite3GlobalConfig.bFullMutex;
...
if( isThreadsafe ){
  db->mutex = sqlite3MutexAlloc(SQLITE_MUTEX_RECURSIVE);

Vdbeblob.c

148 sqlite3_mutex_enter(db->mutex); in sqlite3_blob_open() 
361 sqlite3_mutex_enter(db->mutex); in sqlite3_blob_close() 
388 sqlite3_mutex_enter(db->mutex); in blobReadWrite() 
486 sqlite3_mutex_enter(db->mutex); in sqlite3_blob_reopen()

If sqlite_threadsafe=1, SQLITE allocates a mutex for each connection and use the mutex to serialize the operations on the Connection.

In fact, for Sqlite_threadsafe, there are no difference between 2 and any value other than 0 and 1, in which case, only ACC ESS to the global resources is proteced by mutexes, no per connection serialization, concurrently access a connection from Multiple threads can cause errors.

Concurrent Control in SQLite