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, AHWBL. 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 transaction). --- 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- >boun...@mailinglists.sqlite.org] On Behalf Of Nick >Sent: Monday, 12 February, 2018 01:15 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Question about threadsafe > >I ran several multi-threads tests these days and I want to get a >confirmation >that my understanding is correct. >I use WAL mode and I think whether or not use the same connection >with >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 >concurrently, >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 >handle >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 >threads >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 >busy_timeout() >to retry(Mode 2), or, use the same connection and the RETRY operation >will >be done by sqlite(Mode 4). > >I think sqlite is threadsafe means the integrity of database is >guaranteed. >And there will not be any crash or corruption if applications use >sqlite >the way like mode 2 and 4 above. > >Is it right? > >Thanks. > > > >-- >Sent from: http://sqlite.1065341.n5.nabble.com/ >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
