On Monday, 5 August, 2019 17:23, test user <example.com.use...@gmail.com> wrote:
>Whats the difference between these two options for the >SQLITE_THREADSAFE compile time flag? >From the docs: >(1, SERIALIZED) >(2, MULTITHREAD) The SQLite3 library code is not multiply re-entrant, but is only singly-entrant on each connection. (It is, however, multiply entrant provided that those entrances each are on a different connection) This is because the SQLite3 connection pointer is a pointer to a structure that contains information pertaining to that connection, and a statement (or blob handle) is a sub-construct of the parent connection from which it was made. Therefore, you may only make ONE CALL at a time into the SQLite3 library per connection (or sub-construct thereof), because those calls will mutate data associated with the connection. It does not matter from whence that call originated (as in thread) merely that there can only be one active at a time per connection. Period. The difference between SINGLETHREAD, SERIALIZED and MULTITHREAD is in how this is guaranteed. When the THREADSAFE parameter is set to SEARALIZED this means that the SQLite3 library itself will protect the connection data with a mutex to make sure that you do not violate this constraint by "serializing" simultaneous calls into the library on a single connection. This means that if you make two simultaneous calls on the same connection (from different threads, for example), one of them will get the mutex and proceed immediately, and the other one will have to wait until that mutex is released, thus ensuring that the single-entrance requirement is met. When the THREADSAFE parameter is set to MULTITHREAD this means that the SQLite3 library WILL NOT protect the connection data with a mutex to protect you from violating this constraint, and that it is entirely and completely your responsibility to ensure that you do not violate the single-entrance (per connection) requirement. If you do violate the single entrance requirement, you may corrupt the database, the library, the computer, the world, and cause the end of the universe. If and only if you are absolutely sure that you are complying with the single-entrance requirement THEN you can change the THREADSAFE mode from SERIALIZED to MULTITHREAD, which will save you a few nanoseconds per call into the library because the mutexes will no longer be checked. When the THREADSAFE is set to either SERIALIZED or MULTITHREADED the code to handle these mutexes is compiled into the library. You can switch between these two modes at runtime. If you are only using one thread then you can set the THREADSAFE parameter to SINGLETHREAD which will cause the mutex code to be omitted entirely, saving you another nanosecond per call since you will not even need to "jump around" the mutex checking code. When you compile the library with THREADSAFE set to SINGLETHREAD then the mutex protection code IS NOT compiled into the library and therefore you cannot turn it on or off at runtime, since it does not exist. You are free to use as many threads as you like to call into the SQLite3 library no matter what THREADSAFE mode is set at compile or runtime if you are absolutely sure that you are complying with the single-entrance per connection requirement. The THREADSAFE parameter merely sets the level of suspenders that you wish to have to protect against shoddy programming or non-deterministic languages (ie, those that do things such as asynchronous garbage collection or destructors). SINGLETHREAD means leave out the protection code altogether SERIALIZED means to include the code and use a mutex to protect against concurrent entry on a single connection MULTITHREAD means to include the code but "jump around it" so that it is not used >When SQLITE_THREADSAFE=(1, SERIALIZED), how are the reads/writes >queued? Is it per connection, file, or process? SQLITE_THREADSAFE has absolutely no impact on anything OTHER THAN protecting the connection data against multiple concurrent modification. I don't know exactly what you mean by "how are read/writes queued". If you mean I/O, this is an Operating System issue and has nothing to do with SQLite3. When the code requests to read data it issues a read to the Operating System. When it wants to write, it issues a write to the Operating System. How the Operating System carries out those operations is not within the purview of a user program. >What happens when a request is in the queue, does it just wait until >it can be actioned, or return SQLITE_BUSY? What do you mean by "request is in the quere"? There is no queue. >On a 8 core machine, how would I get optimal read throughput from a >single database file? That depends on the speed of the I/O device and the speed of a core, and what processing you are doing between reads, what you are reading, the capabilities of the operating system, and the multiprogramming ratio achieved by your code. >If I have many database connections to the same file, can they all >read concurrently on all cores? Again this is an Operating System issue and is not a user program issue. However, generally speaking there is only one channel (physical set of wires) connecting the storage device containing the "database file" to the central processor of the computer. This means that the I/O channel can only perform one operation at a time. The Operating System may make it appear that multiple things are happening at once, but in reality they are not. This is really an Operating System issue. The SQLite3 library issues a read request to the operating system when it wants to read data, and a write request when it wants to write. How your user program utilizes "the cores" is not under the control of SQLite3. How the OS deals with read and write requests is an Operating System issue and is not within the control of a user program (nor of a library such as SQLite3 which merely requests the Operating System to perform the actual I/O). -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
