Thanks, When SQLITE_THREADSAFE=(1, SERIALIZED), how are the reads/writes queued? Is it per connection, file, or process?
Quote: I don't know exactly what you mean by "how are read/writes queued". Quote: 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 What I mean by "queue" is that if one request is waiting until a mutex is released, it is in an implicit queue for that resource. I wanted to know how this queue works: - Does the second request just take longer to return from the FFI call whilst waiting for the mutex? - Or does the FFI return SQLITE_BUSY and expect the caller to call again in the future? In other words: How should I handle this in my library that uses the FFI? So in summary, there is no difference in the multi threaded performance that can be gained between SERIALIZED and MULTITHREADED (aside from the mutex overhead)? The only difference is SERIALIZED enforces correct usage at a small overhead cost? So for example, if I had: - 8 cores - 8 threads - 8 db connections, 1 per thread - 1 database file - x amount of read requests per second If I were to load balance x requests over each of the 8 threads, all the reads would complete concurrently when in SERIALIZED mode, with WAL enabled? Assume other bottlenecks in the system are not an issue (like disk speed). Im just trying to confirm that SERIALIZED will not queue up requests for (1 file, multiple connections to that file, read only requests). On Tue, Aug 6, 2019 at 1:55 AM Keith Medcalf <kmedc...@dessus.com> wrote: > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
