SQLite Forum

multi thread mode

multi thread mode

(1) By sqlite3_preupdate_count (XiongZaiBingGan) on 2021-01-20 03:57:24 [link] [source]

i have read the document 'Using SQLite In Multi-Threaded Applications',but i don't get the real difference between the 'multi-thread' and 'Serialized' mode.

as the document says: in 'multi-thread' mode , SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads.

SQLITE_THREADSAFE = 2 descriptions the following: SQLite can be used in a multithreaded program so long as no two threads attempt to use the same database connection (or any prepared statements derived from that database connection) at the same time.

#1.in the following code,is thread-safe when SQLITE_THREADSAFE=2(in 'multi-thread' mode)?

#2.when sqlite3_open() create 2 connection object in 2 individual thread (the 2 individual threads in same process),the 2 connection object is same or different?

####void* myInsert(void* arg)


sqlite3_open("MyDB", &db); /* Open a database named MyDB */

sprintf(SQL, "insert into Students values(%d)", val);

rc = sqlite3_prepare(db, SQL, -1, &stmt, 0);

rc = sqlite3_step(stmt);


return NULL;


####int main(void)


for (i=0; i < 10; i++)

pthread_create(&t[i], 0, myInsert, (void*)i);

for (i=0; i<10; i++)

pthread_join(t[i], 0);

return 0;


(2.1) By Keith Medcalf (kmedcalf) on 2021-01-20 05:34:21 edited from 2.0 in reply to 1 [link] [source]

Regarding your example code, it depends where sqlite3* db is declared.

Since you have not provided a declaration for any of sqlite3* db, sqlite3_stmt* stmt, int rc, or int val your questions are unanswerable and your sample code will neither compile nor run.

As for your initial premise. The SQLite3 library is serially entrant per connection, and a statement is a child structure of a connection.

A connection (sqlite3*) is a pointer to a data structure that is shared by all things associated with that connection.

Any call into the SQLite3 library which uses a "connection" is only singly (serially) entrant. That means that only one thread may execute a library call which references this common data structure at a time because otherwise one "entrance" may "mutate" the "connection" data while another "entrance" is using it. This will cause untoward results ranging from nothing to the instantaneous end to all life in the multiverse.

The SQLITE_THREADSAFE sets the level of "protection" that the library will take to prevent this rather unfortunate circumstance.

If you set the SQLITE_THREADSAFE to SINGLETHREAD you are telling the library that under no circumstances whatsoever will there EVER BE concurrent entrances to the library code on different threads. It does not mean that you cannot have as many threads as you want, or that only one thread may make calls into the library, only that ONLY ONE OF THEM AT A TIME EVER will make a call into the library. Having asserted this, the library will DISABLE ALL the protections in place to prevent multiple entrances from accessing the same data of any type concurrently. If you violate the contract you have stated and bad things happen, it is YOUR FAULT exclusively.

If you set the SQLITE_THREADSAFE to SERIALIZED (the default) then the library will "take precautions" to ensure that the single entrance per connection is enforced against you, and will apply protections to ensure that multiple entrances do not attempt to access/mutate the same global data at the same time. This means that even if you bugger up your compliance with the SINGLE ENTRANCE PER CONNECTION requirement, the library will automatically ensure SINGLE ENTRANCE PER CONNECTION with semaphores, thus effectively "serializing" your concurrent access per connection so that only one entrance is executing per connection at a time.

If you set the SQLITE_THREADSAFE to MULTITHREAD then you are telling the library to set its protection level half-way between the two above extremes. The library will provide protection for its global data. It will not provide protection to enforce the single-entrance per connection requirement BECAUSE YOU HAVE SAID THAT YOU WILL DO IT. If you violate the contract you have stated by attempting multiple entrance on the same connection, the library will not stop you. However, anything bad that happens if you do is YOUR FAULT exclusively.

Other than the above protections to global and connection specific data, the SQLITE_THREADSAFE has no other effect. It does not affect transaction processing, nor does it affect isolation, nor does it affect the ACID properties of SQLite3. It simply controls multiple simultaneous access to shared data structures in memory.

(3.1) By Keith Medcalf (kmedcalf) on 2021-01-20 05:35:13 edited from 3.0 in reply to 2.0 [link] [source]

In short,

SQLITE_THREADSAFE = 0 means Not at all Threadsafe.
SQLITE_THREADSAFE = 1 means Fully Threadsafe.
SQLITE_THREADSAFE = 2 means Threadsafe so long as you follow the rules.

If your multithreaded program is compliant with the requirements then changing the SQLITE_THREADSAFE mode from its default SERIALIZED (1) to one of the other modes may save you a few nanoseconds to a few microseconds on each call into the library, depending on the OS, the hardware, the phase of the moon, and what else may happen to be running on the computer at the same time.

(4) By sqlite3_preupdate_count (XiongZaiBingGan) on 2021-01-20 05:39:37 in reply to 2.0 [link] [source]

sqlite3* db, sqlite3_stmt* stmt, int rc, and int val all defines in the myInsert() method.

in this situation, is my example code thread-safe in 'multi-thread' mode ?

#void* myInsert(void* arg)


sqlite3* db = 0;

sqlite3_stmt* stmt = 0;

int val = (int)arg;

char SQL[100];

int rc;

rc = sqlite3_open("MyDB", &db); /* Open a database named MyDB */

