Sqlite and Multiple Threads

In our last blog we discussed about Sqlite Error 14 , now this time we are going to focus on a new concept that is Sqlite and Multiple Threads. Multiple Threading is the process by which a processor concurrently executes a series or threads or processes. Sqlite also supports multiple threading with the help of multiple threads. The threading mode can be either selected at compile-time, start-time or run-time. The different types of threading modes supported by Sqlite are:

1) Sqlite Single Thread

In this mode all the objects that are used for mutual exclusion among threads is disabled. When this mode is active, it becomes unsafe to use Sqlite in more than a single thread at the same time.

2) Multithreaded Sqlite

When this mode is active, more than one thread can access Sqlite at once. Though, it should be kept in mind that a common database should not be accessed by multiple threads.

3) Serialized Sqlite

In this mode no restriction is being put and Sqlite can be safely used for performing multiple threads. The default mode enabled in Sqlite is serialized.

Selection Of Threading Modes For Varied Time Selection

For Sqlite Compile Time Options

For selection of threading mode in compile-time SQLITE THREADSAFE parameter is used. In case single-thread mode is enabled at compile time it becomes impossible to enable serialized or multiple-thread mode at both run-time and start-time.

Single-Thread mode: The parameter –DSQLITE THREADSAFE=0 is used to set single thread mode.

Multi-Thread mode: The parameter –DSQLITE THREADSAFE=2 is used for multiple-thread mode.

Serialized mode: The parameter –DSQLITE THREADSAFE=1 is used for serialized mode. Also if no SQLITE THREADSAFE parameter exists, then also serialized mode is deployed.

For Start-Time

In case the thread mode selected at compile-time is not single-thread, the threading modes are selected using Sqlite3_config().

Single-Thread mode: Parameter SQLITE CONFIG SINGLETHREAD sets single-thread mode.

Multi-Thread mode: Parameter SQLITE CONFIG MULTITHREAD sets multi-thread mode.

Serialized mode: Parameter SQLITE CONFIG SERIALIZED sets serialized mode.

For Run-Time

The threading modes in run-time are initiated by selecting flags given by third arguments to Sqlite3 open v2(). In case Sqlite3 open() or Sqlite3 open16() arguments are used instead of Sqlite3 open v2() or no flag is set, then default thread modes of compile-time and start-time are initiated.

Multi-Thread mode: Flag SQLITE OPEN NOMUTEX sets multi-thread mode.

Serialized mode: Flag SQLITE OPEN FULLMUTEX sets serialized mode

Conclusion

While running different threading modes in Sqlite, due to certain mistake in the thread-mode selection or if same database is accessed by multiple threads, database corruption is likely to occur. In such a scenario, deploying third party applications like Sqlite Database Repair proves to be useful for recovery of corrupted database.