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
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.
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] 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
>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
>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: http://sqlite.1065341.n5.nabble.com/
>sqlite-users mailing list
sqlite-users mailing list