THREADSAFE is about re-entrancy control.  It has nothing to do with connections 
or transactions or how many/which threads can make sqlite3 calls (except in the 
case of THREADSAFE=0).

TRANSACTIONS are an attribute of a CONNECTION.  All operations on a CONNECTION 
(and statements derived/prepared from the same CONNECTION) occur within the 
same transaction.  This is not affected by the THREADSAFE setting (which is a 
re-entrance control).

The re-entrancy requirements are that only ONE call may be active inside the 
sqlite3 library on each connection (or object derived from a connection, such 
as a statement) at a time.  This means that if you derive/prepare 10 statements 
from the same CONNECTION, you may only step ONE of these statements at a time.  
Ever.  No matter what.

THREADSAFE=0 means that there is only a single thread calling the sqlite3 
library functions.  There can never therefore NEVER be multiple entrances into 
the sqlite3 library since only ONE thread is making calls into the library from 
a process (ie, the main thread).  If you lie about this and violate the rules, 

THREADSAFE=1 means that the sqlite3 library enforces the re-entrancy 
requirements via mutexes attached to the CONNECTION object.  This means that 
the library will serialize access to the sqlite3 engine for you so that only 
one call (entrance) per connection is permitted to proceed.  Other entrances 
(calls) will wait until the in-progress call is complete before proceeding.

THREADSAFE=2 means that the sqlite3 library WILL NOT ENFORCE the re-entrancy 
requirements via mutexes attatched to the CONNECTION object.  The limitation of 
only ONE entrance per connection object at one time is still in effect however, 
so if you violate the rules then AHWBL.

If you share a CONNECTION amongst threads, then everything done on those 
threads occurs within the context of a single transaction.  If you use 
THREADSAFE=1, then sqlite3 will ensure that your threads will not attempts to 
concurrently access the same connection at the same time.  If you use 
THREADSAFE=2, the sqlite3 will not do this and *YOU* must ensure that you do 
not attempt to call an sqlite3 function on the same connection at the same 
time.  If you do not ensure this, AHWBL.

Therefore IF you use THREADSAFE=2 *AND* open a new connection dedicated to each 
thread, THEN you can be sure that you are meeting the entrance requirements 
provided that only calls against that connection (or objects derived therefrom) 
are made on the thread which owns that connection, and from NO OTHER THREAD.

Regardless of the THREADSAFE setting, transactions are an attribute of a 
CONNECTION and have nothing whatsoever to do with threads.  

In any journal mode other than WAL, there can be either multiple reader 
CONNECTIONS or ONE writer CONNECTION.  Readers block writers and the writer 
will block readers.

In WAL mode, readers do not block writer CONNECTIONS and writers do not block 
reader CONNECTIONS.  You may have multiple CONNECTIONS reading and one 
connection WRITING at any given time.  Database WRITES will not be visible to 
OTHER CONNECTIONS until (a) the writer commits and (b) the reader starts a new 
transaction after the WRITE is committed (whether an implicit or explicit 

The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>] On Behalf Of Nick
>Sent: Monday, 12 February, 2018 01:15
>Subject: Re: [sqlite] Question about threadsafe
>I ran several multi-threads tests these days and I want to get a
>that my understanding is correct.
>I use WAL mode and I think whether or not use the same connection
>THREADSAFE=1, 2 is the key to my question.
>Mode 1, threadsafe=2 + multiple threads use the same connection:
>It is not threadsafe;
>Mode 2, threadsafe=2 + each thread runs a sqlite3_open():
>It is threadsafe which means reading and writing can proceed
>but only one writer at a time. PRAGMA busy_timeout() may avoid “db is
>locked” when writing.
>Mode 3, threadsafe=1 + each thread runs a sqlite3_open():
>Same with mode 2, as threadsafe=1 is only supported the ability of a
>to be used
>by more than one thread.
>Mode 4, threadsafe=1 + multiple threads use the same connection:
>Reading and writing can proceed concurrently; Two writers can start
>at the
>same time and sqlite will make them Serialized(but how? guess some
>will be blocked and retry, but I can not find it in the source code).
>In general, WAL make reading and writing concurrent - not just
>serial, but
>writing and writing can only be serial. So writers should use
>to retry(Mode 2), or, use the same connection and the RETRY operation
>be done by sqlite(Mode 4).
>I think sqlite is threadsafe means the integrity of database is
>And there will not be any crash or corruption‎ if applications use
>the way like mode 2 and 4 above.
>Is it right?
>Sent from:
>sqlite-users mailing list

sqlite-users mailing list

Reply via email to