if (rc != SQLITE_OK) {

fprintf(stderr, "Thread[%d] fails to open the databasen", val);

goto errorRet;


sprintf(SQL, "insert into Students values(%d)", val);

rc = sqlite3_prepare(db, SQL, -1, &stmt, 0);

if (rc != SQLITE_OK) {

fprintf(stderr, "Thread[%d] fails to prepare SQL: %s ->

#return code %dn", val, SQL, rc);

goto errorRet;


rc = sqlite3_step(stmt);

if (rc != SQLITE_DONE) {


"Thread[%d] fails to execute SQL: %s -> return code %dn", val, SQL, #rc);


else {

printf("Thread[%d] successfully executes SQL: %sn", val,





return (void*)rc;


(5) By Keith Medcalf (kmedcalf) on 2021-01-20 06:27:31 in reply to 4 [link] [source]

Yes, you have one connection per thread.

(6) By anonymous on 2021-01-21 01:09:55 in reply to 5 [link] [source]

If you have one connection per thread (connection is stored inside a TLS variable), but multiple connections to the same underlying database, is it safe to run on SQLITE_CONFIG_SINGLETHREAD?

(7) By Keith Medcalf (kmedcalf) on 2021-01-21 02:42:59 in reply to 6 [link] [source]

Do you have more than one simultaneous entrance to the same instance of the SQLite3 library? If so, then you are not single threaded.

Also, it does not matter where the POINTER TO THE CONNECTION DATA is stored, only where the CONNECTION DATA ITSELF is stored. You can store the pointers to the connections on the moon and at the bottom of the sea. This is quite irrelevant to where the CONNECTION DATA which is pointed to by the connection pointer is stored since you are not in control of where this data resides.

(8) By Deon Brewis (deonb) on 2021-01-21 18:02:39 in reply to 7 [link] [source]

Each thread does its own sqlite3_open_v2, get their own thread-specific handles, run their own thread-specific prepares, set up their own thread-specific functions and virtual tables etc.

But they do an open to the same filename on disk.

Would that constitute being single-threaded or multi-threaded?

Put another way, does SQLITE share state inside a process in a method that's not tied to the handle?

(9) By Keith Medcalf (kmedcalf) on 2021-01-21 18:40:32 in reply to 8 [link] [source]

Consider the following C function:

struct list
  struct list* prior;
  void* memory;

void* GetHandle()
   static struct list *head = 0;
   void* h = malloc(10);
   struct list *listentry = malloc(sizeof(struct list));
   listentry->memory = h;
   listentry->prior = head;
   head = listentry;
   return h;

It does not matter WHERE you store the thing returned by GetHandle because it merely POINTS TO a data structure allocated by the same library function.

So to answer your question if each thread is one thread of separate processes, then each of those processes qualifies as a single-threaded use. However, if the "library attachment" is per-process (rather than per thread) and all the threads are in the same process, then you have multiple threads accessing the same process global storage.

That is to say that the SQLite3 library is not "linked per thread" unless you re-write it to do so. It exists per process.

(10) By Deon Brewis (deonb) on 2021-01-21 22:26:35 in reply to 9 [link] [source]

In the code above if you call GetHandle() from multiple threads (and you don't share the handle between the threads explicitly on the caller side), they're still not going to be overlapping each other.

They only become overlapping if the implementation on the SQLITE side is something like this:

void* g_handle = 0;

void* GetHandle()
   if (g_handle)
       return g_handle;

   static struct list *head = 0;
   void* h = malloc(10);
   struct list *listentry = malloc(sizeof(struct list));
   listentry->memory = h;
   listentry->prior = head;
   head = listentry;

   g_handle = h;
   return g_handle;

The question is, does SQLITE ever do that? Like you said, does SQLITE have explicit global process storage? (And where is it in the sqlite3.c code?)

My client code is effectively this:

thread_local sqlite3* t_handle = nullptr;
if (!t_handle)
   sqlite3_open_v2("somefile.db", &t_handle, ...);

(11) By anonymous on 2021-01-21 23:30:36 in reply to 10 [source]

does SQLITE have explicit global process storage?

Yes, it has.

(12) By Deon Brewis (deonb) on 2021-01-22 01:42:31 in reply to 11 [link] [source]

Ok, looking at the relocation table inside sqlite3.obj file I can see that the default implementation of sqlite3_pcache_methods2 use a global pcache1_g.

However, those functions are user overridable.

Anything else that's global? (And written to. There's obviously a lot of read-only literals that are global).

(13) By Keith Medcalf (kmedcalf) on 2021-01-22 11:36:44 in reply to 12 [link] [source]

What does it matter?

Why would you want to change the SQLITE_THREADSAFE at all from the perfectly guaranteed to work default value? What problem are you seeking to solve?

Just because a setting is available does not mean that you need to diddle with it and adjust it from its default without good cause (especially if you do not understand it). From half-a-century of experience I can tell you that people who do things "just cuz" usually end up being hoisted by their own petard.

The most recent example is all the morons who hoisted themselves by their own petard (their inherently defective Risk Assessment practices) in regards to installing malicious software from Solarwinds -- an installation that was neither required nor warranted, but undertaken "just cuz" they like to fiddle-diddle for fiddle-diddling sake, rather than because a Risk Assessment indicated that there was a reason for the update.